Excel 2019 Power Programming with VBA. Michael Alexander
Читать онлайн книгу.
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 Module1 in the current workbook. When you double-click Module1, the code in the module appears in the Code window.
NOTE
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 Sub procedure 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.
Testing your macro
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.
NOTE
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.
Editing your 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 Module1 and 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.
Comparing absolute and relative macro recording
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.
Recording macros with absolute references
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.xlsm file 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
NOTE
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 AddTotal macro 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.