Automate Excel Functions
Easy-to-create macros can take over many manual processes.
|Key to Instructions
To help readers follow the instructions in this article, we use two different typefaces:
Boldface type is used to identify the names of icons, agendas and URLs.
Sans serif type indicates commands and instructions that users should type into the computer and the names of files.
acros, those do-it-yourself software programs, rank among Microsoft’s most useful tools. They automate many computer tasks that you otherwise would have to execute manually—from the simple task of creating customized worksheets to the very complex tasks of exporting journal entries in Excel into an accounting package and creating reports in Word.
What makes macros so wonderful is that in many cases you don’t have to be an expert to set them up. If you’re willing to invest a little time to learn the language they are written in, Visual Basic (VB), you can make them perform some astonishingly complicated jobs—such as handling an entire monthly close. For this article we will focus on macro basics that do not require you to program in VB. But be forewarned, once you see how powerful macros are, you may find yourself anxious to learn the language.Although macros are available in the entire Microsoft Office Suite—including Word, Access, PowerPoint and Outlook—we’re going to show you how they work in Excel, which is where they really show their muscle for CPAs and other finance professionals. Once you start using them, you’ll likely find your work output increasing substantially.
Follow along and we’ll create a macro simply by recording the keyboard strokes and mouse clicks needed to perform a typical accounting task—setting up a workpaper. As you proceed, Excel translates your recorded steps into VB. Once they’re recorded you can command Excel to replay them. It usually takes about five minutes to set up workpapers manually; with a macro, it takes seconds.
As you can see in exhibit 1, below, we have a standard custom format for all our workpapers, which includes a line for the client name, workpaper name, period, purpose, initials and date.
That will engage the Record Macro dialog box (exhibit 3).Under Macro name select something short and friendly. Note that macro names must be one word. We’ve selected SetupWorkpaper. Under the Shortcut key pick a letter that, when pressed simultaneously with Ctrl, will execute the completed macro. We’ve selected the letter S. The shortcut key method is only one of the numerous ways to execute the macro; we’ll show you more later.Under Store macro in you have several location options. If the macro will be run in only one specific workbook, save it in that workbook. If you would like to run the macro in several workbooks, save it in your Personal Macro Workbook. It’s important to remember where you save the macro text.
If you save it in the Personal Excel Workbook, a dialog box asking you to confirm your decision will open (exhibit 4, below). Click on Yes.
Once you have a macro in memory, let’s see how to run it. Begin by opening a new workbook. Remember we mentioned there are several ways to launch your macro. You can use the shortcut key, Ctrl+S, which is the easiest; however, if you create many macros you may not be able to remember which key triggers which macro. The other methods—the Form button, the Toolbar button or the Macro box—provide you with the macro name.
For those who would like to “look under the hood” and see the VB code that Excel wrote for the macro, click on Tools, Macros and Visual Basic Editor. Here you can view the code, and once you become familiar with the language, you can edit it, too. The box below shows what a portion of the code looks like.
‘ SetupWorkpaper Macro
‘ Macro recorded 5/20/2004 by Jeff Lenning
‘ Keyboard Shortcut: Ctrl+s
ActiveCell.FormulaR1C1 = “Click Consulting”
ActiveCell.FormulaR1C1 = “Accounts Receivable Detail”
ActiveCell.FormulaR1C1 = “FYE: 6/30/2004”
ActiveCell.FormulaR1C1 = “Purpose”
ActiveCell.FormulaR1C1 = “The purpose of this worksheet is to ”
ActiveCell.FormulaR1C1 = _
“The purpose of this worksheet is to provide the detail”
ActiveCell.FormulaR1C1 = _
“The purpose of this worksheet is to provide the detail for the ”
ActiveCell.FormulaR1C1 = “6/30/2004 Accounts Receivable balance.”
Selection.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
Depending on which version of Excel you have and whether you’ve downloaded the latest Microsoft service pack, you may get an alert each time you open a file that contains a macro. Microsoft inserted the alert because macros can be programmed to act like viruses—instructed to delete files and otherwise wreak havoc on your system.