Chapter 2
In This Chapter
Developing a useful VBA macro: A hands-on, step-by-step example
Recording your actions by using Excel's macro recorder
Examining and testing recorded code
Changing a recorded macro
Dealing with macro security issues
I’m not much of a swimmer, but I have found that the best way to get into a cold body of water is to jump right in — no sense prolonging the agony. By wading through this chapter, you can get your feet wet immediately but avoid getting in over your head.
By the time you reach the end of this chapter, you may start feeling better about this Excel programming business, and you’ll be glad you took the plunge. This chapter provides a step-by-step demonstration of how to develop a simple but useful VBA macro.
Before you can call yourself an Excel programmer, you must go through the initiation rites. That means you need to make a small change so Excel will display a new tab at the top of the screen: Developer. Getting Excel to display the Developer tab is easy (and you only have to do it one time). Just follow these steps:
Click OK.
You're back to Excel with a brand-new tab: Developer.
When you click the Developer tab, the Ribbon displays information that is of interest to programmers (that's you!). Figure 2-1 shows how the Ribbon looks when the Developer tab is selected in Excel 2016.
In this section, I describe how to create your first macro. The macro that you’re about to create does this:
This macro won't be winning any prizes in the Annual VBA Programmer's Competition, but everyone must start somewhere. The macro accomplishes all these steps in a single action. As I describe in the following sections, you start by recording your actions as you go through these steps. Then you test the macro to see whether it works. Finally, you edit the macro to add some finishing touches. Ready?
This section describes the steps you take prior to recording the macro. In other words, you need to make a few preparations before the fun begins:
If necessary, create a new, empty workbook.
Pressing Ctrl+N is my favorite way to do that.
Click the Developer tab, and take a look at the Use Relative References button in the Code group.
If the color of that button is different from the other buttons, you're in good shape. If the Use Relative References button is the same color as the other buttons, you need to click it to enable this option.
I explain more about the Use Relative References button in Chapter 6. For now, just make sure that the option is turned on. When it’s turned on, the Use Relative References button will be a different color.
Here comes the hands-on part. Follow these instructions carefully:
Select a cell.
Any cell will do.
Choose Developer ⇒ Code ⇒ Record Macro or click the macro recording button on the status bar.
The Record Macro dialog box appears, as shown in Figure 2-2.
Enter a name for the macro.
Excel provides a default name (something like Macro1), but it’s better to use a more descriptive name. NameAndTime (with no spaces) is a good name for this macro.
Click the Shortcut Key box, and enter Shift+N (for an uppercase N) as the shortcut key.
Specifying a shortcut key is optional. If you do specify one, you can execute the macro by pressing a key combination — in this case, Ctrl+Shift+N.
You can enter some text in the Description box, if you like.
This step is optional. Some people like to describe what the macro does (or is supposed to do).
Click OK.
The Record Macro dialog box closes, and Excel’s macro recorder is turned on. From this point, Excel monitors everything you do and converts it to VBA code.
=NOW()
The formula displays the current date and time.
Choose Home ⇒ Clipboard ⇒ Paste ⇒ Values (V).
This command converts the formula to its value.
Choose Developer ⇒ Code ⇒ Stop Recording.
The macro recorder is turned off.
Congratulations! You just created your first Excel VBA macro. You may want to phone your mother and tell her the good news.
Now you can try out this macro and see whether it works properly. To test your macro, move to an empty cell and press Ctrl+Shift+N.
In a flash, Excel executes the macro. Your name and the current date and time are displayed in large, bold letters.
You’ve recorded a macro, and you’ve tested it. If you’re a curious type, you’re probably wondering what this macro looks like. And you might even wonder where it’s stored.
Remember when you started recording the macro? You indicated that Excel should store the macro in This Workbook. The macro is stored in the workbook, but you need to activate the Visual Basic Editor (VBE, for short) to see it.
Follow these steps to see the macro:
Choose Developer ⇒ Code ⇒ Visual Basic (or press Alt+F11).
The Visual Basic Editor program window appears, as shown in Figure 2-3. This window is highly customizable, so your VBE window may look a bit different. The VBE program window contains several other windows and is probably very intimidating. Don’t fret; you’ll get used to it.
In the VBE window, locate the window called Project.
The Project window (also known as the Project Explorer window) contains a list of all workbooks and add-ins that are currently open. Each project is arranged as a tree and can be expanded (to show more information) or contracted (to show less information).
The VBE uses quite a few different windows, any of which can be open or closed. If a window isn’t immediately visible in the VBE, you can choose an option from the View menu to display the window. For instance, if the Project window is not visible, you can choose View ⇒ Project Explorer (or press Ctrl+R) to display it. You can display any other VBE window in a similar manner. I explain more about the components of the VBE in Chapter 3.
Select the project that corresponds to the workbook in which you recorded the macro.
If you haven’t saved the workbook, the project is probably called VBAProject (Book1).
Click the plus sign (+) to the left of the folder named Modules.
The tree expands to show Module1, which is the only module in the project.
Double-click Module1.
The VBA code in that module is displayed in a Code window. Figure 2-3, shown earlier, shows how it looks on my screen. Your screen may not look exactly the same. The code that's recorded depends on the specific actions you made while recording the macro.
At this point, the macro probably looks like Greek to you. Don’t worry. Travel a few chapters down the road, and all will be as clear as the view from Olympus.
The NameAndTime macro consists of several statements. Excel executes the statements one by one, from top to bottom. A statement that's preceded by an apostrophe (’) is a comment. Comments are included only for your information and are ignored. In other words, Excel skips right over comments.
The first VBA statement (which begins with the word Sub) identifies the macro as a Sub procedure and gives its name; you provided this name before you started recording the macro. If you read through the code, you may be able to make sense of some of it. You see your name, the formula you entered, and lots of additional code that changes the font. The Sub procedure ends with the End Sub statement.
As you might expect, you can not only view your macro in the VBE, but also change it. Even though you probably have no idea what you're doing at this point, I bet you can make these changes to the code:
Selection.Font.Italic = True
After you’ve made your changes, jump back to Excel and try out the revised macro to see how it works. Just as you can press Alt+F11 in Excel to display the VBE, you can press Alt+F11 in the VBE to switch back to Excel.
If you store one or more macros in a workbook, the file must be saved as a macro-enabled file type. In other words, the file must be saved with an XLSM extension rather than the normal XLSX extension.
For example, when you save the workbook that contains your NameAndTime macro, the file format in the Save As dialog box defaults to XLSX (a format that cannot contain macros). Unless you change the file format to XLSM, Excel displays the warning shown in Figure 2-4. You need to click No and then choose Excel Macro-Enabled Workbook (*.xlsm) from the Save As Type drop-down list.
Macro security is a key feature in Excel. The reason is that VBA is a powerful language — so powerful that it's possible to create a macro that can do serious damage to your computer. A macro can delete files, send information to other computers, and even destroy Windows so that you can't even start your system.
The macro security features introduced in Excel 2007 were created to help prevent these types of problems.
Figure 2-5 shows the Macro Settings section of the Trust Center dialog box. To display this dialog box, choose Developer ⇒ Code ⇒ Macro Security.
By default, Excel uses the Disable All Macros with Notification option. With this setting in effect, if you open a workbook that contains macros (and the file is not digitally “signed” or stored in a trusted location), Excel displays a warning like the one in Figure 2-6. If you are certain that the workbook comes from a trusted source, click Enable Macros, and the macros will be enabled.
Excel will remember if you've designated a workbook to be safe. So the next time you open it, you won't see the Security Warning.
Perhaps the best way to handle macro security is to designate one or more folders as trusted locations. All the workbooks in a trusted location are opened without a macro warning. You designate trusted folders in the Trusted Locations section of the Trust Center dialog box.
If you want to find out what the other macro security settings imply, press F1 while the Macro Settings section of the Trust Center dialog box is in view. You'll get a Help screen that describes the security settings.
By the time you finish this book, you’ll completely understand how the NameAndTime macro works — and you’ll be able to develop more sophisticated macros. For now, I wrap up the chapter with a few additional points about the macro:
Congratulations. You’ve been initiated into the world of Excel programming. (Sorry, there’s no secret handshake or decoder ring.) I hope this chapter helps you realize that Excel programming is something you can actually do — and even live to tell about. Keep reading. Subsequent chapters almost certainly answer any questions you have, and you’ll soon understand exactly what you did in this hands-on session.