Michael Alexander - Excel 2019 Power Programming with VBA

Excel stored your newly recorded macro in a new module that it created automatically and named Module1. To view the code in this module, you must activate the Visual Basic Editor. You can activate the VB Editor in either of two ways:

Press Alt+F11.

Choose Developer ➪ Code ➪ Visual Basic.

In the VB Editor, the Project window displays a list of all open workbooks and add-ins. This list is displayed as a tree diagram on the left of the screen, which you can expand or collapse. The code that you recorded previously is stored in Module1in the current workbook. When you double-click Module1, the code in the module appears in the Code window.

If you don't see a Project window in the VB Editor, you can activate it by going up to the menu and selecting View ➪ Project Explorer. Alternatively, you can use the keyboard shortcut Ctrl+R.

The macro should look something like this:

Sub MyName() '' MyName Macro '' Keyboard Shortcut: Ctrl+Shift+N ActiveCell.FormulaR1C1 = "Michael Alexander" End Sub

The macro recorded is a Subprocedure that is named MyName. The statements tell Excel what to do when the macro is executed.

Notice that Excel inserted some comments at the top of the procedure. These comments are some of the information that appeared in the Record Macro dialog box. These comment lines (which begin with an apostrophe) aren't really necessary, and deleting them has no effect on how the macro runs. If you ignore the comments, you'll see that this procedure has only one VBA statement.

ActiveCell.FormulaR1C1 = "Michael Alexander"

This single statement causes the name you typed while recording to be inserted into the active cell.

Before you recorded this macro, you set an option that assigned the macro to the Ctrl+Shift+N shortcut key combination. To test the macro, return to Excel by using either of the following methods:

Press Alt+F11.

Click the View Microsoft Excel button on the standard toolbar in the VB Editor window.

When Excel is active, activate a worksheet. (It can be in the workbook that contains the VBA module or in any other workbook.) Select a cell and press Ctrl+Shift+N. The macro immediately enters your name into the cell.

In the preceding example, notice that you selected your target cell before you started recording your macro. This step is important. If you select a cell while the macro recorder is turned on, the actual cell that you selected will be recorded into the macro. In such a case, the macro would always format that particular cell, and it would not be a general-purpose macro.

After you record a macro, you can make changes to it. For example, assume that you want your name to be bold. You could re-record the macro, but this modification is simple, so editing the code is more efficient. Press Alt+F11 to activate the VB Editor window. Then activate Module1and insert ActiveCell.Font.Bold = True, as demonstrated in the following sample code:

ActiveCell.Font.Bold = True

The edited macro appears as follows:

Sub MyName() '' MyName Macro '' Keyboard Shortcut: Ctrl+Shift+N ActiveCell.Font.Bold = True ActiveCell.FormulaR1C1 = "Michael Alexander" End Sub

Test this new macro, and you'll see that it performs as it should.

Now that you've read about the basics of the macro recorder interface, it's time to go deeper and begin recording a more complex macro. The first thing you need to understand before you begin is that Excel has two modes for recording: absolute reference and relative reference.

Excel's default recording mode is in absolute reference. As you may know, the term absolute reference is often used in the context of cell references found in formulas. When a cell reference in a formula is an absolute reference, it does not automatically adjust when the formula is pasted to a new location.

The best way to understand how this concept applies to macros is to try it. Open the Chapter 2 Sample.xlsmfile and record a macro that counts the rows in the Branchlist worksheet. (See Figure 2.2.)

FIGURE 2.2 Your pretotaled worksheet containing two tables

The sample dataset used in this chapter can be found on this book's companion website. See this book's introduction for more on the companion website.

Follow these steps to record the macro:

1 Before recording, make sure that cell A1 is selected.

2 Select Record Macro from the Developer tab.

3 Name the macro AddTotal.

4 Choose This Workbook in the Store Macro In drop-down.

5 Click OK to start recording.At this point, Excel is recording your actions. While Excel is recording, perform the following steps:

6 Select cell A16, and type Total in the cell.

7 Select the first empty cell in Column D (D16), type = COUNTA(D2:D15), and then press Enter. This gives a count of branch numbers at the bottom of column D. The COUNTA function is used to catch any branch numbers stored as text.

8 Click Stop Recording on the Developer tab to stop recording the macro.

The formatted worksheet should look like something like the one in Figure 2.3.

FIGURE 2.3 Your post-totaled worksheet

To see your macro in action, delete the total row that you just added and play back your macro by following these steps:

1 Select Macros from the Developer tab.

2 Find and select the AddTotal macro that you just recorded.

3 Click the Run button.

If all goes well, the macro plays back your actions perfectly and gives your table a total. Here's the thing: no matter how hard you try, you can't make the AddTotalmacro work on the second table. Why? Because you recorded it as an absolute macro.

To understand what this means, examine the underlying code. To examine the code, select Macros from the Developer tab to get the Macro dialog box illustrated in Figure 2.4. The Macro dialog box will, by default, list the macros available in all open Excel workbooks (including any Add-ins that you may have installed). You can limit the list to only those macros contained in the active workbook by changing the Macros In setting to This Workbook.

FIGURE 2.4 The Excel Macro dialog box

Select the AddTotalmacro and click the Edit button. This opens the Visual Basic Editor to show you the code that was written when you recorded your macro.

Sub AddTotal() Range("A16").Select ActiveCell.FormulaR1C1 = "Total" Range("D16").Select ActiveCell.FormulaR1C1 = "=COUNTA(R[-14]C:R[-1]C)" End Sub

Pay particular attention to line 2 and line 4 of the macro. When you asked Excel to select cell range A16 and then D16, those cells are exactly what it selected. Because the macro was recorded in absolute reference mode, Excel interpreted your range selection as absolute. In other words, if you select cell A16, that cell is what Excel gives you. In the next section, you will examine what the same macro looks like when recorded in relative reference mode.