Budget Planning with Excel: Macros

If you've never created a macro in Microsoft Office, you're missing out. You probably have some repetitive task that you wish you could automate. Well, it probably is possible to automate it (though it might take writing some computer code).

There is a way to create macros without dealing with code: you can tell Excel (or Word, or other Office programs) to record a macro. I won't explain this; if you haven't done it, just search for "record macro Excel" and you'll find the info you need.

To deal with the code that underlies macros, you use the Visual Basic Editor. Open this program by pressing Alt + F11 from Excel. It should look something like this:

Though you won't have the module Budget2010 and its code. That's what I'm going to give you.

I can't take time to explain everything about this editor or the language (Visual Basic) that it uses. There are millions of web pages out there that explain these, as well as a decent help file in the Visual Basic Editor itself.

For now, just try adding a module (Insert > Module) and giving it a meaningful name (instead of the default Module1). Then copy the code below and paste it into the module's code window:

Sub AddAnotherLinetoDate()
'Shortcut=Ctrl+D
'Adds another row below the current one, so you can record another transaction for that day.
Dim s As Worksheet
Dim r, c As Long
Dim d As String

Set s = ActiveSheet
r = ActiveCell.Row
c = ActiveCell.Column
d = s.Cells(r, 1).Text
Rows(r + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'Give the new row the same date as the original row.
s.Cells(r + 1, 1).Value = d
'Gray out the date in the new row
s.Range("A" & r + 1 & ":A" & r + 1).Font.Color = RGB(169, 169, 169)
s.Cells(r + 1, c).Select
End Sub

The comments (the lines that start with ') explain what this subroutine does: it adds a new row to the selected date. To try the macro out, select a cell in Excel, then go back to the Visual Basic Editor, put your cursor anywhere inside the code, and press F5.

Of course, you don't want to have to open the Visual Basic Editor every time you run a macro, so you can give macros keyboard and button shortcuts. I'll explain how to do that in my next post.

In the meantime, you'll want to save your Excel spreadsheet, which now has this code stored in it as well. In Office 2007, you have to save the spreadsheet with the .xslm extension to enable macros.