Excel-VBA: Recording the Macro

Taking the First Steps

This tutorial describes the steps you take prior to recording the macro. In other words, you need to make a few preparations before the fun begins.

1. Start Excel if it’s not already running.

2. If necessary, create a new, empty workbook (Ctrl+N is my favorite way to do that).

3. 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 than the other buttons, then you’re in good shape. If the Use Relative References button is the same color as the other buttons, then you need to click it.

I explain more about the Use Relative References button later. For now, just make sure that the option is turned on. When it’s turned on, it will be a different color.

Recording the Macro

Here comes the hands-on part. Follow these instructions carefully:

1. Select a cell — any cell will do.

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

Figure 2-2: The Record Macro dialog box appears when you’re about to record a macro.

3. Enter a name for the macro.
Excel provides a default name, but it’s better to use a more descriptive name. NameAndTime (with no spaces) is a good name for this macro.

4. Click in 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, then you can execute the macro by pressing a key combination — in this case, Ctrl+Shift+N.

5. Make sure the Store Macro In setting is This Workbook.

6. You can enter some text in the Description box if you like. This is optional. Some people like to describe what the macro does (or is sup-posedto do).


7. Click OK.

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

8. Type your name in the active cell.

9. Move the cell pointer to the cell below, and enter this formula:

=NOW()

The formula displays the current date and time.

10. Select the formula cell and press Ctrl+C to copy that cell to the Clipboard.

11. Choose Home➪Clipboard➪Paste➪Values.

This command converts the formula to its value.

12. With the date cell selected, press Shift+up arrow to select that cell and the one above it (which contains your name).

13. Use the controls in the Home➪Font group to change the formatting to Bold, and make the font size 16 point.

14. Choose Developer➪Code➪Stop Recording.

The macro recorder is turned off.

Congratulations! You just created your first Excel VBA macro.

Testing the Macro

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 are dis-played in large, bold letters.

TIP: Another way to execute the macro is to choose Developer➪Code➪Macros (or press Alt+F8) to display the Macros dialog box. Select the macro from the list (in this case, NameAndTime) and click Run. Make sure you select the cell that will hold your name before executing the macro.

Examining the Macro

So far, 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 work-book, but you need to activate the Visual Basic Editor (VBE, for short) to see it.

Follow these steps to see the macro:

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

2. 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 treeand can be expanded (to show more information) or contracted (to show less information).

Figure 2-3: The Visual Basic Editor is where you view and edit VBA code

The VBE uses quite a few different windows, any of which can be either 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.

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

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

5. Double-click Module1.

The VBA code in that module is displayed in a Code window. Figure 2-4 shows how it looks on my screen. Your screen may not look exactly the same.

Figure 2-4: The VBE displays the VBA code in Module1 of Book1.

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 (also known as a Sub procedure) consists of several statements. Excel executes the statements one by one, from top to bottom. A statement preceded by an apostrophe (’) is a comment. Comments are included only for your information and are essentially ignored. In other words, Excel skips right over comments.

The first actual 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.

Saving Workbooks That Contain Macros

If you store one or more macros in a workbook, the file must be saved with “macros enabled.” 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-5. You need to click No, and then choose Excel Macro-Enabled Workbook (*.xlsm) from the Save As Type drop-down list.

Figure 2-5: If your workbook contains macros, and you attempt to save it in a non-macro file format, Excel warns you.