The macro recorder doesn’t work!

Microsoft began to dominate the spreadsheet market in the mid-1990s. Although it was wildly successful in building a powerful spreadsheet program to which any Lotus 1-2-3 user could easily transition, the macro language was just too different. Anyone proficient in recording Lotus 1-2-3 macros who tried recording a few macros in Excel most likely failed. Although the Microsoft VBA programming language is much more powerful than the Lotus 1-2-3 macro language, the fundamental flaw is that the macro recorder does not work when you use the default settings.

With Lotus 1-2-3, you could record a macro today and play it back tomorrow, and it would faithfully work. When you attempt the same feat in Microsoft Excel, the macro might work today but not tomorrow. In 1995, when I tried to record my first Excel macro, I was horribly frustrated by this. In this book, I teach you the three rules for getting the most out of the macro recorder.

No one person on the Excel team is focused on the macro recorder

As Microsoft adds new features to Excel, the individual project manager for a feature makes sure that the macro recorder will record something when you execute the command. In the past decade, the recorded code might work in some situations, but it often does not work in all situations. If Microsoft had someone who was focused on creating a useful macro recorder, the recorded code could often be a lot more general than it currently is.

It used to be that you could record a command in any of five ways, and the recorded code would work. Unfortunately, today, if you want to use the macro recorder, you often have to try recording the macro several different ways to find a set of steps that records code that reliably works.

Visual Basic is not like BASIC

Nearly three decades ago, the code generated by the macro recorder was unlike anything I had ever seen. It said this was “Visual Basic” (VB). I have had the pleasure of learning half a dozen programming languages at various times; this bizarre-looking language was horribly unintuitive and did not resemble the BASIC language I had learned in high school.

To make matters worse, even in 1995 I was the spreadsheet expert in my office. My company had forced everyone to convert from Lotus 1-2-3 to Excel, which meant I was faced with a macro recorder that didn’t work and a language that I couldn’t understand. This was not a good combination of events.

My assumption in writing this book is that you are pretty talented with a spreadsheet. You probably know more than 90% of the people in your office. I also assume that even though you are not a programmer, you might have taken a class in BASIC at some point. However, knowing BASIC is not a requirement—it actually is a barrier to entry into the ranks of being a successful VBA programmer. There is a good chance that you have recorded a macro in Excel, and there’s a similar chance that you were not happy with the results.

Good news: Climbing the learning curve is easy

Even if you’ve been frustrated with the macro recorder, it is really just a small speed bump on your road to writing powerful programs in Excel. This book teaches you not only why the macro recorder fails but also how to change the recorded code into something useful. For all the former BASIC programmers in the audience, I decode VBA so that you can easily pick through recorded macro code and understand what is happening.

Great news: Excel with VBA is worth the effort

Although you probably have been frustrated with Microsoft over the inability to record macros in Excel, the great news is that Excel VBA is powerful. Absolutely anything you can do in the Excel interface can be duplicated with stunning speed in Excel VBA. If you find yourself routinely creating the same reports manually day after day or week after week, Excel VBA will greatly streamline those tasks.

The authors of this book provided Excel consulting for 20 years. We have automated reports for hundreds of clients. The stories are often similar: The IT department has a several-month backlog of requests. Someone in accounting or engineering discovers that he or she can import some data into Excel and get the reports necessary to run the business. This is a liberating event: You no longer need to wait months for the IT department to write a program. However, the problem is that after you import the data into Excel and win accolades from your manager for producing the report, you will likely be asked to produce the same report every month or every week. This becomes very tedious.

Again, the great news is that with a few hours of VBA programming, you can automate the reporting process and turn it into a few button clicks. The reward is great. So, hang with me as we cover a few of the basics.

This chapter exposes why the macro recorder does not work. It also walks through an example of recorded code and demonstrates why it works today but will fail tomorrow. I realize that the code you see in this chapter might not be familiar to you, but that’s okay. The point of this chapter is to demonstrate the fundamental problem with the macro recorder. This chapter also explains the fundamentals of the Visual Basic environment.

Adding a trusted location

You can choose to store your macro workbooks in a folder that is marked as a trusted location. Any workbook stored in a trusted folder will have its macros enabled. Microsoft suggests that a trusted location should be on your hard drive. The default setting is that you cannot trust a location on a network drive.

To specify a trusted location, follow these steps:

1. Click Macro Security in the Developer tab.

2. Click Trusted Locations in the left navigation pane of the Trust Center.

3. If you want to trust a location on a network drive, select Allow Trusted Locations On My Network.

4. Click the Add New Location button. Excel displays the Microsoft Office Trusted Location dialog box (see Figure 1-2).

5. Click the Browse button. Excel displays the Browse dialog box.

6. Browse to the parent folder of the folder you want to be a trusted location. Click the trusted folder. Although the folder name does not appear in the Folder Name box, click OK. The correct folder name will appear in the Browse dialog box.

7. If you want to trust subfolders of the selected folder, select Subfolders Of This Location Are Also Trusted.

8. Click OK to add the folder to the Trusted Locations list.

Using macro settings to enable macros in workbooks outside trusted locations

For all macros not stored in a trusted location, Excel relies on the macro settings. The Low, Medium, High, and Very High settings that were familiar in Excel 2003 have been renamed.

To access the macro settings, click Macro Security in the Developer tab. Excel displays the Macro Settings category of the Trust Center dialog box. Select the second option, Disable All Macros With Notification. A description of each option follows:

• Disable VBA Macros Without Notification—This setting prevents all macros from running. This setting is for people who never intend to run macros. Because you are currently holding a book that teaches you how to use macros, it is assumed that this setting is not for you. This setting is roughly equivalent to the old Very High security setting in Excel 2003. With this setting, only macros in the Trusted Locations folders can run.

• Disable VBA Macros With Notification—The operative words in this setting are “With Notification.” This means that you see a notification when you open a file with macros, and you can choose to enable the content. If you ignore the notification, the macros remain disabled. This setting is similar to the Medium security setting in Excel 2003 and is the recommended setting. In Excel, a message is displayed in the Message area, indicating that macros have been disabled. You can choose to enable the content by clicking that option, as shown in Figure 1-3.

• Disable VBA Macros Except Digitally Signed Macros—This setting requires you to obtain a digital signing tool from Verisign or another provider. This might be appropriate if you are going to be selling add-ins to others, but it’s a bit of a hassle if you just want to write macros for your own use.

• Enable VBA Macros (Not Recommended: Potentially Dangerous Code Can Run)—This setting is similar to the Low macro security setting in Excel 2003. Although it requires the least amount of hassle, it also opens your computer to attacks from malicious Melissa-like viruses. Microsoft suggests that you not use this setting.

Using Disable All Macros With Notification

It is recommended that you set your macro settings to Disable All Macros With Notification. If you use this setting and open a workbook that contains macros, you see a security warning in the area just above the formula bar. If you are expecting macros in this workbook, click Enable Content. If you do not want to enable macros for the current workbook, dismiss the security warning by clicking the X at the far right of the message bar.

If you forget to enable the macros and attempt to run a macro, Excel indicates that you cannot run the macro because all macros have been disabled. If this occurs, close the workbook and reopen it to access the message bar again.

Filling out the Record Macro dialog box

In the Macro Name field, type a name for the macro. Be sure to type continuous characters. For example, type Macro1 (without a space), not Macro 1 (with a space). A macro name can include letters, numbers, and the underscore character. The name cannot start with a number. Assuming that you will soon be creating many macros, use a meaningful name for the macro. A name such as FormatReport is more useful than one like Macro1.

The second field in the Record Macro dialog box is a shortcut key. If you type a lowercase j in this field and later press Ctrl+J, this macro runs. Be careful, however, because Ctrl+A through Ctrl+Z (except Ctrl+J) are all already assigned to other tasks in Excel. If you assign a macro to Ctrl+B, you won’t be able to use Ctrl+B for bold anymore. One alternative is to assign the macros to Ctrl+Shift+A through Ctrl+Shift+Z. To assign a macro to Ctrl+Shift+A, you type Shift+A in the shortcut key box.

In the Record Macro dialog box, choose where you want to save a macro when it is recorded: Personal Macro Workbook, New Workbook, or This Workbook. My recommendation is that you store macros related to a particular workbook in This Workbook.

The Personal Macro Workbook (Personal.xlsm) is not a visible workbook; it is created if you choose to save the recording in the Personal Macro Workbook. This workbook is used to save a macro in a workbook that opens automatically when you start Excel, thereby enabling you to use the macro. After Excel is started, the workbook is hidden. If you want to display it, select Unhide from the View tab.

The fourth box in the Record Macro dialog box is for a description. This description is added as a comment to the beginning of your macro.

After you select the location where you want to store the macro, click OK. Record your macro. For this example, type Hello World in the active cell and press Ctrl+Enter to accept the entry and stay in the same cell. When you are finished recording the macro, click the Stop Recording icon in the Developer tab.

Creating a macro button on the ribbon

You can add an icon to a new group on the ribbon to run your macro. This is appropriate for macros stored in the Personal Macro Workbook. Icons added to the ribbon are still enabled even when your macro workbook is not open. If you click the icon when the macro workbook is not open, Excel opens the workbook and runs the macro. Follow these steps to add a macro button to the ribbon:

1. Right-click the ribbon and choose Customize The Ribbon.

2. In the list box on the right, choose the tab name where you want to add an icon.

3. Click the New Group button below the right list box. Excel adds a new entry called New Group (Custom) to the end of the groups in that ribbon tab.

4. To move the group to the left in the ribbon tab, click the up arrow icon on the right side of the dialog box several times.

5. To rename the group, click the Rename button. Type a new name, such as Report Macros. Click OK. Excel shows the group in the list box as Report Macros (Custom). Note that the word Custom does not appear in the ribbon.

6. Open the upper-left drop-down menu and choose Macros from the list. The Macros category is fourth in the list. Excel displays a list of available macros in the left list box.

7. Choose a macro from the left list box. Click the Add button in the center of the dialog box. Excel moves the macro to the right list box in the selected group. Excel uses a generic VBA icon for all macros.

8. Click the macro in the right list box. Click the Rename button at the bottom of the right list box. Excel displays a list of 180 possible icons. Choose an icon. Alternatively, type a friendly label for the icon, such as Format Report.

9. You can move the Report Macros group to a new location on the ribbon tab. Click Report Macros (Custom) and use the up and down arrow icons on the right of the dialog box.

10. Click OK to close the Excel Options dialog box. The new button appears on the selected ribbon tab.

Creating a macro button on the Quick Access Toolbar

You can add an icon to the Quick Access Toolbar to run a macro. If a macro is stored in the Personal Macro Workbook, you can have the button permanently displayed in the Quick Access Toolbar. If the macro is stored in the current workbook, you can specify that the icon should appear only when the workbook is open. Follow these steps to add a macro button to the Quick Access Toolbar:

1. Right-click the Quick Access Toolbar and choose Customize Quick Access Toolbar.

2. If your macro should be available only when the current workbook is open, open the upper-right drop-down menu and change For All Documents (Default) to For FileName.xlsm. Any icons associated with the current workbook are displayed at the end of the Quick Access Toolbar.

3. Open the upper-left drop-down menu and select Macros from the list. The Macros category is fourth in the list. Excel displays a list of available macros in the left list box.

4. Choose a macro from the left list box. Click the Add button in the center of the dialog box. Excel moves the macro to the right list box. Excel uses a generic VBA icon for all macros.

5. Click the macro in the right list box. Click the Modify button at the bottom of the right list box. Excel displays a list of 180 possible icons (see Figure 1-5). Choose an icon from the list. In the Display Name box, replace the macro name with a short name that appears in the ToolTip for the icon.

6. Click OK to close the Modify Button dialog box.

7. Click OK to close the Excel Options dialog box. The new button appears on the Quick Access Toolbar.

Assigning a macro to a form control, text box, or shape

If you want to create a macro specific to a workbook, you can store the macro in the workbook and attach it to a form control or any object on the sheet.

Follow these steps to attach a macro to a form control on the sheet:

1. On the Developer tab, click the Insert button to open its drop-down menu. Excel offers 12 form controls and 12 ActiveX controls in this one drop-down menu. The form controls are at the top, and the ActiveX controls are at the bottom. Most icons in the ActiveX section of the drop-down menu look identical to an icon in the form controls section of the drop-down menu. Click the Button Form Control icon at the upper-left corner of the Insert drop-down menu.

2. Move your cursor over the worksheet; the cursor changes to a plus sign.

3. Draw a button on the sheet by clicking and holding the left mouse button while drawing a box shape. Release the button when you have finished.

4. Choose a macro from the Assign Macro dialog box and click OK. The button is created with generic text, such as Button 1.

5. Type a new label for the button. Note that while you are typing, the selection border around the button changes from dots to diagonal lines to indicate that you are in Text Edit mode. You cannot change the button color while in Text Edit mode. To exit Text Edit mode, either click the diagonal lines to change them to dots or Ctrl+click the button again. Note that if you accidentally click away from the button, you should Ctrl+click the button to select it. Then drag the cursor over the text on the button to select the text.

6. Right-click the dots surrounding the button and select Format Control. Excel displays the Format Control dialog box, which has seven tabs across the top. If your Format Control dialog box has only a Font tab, you failed to exit Text Edit mode. If this occurred, close the dialog box, Ctrl+click the button, and repeat this step.

7. Use the settings in the Format Control dialog box to change the font size, font color, margins, and similar settings for the control. Click OK to close the Format Control dialog box when you have finished. Click a cell to deselect the button.

8. Click the new button to run the macro.

Macros can be assigned to any worksheet object, such as an icon, a shape, SmartArt graphics, or a text box. In Figure 1-6, the top button is a traditional button form control. The other images are an icon, a shape with WordArt, and a SmartArt graphic. To assign a macro to any object, right-click the object and select Assign Macro.

VB Editor settings

Several settings in the VB Editor enable you to customize this editor and assist you in writing your macros.

Under Tools, Options, Editor, you find several useful settings. All settings except for one are set correctly by default. The remaining setting requires some consideration on your part. This setting is Require Variable Declaration. By default, Excel does not require you to declare variables. I prefer disabling this setting because it can save time when you create a program. My coauthor prefers to change this setting to require variable declaration. This change forces the compiler to stop if it finds a variable that it does not recognize, which reduces misspelled variable names. Declaring variables with certain data types is important to catch data type-related errors. Whether you turn this setting on or keep it off is a matter of your personal preference.

The Project Explorer

The Project Explorer lists any open workbooks and add-ins that are loaded. If you click the + icon next to the VBA Project, you see that there is a folder containing Microsoft Excel objects. There can also be folders for forms, class modules, and standard modules. Each folder includes one or more individual components.

Right-clicking a component and selecting View Code or just double-clicking the components brings up any code in the Programming window. The exception is userforms, where double-clicking displays the userform in Design view.

To display the Project Explorer window, select View, Project Explorer from the menu or press Ctrl+R or locate the bizarre Project Explorer icon just below the Tools menu, sandwiched between Design Mode and Properties Window.

To insert a module, right-click your project, select Insert, and then choose the type of module you want. The available modules are as follows:

• Microsoft Excel objects—By default, a project consists of sheet modules for each sheet in the workbook and a single ThisWorkbook module. Code specific to a sheet such as controls or sheet events is placed on the corresponding sheet. Workbook events are placed in the ThisWorkbook module. You read more about events in Chapter 7, “Event programming.”

• Forms—Excel enables you to design your own forms to interact with the user. You read more about these forms in Chapter 10.

• Modules—When you record a macro, Excel automatically creates a module in which to place the code. Most of your code resides in these types of modules.

• Class modules—Class modules are Excel’s way of letting you create your own objects. They also allow pieces of code to be shared among programmers without the programmer’s needing to understand how it works. You read more about class modules in Chapter 9, “Creating classes and collections.”

The Properties window

The Properties window enables you to edit the properties of various components such as sheets, workbooks, modules, and form controls. The properties list varies according to what component is selected. To display this window, select View, Properties Window from the menu, press F4, or click the Project Properties icon on the toolbar.

Recording the macro

The macro recorder is now recording your every move. For this reason, perform your steps in the exact order without extraneous actions. If you accidentally move to column F, type a value, clear the value, and then move back to E to enter the first total, the recorded macro will blindly make that same mistake day after day after day. Recorded macros move fast, but there is nothing like watching the macro recorder play out your mistakes repeatedly.

Carefully execute all the actions necessary to produce the report. After you have performed the final step, click the Stop Recording button in the Developer tab of the ribbon.

Examining code in the Programming window

Let’s look at the code you just recorded in the “Preparing to record a macro” section. Don’t worry if it doesn’t make sense yet.

To open the VB Editor, press Alt+F11. In your VBA project (MacroToImportInvoices.xlsm), find the component Module1, right-click the module, and select View Code. Notice that some lines start with an apostrophe; these are comments and are ignored by the program. The macro recorder starts your macros with a few comments, using the description you entered in the Record Macro dialog box. The comment for the keyboard shortcut is there to remind you of the shortcut.

Application.MacroOptions Macro:="ImportInvoice", _ 
 Description:="", ShortcutKey:="j"

Recorded macro code is usually pretty tidy (see Figure 1-10). Each line of code that is not a comment is indented four characters. If a line is longer than 100 characters, the recorder breaks it into multiple lines and indents the continued lines an additional four characters. To continue a line of code, type a space and an underscore at the end of the first line and then continue the code on the next line. Don’t forget the space before the underscore. Using an underscore without the preceding space causes an error.

Consider that the following seven lines of recorded code are actually only one line of code that has been broken into seven lines for readability:

Click here to view code image

Workbooks.OpenText Filename:="C:somepathinvoice.txt", _
 Origin:=437, StartRow:=1, DataType:=xlDelimited, _
 TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
 Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _
 Other:=False, FieldInfo:=Array(Array(1, 3), Array(2, 1), _
 Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
 Array(7, 1)), TrailingMinusNumbers:=True

Counting this as one line, the macro recorder was able to record the 21-step process in 14 lines of code, which is pretty impressive.

Test each macro

It is always a good idea to test macros. To test your new macro, return to the regular Excel interface by pressing Alt+F11 or Alt+Q. Close Invoice.txt without saving any changes. MacroToImportInvoices.xlsm is still open.

Press Ctrl+I to run the recorded macro. It should work beautifully if you completed the steps correctly. The data is imported, totals are added, bold formatting is applied, and the columns are made wider. This seems like a perfect solution (see Figure 1-11).

Running the macro on another day produces undesired results

After testing the macro, be sure to save your macro file to use on another day. But suppose that the next day, after receiving a new Invoice.txt file from the system, you open the macro and press Ctrl+I to run it, and disaster strikes. The data for June 5 happened to have 9 invoices, but the data for June 6 now has 17 invoices. The recorded macro blindly added the totals in Row 11 because this was where you put the totals when the macro was recorded (see Figure 1-12).

For those of you working along using the sample files in this book, follow these steps to try importing data for another day:

1. Close Invoice.txt in Excel.

2. In Windows Explorer, rename Invoice.txt to be Invoice1.txt.

3. In Windows Explorer, rename Invoice2.txt to be Invoice.txt.

4. Return to Excel and the MacroToImportInvoices.xlsm workbook.

5. Press Ctrl+I to run the macro with the larger data set.

This problem arises because the macro recorder is recording all your actions in Absolute mode by default. As an alternative to using the default state of the macro recorder, the next section discusses relative recording and how it might get you closer to the desired solution.

Possible solution: Use relative references when recording

By default, the macro recorder records all actions as absolute actions. If you navigate to row 11 when you record the macro, the macro will always go to row 11 when the macro is run. This is rarely appropriate when dealing with variable numbers of rows of data. The better option is to use relative references when recording.

Macros recorded with absolute references note the actual address of the cell pointer, such as A11. Macros recorded with relative references note that the cell pointer should move a certain number of rows and columns from its current position. For example, if the cell pointer starts in cell A1, the code ActiveCell.Offset(16, 1).Select would move the cell pointer to B17, which is the cell 16 rows down and 1 column to the right.

Although relative recording is appropriate in most situations, there are times when you need to do something absolute while recording a macro. Here’s a great example: After adding the totals to a data set, you need to return to row 1. If you simply click row 1 while in Relative mode, Excel records that you want to select the row 10 rows above the current row. This works with the first invoice file but not with longer or shorter invoice files. Here are two workarounds:

• Toggle relative recording off, click row 1, and then toggle relative recording back on.

• Keep relative recording turned on. Display the Go To dialog box by pressing F5. Type A1 and click OK. The Go To dialog box gets recorded as always, going to the absolute address you typed, even if relative recording is turned on. A variation of this method is used in the following example.

The next example shows the same task as before but uses relative references this time. The solution will be much closer to working correctly.

Never use AutoSum or Quick Analysis while recording a macro

There actually is a macro recorder solution to the current problem with recording an AutoSum. It is important to recognize that the macro recorder will never correctly record the intent of the AutoSum button.

If you are in cell E99 and click the AutoSum button, Excel starts scanning from cell E98 upward until it locates a text cell, a blank cell, or a formula. It then proposes a formula that sums everything between the current cell and the found cell.

However, the macro recorder records the particular result of that search on the day that the macro was recorded. Rather than record something along the lines of “do the normal AutoSum logic,” the macro recorder inserts a single line of code to add up the previous 98 cells.

Excel 2013 added the Quick Analysis feature. Select E2:G99; click the Quick Analysis icon that appears below and to the right of a rectangular selection; choose Totals, Sum at Bottom; and you get the correct totals in row 100. The macro recorder hard-codes the formulas to always appear in row 100 and to always total row 2 through row 99.

The somewhat bizarre workaround is to type a SUM function that uses a mix of relative and absolute row references. If you type =SUM(E$2:E10) while the macro recorder is running, Excel correctly adds code that always sums from a fixed row two down to the relative reference that is just above the current cell.

Here is the resulting code, with a few comments:

Click here to view code image

Sub FormatInvoice3()
 ' FormatInvoice3 Macro
' Import. Total. Format.
' Keyboard Shortcut: Ctrl+K
Workbooks.OpenText Filename:="C:Datainvoice.txt", _
    Origin:=437, StartRow:=1, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
     Tab:=False, Semicolon:=False, Comma:=True, Space:=False, _
     Other:=False, FieldInfo:=Array(Array(1, 3), Array(2, 1), _
     Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
     Array(7, 1)), TrailingMinusNumbers:=True
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(0, 4).Range("A1").Select
Selection.FormulaR1C1 = "=SUM(R2C:R[-1]C)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:C1"), _
     Type:=xlFillDefault
ActiveCell.Range("A1:C1").Select
ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Activate
Selection.Font.Bold = True
Application.Goto Reference:="R1C1:R1C7"
Selection.Font.Bold = True
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
End Sub

This third macro consistently works with a data set of any size.

Four tips for using the macro recorder

You will rarely be able to record 100% of your macros and have them work. However, you will get much closer by using the following four tips.