It's no secret that I'm a total Excel fanboy. Much of that comes from the fact that I enjoy writing VBA code, and Excel combined with VBA scripts open up a whole world of possibilities.
In the past, here at MUO, I've shared a few of the things I've done with Excel and VBA, like exporting Excel data into Word or sending out emails straight from Excel. Of course if you aren't much of a coder, you can always get Excel applications someone else has written, like those listed by Simon.
However, if you're serious about using Excel to manage more of your life, then you should take the time to learn how VBA coding works. To help with that, I've decided to share a "Automation" spreadsheet that I've created to manage different areas of my life. This spreadsheet has 4 tabs and covers everything from grouping links of URLs that I want to launch all at once, to managing my debt and paying it off faster.
If you want to enhance your use of Excel, I invite you to follow along as I share a few of these designs - and simple scripts - that I used to accomplish these tasks.
Managing Your Life With Excel
Microsoft Excel is not just a data spreadsheet. It is actually a design platform for applications. If you look at it that way, you may realize just how much you can accomplish with the application.
Look at a sheet as a design board where you can place Visual Basic form objects like command buttons, dropdown boxes, textboxes and anything else at all. Not only can you place them anywhere on the sheet, but you can use those objects to interactively (or automatically) add, remove, or manipulate information on the sheet.
Monitoring Your Websites
I've tried a lot of different tools to ping the different websites that I manage, like the ICMP Ping Manager. But, not long ago I discovered a way to ping websites from right inside of an Excel VBA script. That meant, I could add a sheet to my "Automation" workbook that would ping all of the websites that I manage, and put the results into a cell next to the website name.
This is how I laid out the sheet.
The number "4" in cell B1 is used to display the count of websites that I've installed on the sheet. This will allow the script to only count through the number of cells that actually have sites listed, started at A3.
The code to accomplish this looks like this:
Dim intSiteCount As Integer
Dim intCount As Integer
Dim oPing As Object, oRetStatus As Object
Dim sHost As String
Dim sPing As String
Dim intCol As Integer
Dim intRow As Integer
intSiteCount = CInt(Sheet1.Cells(1, 2).Value)
intRow = 3
For intCount = 1 To intSiteCount
sPing = ""
Sheet1.Cells(intRow, 2) = sPing
intRow = intRow + 1
Next
intRow = 3
For intCount = 1 To intSiteCount
sHost = Sheet1.Cells(intRow, 1)
Set oPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
("select * from Win32_PingStatus where address = '" & sHost & "'")
For Each oRetStatus In oPing
If IsNull(oRetStatus.StatusCode) Or oRetStatus.StatusCode <> 0 Then
sPing = "Ping Failed"
Else
sPing = sHost & " Ping Success on " & Now() & Chr(10)
sPing = sPing & "Time (ms) = " & vbTab & oRetStatus.ResponseTime & Chr(10)
sPing = sPing & "TTL (s) = " & vbTab & vbTab & oRetStatus.ResponseTimeToLive
End If
Next
Sheet1.Cells(intRow, 2) = sPing
intRow = intRow + 1
Next
The first "For" look at the top just goes through the results cells and clears the results from the last time I ran a check. The second FOR loop counts through the rows listing the websites, starting at the third row (intRow-3), performs the Ping command (the Set oPing line), and then returns the results into column B (Sheet1.Cells(intRow,2) = sPing).
Here's how those results look after the script runs.
The results column shows whether the ping was successful, and the Time/TTL details.
If you aren't familiar with adding command buttons to an Excel sheet, you can add the button from the "Developer" menu, and clicking on the "Insert" button and choosing the button from the list. Draw the button onto the sheet, right click on it, and then select "Assign Macro".
Type the name of the Macro for that button, and click on "New".
This will open up the code screen where you can insert the code from above.
Maintaining a Library of Link Groups
On another tab, I also started to organize the group of links that I use to perform certain tasks. For example, when I write for MUO, I like to open up the MUO Wordpress editor, google, and our Google Docs page for topics. When I research for Top Secret Writers topics, I like to open a few standard media pages.
When I click the "Launch Group" button, it will launch the default web browser and open up all pages in that group. Here's how the script for each button looks:
Dim intSiteCount As Integer
Dim intCount As Integer
Dim intCol As Integer
Dim intRow As Integer
intSiteCount = CInt(Sheet2.Cells(4, 3).Value)
intRow = 5
For intCount = 1 To intSiteCount
ActiveWorkbook.FollowHyperlink (Sheet2.Cells(intRow, 2))
intRow = intRow + 1
Next
This script is simple but effective. The secret to this one is the "FollowHyperlink" function. This code will check the number of links defined in the cell just to the right of the group title, and knows to run through that many links before the title. For each button, the location of the link count, and the column being used needs to be manually typed into the code, but the rest of the code is identical for each button.
Previewing your Picture Gallery
On the next tab of my automation worksheet is where I go when I want to quickly run through all of the images in my pictures folder. I do this because I place more than just images in that folder, and want to see all of the files that I have there.
Here's what it looks like after clicking the "Preview Pics" button.
Right now I manually update this sheet by deleting all pictures from the B column, and then clicking on the "Preview Pics" button.
The button runs the following script:
Dim myPict As StdPicture
Dim strFilePath As String
Dim intRow As Integer
Dim myPictName As Variant
Dim myCell As Range
Dim sPicture As String
Dim strTest As String
Dim myRng As Range
Dim intSkip As Integer
intRow = 2
strFilePath = Sheet3.Cells(1, 3).Value
Set myObject = New Scripting.FileSystemObject
Set mySource = myObject.GetFolder(strFilePath)
On Error Resume Next
With Sheet3
Set myRng = Sheet3.Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
End With
For Each myfile In mySource.Files
'If picture is a file
If Right(myfile, 4) = ".gif" Or Right(myfile, 4) = ".jpg" Or Right(myfile, 4) = ".bmp" Or Right(myfile, 4) = ".tif" Or Right(myfile, 4) = ".png" Then
Sheet3.Cells(intRow, 1).Value = ""
Sheet3.Cells(intRow, 1).Value = myfile.Name
intSkip = 0
For Each myCell In myRng.Cells
If intSkip = 1 Then
With myCell.Offset((intRow - 3) + 1, 0)
Sheet3.Shapes.AddPicture myfile.Path, msoCTrue, msoCTrue, .Left, .Top, 125, 125
End With
End If
intSkip = intSkip + 1
Next myCell
End If
intRow = intRow + 1
Next
The secret of this script is using the StdPicture object, which lets you sort of overlay pictures at the location of certain cells, by defining the left and top properties of the picture to match that of the cell. Just make sure to size the cells slightly larger than what you define in the code. In my situation I used 125 height and width for the pictures, so my cells are set slightly larger than that ahead of time.
Managing Your Debt
The final tab that I want to share is actually one that I wrote a while back about using Excel to create a personal budget.
The most important concept that I wrote about in that article, and one that belongs in any article about using Excel to manage your life, is using Excel to calculate how the "snowball effect" can help you pay down your debt.
The concept is pretty simple. List all of your credit card debt side by side in a sheet, with two columns per debt - total balance and payment. The calculation for each subsequent payment cell is "PrevBalance + (PrevBalance * 0.10/12) - last payment"
Then you can drag all of the values down the sheet and they'll recalculate, showing how quickly your balance will drop as you make those payments. As you can see, making one payment per debt until each debt is paid will eventually pay off each individual debt.
But thanks to the quick calculating power of Excel, you can determine when balances will be paid off, and at that point take the minimum balance for that card and move it over to another card not yet paid off. As the spreadsheet shows, each subsequent balance gets paid off much faster.
Excel allows you to calculate and visualize quickly how your payments will affect future payoff dates, and it also gives you a schedule to look back at while you're trying to make sure that you're right on track in paying off those debts.
As you can see, Excel is a very powerful tool when it comes to managing all aspects of your life - whether it's your work, managing files, or your budget.
Do you have any unique uses for Excel to manage your own life? Share some of your own tips and advice in the comments section below.
Image Credit: magnifying glass via Shutterstock