Automate Excel Functions: Easy-to-Create Macros Can Take over Many Manual Processes
Jeff Lenning · Journal of accountancy online/Journal of accountancy · 2005
Macros, 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. To instruct Excel to begin recording, select Tools, Macros, Record New Macro (exhibit 2, page 47). That will engage the Record Macro dialog box (exhibit 3, page 47). 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. Now you're ready to begin the recording process. Click on OK in exhibit 3, which remains on your screen. Excel signals that it's ready to record your keystrokes by the presence of the small Stop dialog box (exhibit 5, at right). Now perform all the steps to set up your custom workpaper, such as entering the text and formatting it. When you're done, click on the Stop button. EXECUTING A MACRO 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. * The Form button: Click on View, Toolbars and Forms (exhibit 6, below). Doing that will launch the Forms Toolbar (exhibit 7, at right). Click on the button icon (row 2, right side) and then click anywhere in your worksheet to create the button, as shown in exhibit 8, below. …