IN THIS CHAPTER
Knowing Your Tools: The Developer Tab
Overview of Recording, Storing, and Running a Macro
Using New File Types in Excel 2010
Understanding Shortcomings of the Macro Recorder
Visual Basic for Applications (VBA) combined with Microsoft Excel is probably the most powerful tool available to you. VBA is sitting on the desktops of 500 million users of Microsoft Office, and most have never figured out how to harness the power of VBA in Excel. Using VBA, you can speed the production of any task in Excel. If you regularly use Excel to produce a series of monthly charts, you can have VBA do the same task for you in a matter of seconds.
There are two barriers to learning successful VBA programming. First, Excel’s macro recorder is flawed and does not produce workable code for you to use as a model. Second, for many who learned a programming language such as BASIC, the syntax of VBA is horribly frustrating.
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.
With Lotus 1-2-3, you could record a macro today, 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.
The code generated by the macro recorder was unlike anything I had ever seen. It said this was “Visual Basic” (VB). I 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 wizard 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 percent 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 a similar chance that you were not happy with the results.
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 will not only teach you 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 will decode VBA so that you can easily pick through recorded macro code and understand what is happening.
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 work for MrExcel Consulting. In this role, we have automated reports for hundreds of clients. The stories are often similar: The MIS 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 will work today but 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. You also learn the fundamentals of the Visual Basic environment.
Let’s start with a basic overview of the tools needed to use VBA. By default, Microsoft hides the VBA tools. You need to complete the following steps to change a setting in Excel options to access the Developer tab.
Excel displays the Developer tab shown in Figure 1.1.
The Code group on the Developer tab contains the icons used for recording and playing back VBA macros, as listed here:
• Visual Basic icon—Opens the Visual Basic Editor.
• Macros icon—Displays the Macro dialog, where you can choose to run or edit a macro from the list of macros.
• Record Macro icon—Begins the process of recording a macro.
• Use Relative Reference icon—Toggles between using relative or absolute recording. With relative recording, Excel will record that you move down three cells. With absolute recording, Excel will record that you selected cell A4.
• Macro Security icon—Accesses the Trust Center, where you can choose to allow or disallow macros to run on this computer.
The Controls group of the Developer tab contains an Insert menu where you can access a variety of programming controls that can be placed on the worksheet. See “Assigning a Macro to a Form Control, Text Box, or Shape,” later in this chapter. Other icons in this group enable you to work with the on-sheet controls. The Run Dialog button enables you to display a custom dialog box or userform that you designed in VBA. For more on userforms, see Chapter 10, “Userforms: An Introduction.”
The XML group of the Developer ribbon contains tools for importing and exporting XML documents.
After VBA macros were used as the delivery method for some high-profile viruses, Microsoft changed the default security settings to prevent macros from running. Therefore, before we can begin discussing the recording of a macro, we need to show you how to adjust the default settings.
In Excel 2010, you can either globally adjust the security settings or control macro settings for certain workbooks by saving the workbooks in a trusted location. Any workbooks stored in a folder that is marked as a trusted location will automatically have its macros enabled.
You can find the macro security settings under the Macro Security icon on the Developer tab. When you click this icon, the Macro Settings category of the Trust Center is displayed. You can use the left navigation bar in the dialog to access the Trusted Locations list.
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:
Use care when selecting a trusted location. When you double-click an Excel attachment in an e-mail, Outlook stores the file in a temporary folder on your C: drive. You will not want to globally add C: and all subfolders to the Trusted Locations list.
Although trusted locations are not new in Excel 2010, Microsoft has made the process of adding trusted locations more discoverable in Excel 2010.
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. Select the second option, Disable All Macros with Notification. A description of each option follows:
• Disable All 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 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 All Macros with Notification—This setting is similar to Medium security in Excel 2003 and is the recommended setting. In Excel 2003, a Medium setting caused a box to be displayed when you opened a file containing macros. This box forced the person to choose either Enable or Disable. Many novice Excel users randomly choose from this box. In Excel 2010, the message is displayed in the Message Area that macros have been disabled. You can choose to enable the content by clicking that option, as shown in Figure 1.3.
• Disable All 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 a bit of a hassle if you just want to write macros for your own use.
• Enable All Macros (Not Recommended: Potentially Dangerous Code Can Run)—This setting is similar to Low macro security in Excel 2003. Although it requires the least amount of hassle, it also opens your computer up to attacks from malicious Melissa-like viruses. Microsoft suggests that you do not use this setting.
It is recommended that you set your macro settings to Disable All Content with Notification. If you use this setting and open a workbook that contains macros, you will see a Security Warning in the area just above the formula bar. Assuming you were 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.
After you enable macros in a workbook stored on a local hard drive and then save the workbook, Excel will remember that you previously enabled macros in this workbook. The next time you open this workbook, macros will be automatically enabled.
Recording a macro is useful when you do not have experience in writing lines of code in a macro. As you gain more knowledge and experience, you will begin to record lines of code less frequently.
To begin recording a macro, select Record Macro from the Developer tab. Before recording begins, Excel displays the Record Macro dialog box, as shown in Figure 1.4.
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. Assuming you will soon be creating many macros, use a meaningful name for the macro. A name such as FormatReport is more useful than Macro1.
The second field in the Record Macro dialog box is a shortcut key. If you type J
in this field, and then press Ctrl+J, this macro runs. Note that most of the lowercase shortcuts from Ctrl+a through Ctrl+z already have a use in Excel. Rather than being limited to the unassigned Ctrl+j, you can hold down the Shift key and type Shift+A through Shift+Z in the shortcut box. This will assign the macro to Ctrl+Shift+A.
You can reuse a shortcut key for a macro. If you assign a macro to Ctrl+c, Excel will run your macro instead of doing the normal action of copy.
In the Record Macro dialog box, choose where you want to save a macro when it is recorded: Personal Macro Workbook, New Workbook, This Workbook. It is recommended that you store macros related to a particular workbook in This Workbook.
The Personal Macro Workbook (Personal.xlsb
) 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 will open 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.
It is not recommended you use the personal workbook for every macro you save. Save only those macros that assist you in general tasks—not in tasks that are performed in a specific sheet or workbook.
The fourth box in the Record Macro dialog is for a description. This description is added as a comment to the beginning of your macro. Note that legacy versions of Excel automatically noted the date and username of the person recording the macro. Excel 2010 no longer automatically inserts this information in the Description field.
After you select the location where you want to store the macro, click OK. Record your macro. When you are finished recording the macro, click the Stop Recording icon in the Developer tab.
You can also access a Stop Recording icon in the lower-left corner of the Excel window. Look for a small blue square to the right of the word Ready in the status bar. Using this Stop button might be more convenient than returning to the Developer tab. After you record your first macro, this area will usually have a Record Macro icon, which is a small red dot on an Excel worksheet.
If you assigned a shortcut key to your macro, you can play it by pressing the key combination. Macros can also be assigned to toolbar buttons, forms controls, drawing objects, or you can run them from the Visual Basic toolbar.
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. Follow these steps to add a macro button to the Ribbon:
You can add an icon to the Quick Access toolbar to run your macro. If your 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:
If you want to create a macro specific to a workbook, 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:
Macros can be assigned to any worksheet object such as clip art, a shape, SmartArt graphics, or text box. In Figure 1.6, the top button is a traditional button form control. The other images are clip art, a shape with WordArt, and a SmartArt graphic. To assign a macro to any object, right-click the object, and select Assign Macro.
Excel 2010 offers support for four file types. Macros are not allowed to be stored in the default file type. You have to use the Save As setting for all of your macro workbooks, or you can change the default file type used by Excel 2010.
The available files types are as follows:
• Excel Workbook (.xlsx)—Files are stored as a series of XML objects and then zipped into a single file. This new file-saving paradigm in Excel 2010 allows for significantly smaller file sizes. It also allows other applications (even Notepad!) to edit or create Excel workbooks. Unfortunately, macros cannot be stored in files with an .xlsx extension.
• Excel Macro-Enabled Workbook (.xlsm)—This is similar to the default .xlsx format, except macros are allowed. The basic concept is that if someone has an .xlsx file, he or she will not need to worry about malicious macros. However, if they see an .xlsm file, they should be concerned that there might be macros attached.
• Excel Binary Workbook (.xlsb)—This is a binary format designed to handle the larger 1.1-million-row grid size in Excel 2010. Legacy versions of Excel stored their files in a proprietary binary format. Although binary formats might load quicker, they are more prone to corruption, and a few lost bits can destroy the whole file. Macros are allowed in this format.
• Excel 97-2003 Workbook (.xls)—This format produces files that can be read by anyone using legacy versions of Excel. Macros are allowed in this binary format; however, when you save in this format, you lose access to any cells outside of A1:IV65536. In addition, if someone opens the file in Excel 2003, he or she will lose access to anything that used features introduced in Excel 2007 or later.
To avoid having to choose a macro-enabled workbook in the Save As dialog, you can customize your copy of Excel to always save new files in the .xlsm format by following these steps:
Although you and I are not afraid to use macros, I have encountered some people who seem to freak out when they see the .xlsm file type. They actually seem angry that I sent them an .xlsm file that did not have any macros. Their reaction seemed reminiscent of King Arthur’s “You got me all worked up!” line in Monty Python and the Holy Grail.
If you encounter someone who seems to have a fear of the .xlsm file type, remind them of these points:
• Every workbook created in the past 20 years could have had macros, but in fact, most did not.
• If someone is trying to avoid macros, they should use the security settings to prevent macros from running anyway (refer to Figure 1.3). They can still open the .xlsm file to get the data in the spreadsheet.
With these arguments, I hope you can overcome any fears of the .xlsm file type so that it can be your default file type.
Figure 1.7 shows an example of the typical VB Editor screen. You can see three windows: Project Explorer, the Properties window, and the Programming window. Don’t worry if your window doesn’t look exactly like this because you will see how to display the windows you need in this review of the editor.
Several settings in the VB Editor enable you to customize this editor. The following subsection covers the setting that will help with your programming.
Under Tools, Options, Editor, you will 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 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. It is a matter of your personal preference if you turn this setting on or keep it off.
The Project Explorer lists any open workbooks and add-ins that are loaded. If you click the + icon next to the VBA Project, you will see that there is a folder with 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, and then press Ctrl+R or click the Project Explorer icon on the toolbar.
Figure 1.8 shows the Project Explorer pane. This pane can show Microsoft Excel objects, userforms, modules, and class modules.
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 learn more about events in Chapter 9, “Event Programming.”
• Forms—Excel allows you to design your own forms to interact with the user. You learn 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 will reside 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 needing to understand how it works. You will learn more about class modules in Chapter 22, “Creating Classes, Records, and Collections.”
The Properties window enables you to edit the properties of various components such as sheets, workbooks, modules, and form controls. The Property 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.
Suppose you work in an accounting department. Each day you receive a text file from the company system showing all the invoices produced the prior day. This text file has commas separating each field. The columns in the file are InvoiceDate, InvoiceNumber, SalesRepNumber, CustomerNumber, ProductRevenue, ServiceRevenue, and ProductCost (see Figure 1.9).
Each morning, you manually import this file into Excel. You add a total row to the data, bold the headings, and then print the report for distribution to a few managers.
This seems like a simple process that would be ideally suited to using the macro recorder. However, due to some problems with the macro recorder, your first few attempts might not be successful. The following case study explains how to overcome these problems.
Let’s look at the code you just recorded from the case study. Don’t worry if it doesn’t make sense yet.
To open the VB Editor, press Alt+F11. In your VBA Project (MacroToImportInvoices.xls
), 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. The comment for the Keyboard Shortcut is there to remind you of the shortcut.
The comment does not assign the shortcut. If you change the comment to be Ctrl+J, it does not change the shortcut. You must change the setting in the Macro dialog box in Excel or run this line of code:
Recorded macro code is usually pretty neat (see Figure 1.11). Each noncomment line of code is indented four characters. If a line is longer than 100 characters, the recorder breaks it into multiple lines and indents the lines an additional four characters. To continue a line of code, type a space and an underscore at the end of the line.
Note that the physical limitations of this book do not allow 100 characters on a single line. Therefore, the lines will be broken at 80 characters so that they fit on a page. For this reason, your recorded macro might look slightly different from the ones that appear in this book.
Consider that the following seven lines of recorded code is actually only one line of code that has been broken into seven lines for readability:
Counting this as one line, the macro recorder was able to record our 21-step process in 14 lines of code, which is pretty impressive.
Each action you perform in the Excel user interface might equate to one or more lines of recorded code. Some actions might generate a dozen lines of code.
It is always a good idea to test macros. To test your new macro, return to the regular Excel interface by pressing Alt+F11. Close Invoice.txt
without saving any changes. MacroToImportInvoices.xls
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.12).
After testing the macro, be sure to save your macro file to use on another day. The next day, after receiving a new Invoice.txt
file from the system, you open the macro, press Ctrl+I to run it, and disaster strikes. The data for June 6 happened to have 9 invoices, while the data for the June 7 has 17 invoices. However, the recorded macro blindly added the totals in Row 12 because this was where you put the totals when the macro was recorded (see Figure 1.13).
This problem arises because the macro recorder is recording all your actions in absolute mode by default. Instead of using the default state of the macro recorder, the next section discusses relative recording and how this might get you closer to a final solution.
By default, the macro recorder records all actions as absolute actions. If you navigate to Row 11 when you record the macro on Monday, 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.
Let’s try the same case study again, this time using relative references. The solution will be much closer to working correctly.
Open MacroToImportInvoices.xls
and run the new macro with Ctrl+J. This time, everything should look good with the totals in the correct places. Look at Figure 1.16—see anything out of the ordinary?
If you aren’t careful, you might print these reports for your manager. If you did, you would be in trouble. When you look in cell E19, Excel has inserted a green triangle to tell you to look at the cell. If you happened to try this back in Excel 95 or Excel 97 before SmartTags, there would not have been an indicator that anything was wrong.
When you move the cell pointer to E19, an alert indicator pops up near the cell. This indicator tells you the formula fails to include adjacent cells. If you look in the formula bar, you will see that the macro totaled only from Row 10 to Row 18. Neither the relative recording nor the nonrelative recording is smart enough to replicate the logic of the AutoSum button.
At this point, some people would give up. However, imagine that you might have had fewer invoice records on this particular day. Excel would have rewarded you with the illogical formula of =SUM(E6:E1048574)
and a circular reference, as shown in Figure 1.17.
If you have tried using the macro recorder, most likely you would run into similar problems as the ones produced in the last two case studies. Although this is frustrating, you should be happy to know that the macro recorder actually gets you 95 percent of the way to a useful macro.
Your job is to recognize where the macro recorder is likely to fail and then to be able to dive into the VBA code to fix the one or two lines that require adjusting to have a perfect macro. With some added human intelligence, you can produce awesome macros to speed up your daily work.
If you are like me, you are cursing Microsoft about now. We have wasted a good deal of time over a couple of days, and neither macro works. What makes it worse is that this sort of procedure would have been handled perfectly by the old Lotus 1-2-3 macro recorder introduced in 1983. Mitch Kapor solved this problem 24 years ago, and Microsoft still can’t get it right.
Did you know that up through Excel 97, Microsoft Excel secretly ran Lotus command-line macros? I found this out right after Microsoft quit supporting Excel 97. At that time, a number of companies upgraded to Excel XP, which no longer supported the Lotus 1-2-3 macros. Many of these companies hired us to convert the old Lotus 1-2-3 macros to Excel VBA. It is interesting that from Excel 5, Excel 95, and Excel 97, Microsoft offered an interpreter that could handle the Lotus macros that solved this problem correctly, yet their own macro recorder couldn’t (and still can’t!) solve the problem.
There actually is a macro-recorder solution to the current problem. 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.
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 will always sum 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:
This third macro will consistently work with any size dataset.
To see a demo of recording this macro, search for Excel VBA 1 at YouTube.
You will rarely be able to record 100 percent of your macros and have them work. However, you will get much closer by using these three tips demonstrated in the following subsections.
Microsoft should have made this setting be the default. Unless you specifically need to move to Row 1 from the bottom of a dataset, you should usually leave the Use Relative References button in the Developer tab turned on.
If you are at the top of a dataset and need to move to the last cell with data, you can press Ctrl+down arrow or press the End key and then the down-arrow key.
Similarly, to move to the last column in the current row of the dataset, press Ctrl+right arrow or press End and then press the right-arrow key.
By using these navigation keys, you can jump to the end of the dataset, no matter how many rows or columns you have today.
The macro recorder will not record the “essence” of the AutoSum button. Instead, it will hard-code the formula that resulted from pressing the AutoSum button. This formula does not work any time you have more or fewer records in the dataset.
Instead, type a formula with a single dollar sign, such as =SUM(E$2:E10)
. When this is done, the macro recorder records the first E$2
as a fixed reference and starts the SUM
range directly below the Row 1 headings. Provided the active cell is E11, the macro recorder recognizes E10 as a relative reference pointing directly above the current cell.
Chapter 2, “This Sounds Like BASIC, So Why Doesn’t It Look Familiar?” examines the three macros you recorded in this chapter to make more sense out of them. After you know how to decode the VBA code, it will feel natural to either correct the recorded code or simply write code from scratch. Hang on through one more chapter. You’ll soon learn that VBA is the solution, and you’ll be writing useful code that works consistently.