Journal-of-Accountancy-January-2005_Page_1

Automate Excel Functions

Jeff Lenning | January 28, 2014 | Comments Off

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.

Exhibit 1

To instruct Excel to begin recording, select Tools, Macros, Record New Macro (exhibit 2).

Exhibit 2

That will engage the Record Macro dialog box (exhibit 3).

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.

Exhibit 4

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).

Exhibit 5

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).

Exhibit 6

Doing that will launch the Forms Toolbar (exhibit 7, below).

Exhibit 7

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.

Exhibit 8

When you click on the button, you will be prompted by the Assign Macro screen to identify which macro to run. Each macro that you create will be listed under Macro name.

The Toolbar button: To create such a button, click on Tools, Customize and then on the Toolbars tab (exhibit 9, below).

Exhibit 9

Select Macros from the Commands field and click on and drag Custom Button to anywhere in the toolbar. Later, by right-clicking on the newly created toolbar button, you can change the smiley face icon and then assign the macro (exhibit 10).

Exhibit 10

The Macro dialog box: Select Tools and Macro (exhibit 11, below).

Exhibit 11

That will launch the Macro dialog box, which then will list all the available macros (exhibit 12).

Exhibit 12

To execute a macro, select it from the list and click on Run.

LOOK AT THE CODE
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.

Sub SetupWorkpaper()
‘
‘ SetupWorkpaper Macro
‘ Macro recorded 5/20/2004 by Jeff Lenning
‘
‘ Keyboard Shortcut: Ctrl+s
‘
Range(“D1”).Select
ActiveCell.FormulaR1C1 = “Click Consulting”
Range(“D2”).Select
ActiveCell.FormulaR1C1 = “Accounts Receivable Detail”
Range(“D3”).Select
ActiveCell.FormulaR1C1 = “FYE: 6/30/2004”
Range(“A5”).Select
ActiveCell.FormulaR1C1 = “Purpose”
Range(“B5”).Select
ActiveCell.FormulaR1C1 = “The purpose of this worksheet is to ”
Range(“A6”).Select
Range(“B5”).Select
ActiveCell.FormulaR1C1 = _
“The purpose of this worksheet is to provide the detail”
Range(“B6”).Select
Range(“B5”).Select
ActiveCell.FormulaR1C1 = _
“The purpose of this worksheet is to provide the detail for the ”
Range(“B6”).Select
ActiveCell.FormulaR1C1 = “6/30/2004 Accounts Receivable balance.”
Range(“A5”).Select
Selection.Font.Bold = True
Range(“D1:D3”).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

If you want to learn more about VB, check out Microsoft’s Visual Basic for Applications home page at http://msdn.microsoft.com/vba/. There also are many books on the subject; one popular one is Mastering Visual Basic 6 by Evangelos Petroutsos (Sybex Inc., 1999).

If you don’t want to spend the time writing VB code from scratch, there are many places on the Internet where you can find completed macros for many different applications that you can adapt for your own use. Just do a Google search for Visual Basic macros.

Whether you become an expert in macro code or just use the recording method, you’ll find macros can help you speed many of your repetitive operations and boost your productivity.

JEFF LENNING, CPA, is the founder of Click Consulting, Seal Beach, California. His company provides IT solutions, support and development.

Macro Security

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.

It’s a good security practice to never enable macros unless you are absolutely sure they come from a trusted source.

Posted in