I compared recording a macro to using a tape recorder. However, it occurred to me that tape recorders are rapidly going the way of the dinosaurs. So I modernized this section, and it now com-pares macro recording to making a digital video. This analogy, like the previ-ous one, goes only so far.
Recording Basics
You take the following basic steps when recording a macro. I describe these steps in more detail later in this article.
1. Determine what you want the macro to do.
2. Get things set up properly.
This step determines how well your macro works.
3. Determine whether you want cell references in your macro to be rela-tive or absolute.
4. Click the Record Macro button in the left side of the status bar (or choose Developer➪Code➪Record Macro).
Excel displays its Record Macro dialog box.
5. Enter a name, shortcut key, macro location, and description. Each of these items — with the exception of the name — is optional.
6. Click OK in the Record Macro dialog box.
Excel automatically inserts a VBA module. From this point, Excel con-verts your actions into VBA code. It also displays a Stop Recording button on your status bar (a blue square).
7. Perform the actions you want recorded by using the mouse or the keyboard.
8. After you’re finished, click the Stop Recording button on the status bar (or choose Developer➪Code➪Stop Recording).
Excel stops recording your actions.
9. Test the macro to make sure it works correctly.
10. As an option, you might want to clean up the code by removing extra-neous statements.
The macro recorder is best suited for simple, straightforward macros. For example, you might want a macro that applies formatting to a selected range of cells or that sets up row and column headings for a new worksheet.
The macro recorder is for Sub procedures only. You can’t use the macro recorder to create Function procedures.
You may also find the macro recorder helpful for developing more complex macros. Often, I record some actions and then copy the recorded code into another, more complex macro. In most cases, you need to edit the recorded code and add some new VBA statements.
The macro recorder cannot generate code for any of the following tasks:
✓ Performing any type of repetitive looping
✓ Performing any type of conditional actions (using an If-Then statement) ✓ Assigning values to variables
✓ Specifying data types
✓ Displaying pop-up messages
✓ Displaying custom dialog boxes
The macro recorder’s limited capability certainly doesn’t diminish its impor-tance. Recording your actions is perhaps the best way to master VBA. When in doubt, try recording. Although the result may not be exactly what you want, viewing the recorded code may steer you in the right direction.
Preparing to Record
Before you take the big step and turn on the macro recorder, spend a minute or two thinking about what you’re going to do. You record a macro so that Excel can automatically repeat the actions you record.
Ultimately, the success of a recorded macro depends on five factors:
✓ How the workbook is set up while you record the macro ✓ What is selected when you start recording
✓ Whether you use absolute or relative recording mode ✓ The accuracy of your recorded actions
✓ The context in which you play back the recorded macro
The importance of these factors becomes crystal clear when I walk you through an example.
Relative or Absolute?
When recording your actions, Excel normally records absolute references to cells. (This is the default recording mode.) Very often, this is the wrong recording mode. If you use relative recording, Excel records relative refer-ences to cells. The distinction is explained in this section.
Recording in absolute mode
Follow these steps to record a simple macro in absolute mode. This macro simply enters three month names into a worksheet:
1. Choose Developer➪Code➪Record Macro.
2. Type Absolute as the name for this macro.
3. Click OK to begin recording.
4. Activate cell B1 and type Jan in that cell.
5. Move to cell C1 and type Feb.
6. Move to cell D1 and type Mar.
7. Click cell B1 to activate it again.
8. Stop the macro recorder.
9. Press Alt+F11 to activate the VBE.
10. Examine the Module1 module.
Excel generates the following code:
Sub Absolute()
‘
‘ Absolute Macro
‘
Range(“B1”).Select ActiveCell.FormulaR1C1 = “Jan” Range(“C1”).Select ActiveCell.FormulaR1C1 = “Feb” Range(“D1”).Select ActiveCell.FormulaR1C1 = “Mar” Range(“B1”).Select
End Sub
When executed, this macro selects cell B1 and inserts the three month names in the range B1:D1. Then the macro reactivates cell B1.
These same actions occur regardless of which cell is active when you execute the macro. A macro recorded by using absolute references always produces the same results when it is executed. In this case, the macro always enters the names of the first three months into the range B1:D1.
Recording in relative mode
In some cases, you want your recorded macro to work with cell locations in a relative manner. You may want the macro to start entering the month names in the active cell. In such a case, you need to use relative recording.
You can change the manner in which Excel records your actions by clicking the Use Relative References button in the Code group in the Developer tab. This button is a toggle button. When the button appears in a different color, the recording mode is relative. When the button appears normally, you are recording in absolute mode.
You can change the recording method at any time, even in the middle of recording.
To see how relative mode recording works, erase the cells in B1:D1 and then perform the following steps:
1. Activate cell B1.
2. Choose Developer➪Code➪Record Macro.
3. Name this macro Relative.
4. Click OK to begin recording.
5. Click the Use Relative References button to change the recording mode to relative.
When you click this button, it changes to a different color than the rest of the ribbon.
6. Activate cell B1 and type Jan in that cell.
7. Move to cell C1 and type Feb.
8. Move to cell D1 and type Mar.
9. Select cell B1.
10. Stop the macro recorder.
Notice that this procedure differs slightly from the previous example. In this example, you activate the beginning cell before you start recording. This is an important step when you record macros that use the active cell as a base.
This macro always starts entering text in the active cell. Try it. Move the cell pointer to any cell and then execute the Relative macro. The month names are always entered beginning at the active cell.
With the recording mode set to relative, the code that Excel generates is quite different from the code generated in absolute mode:
Sub Relative()
‘
‘ Relative Macro
‘
ActiveCell.FormulaR1C1 = “Jan” ActiveCell.Offset(0, 1).Range(“A1”).Select ActiveCell.FormulaR1C1 = “Feb” ActiveCell.Offset(0, 1).Range(“A1”).Select ActiveCell.FormulaR1C1 = “Mar” ActiveCell.Offset(0, -2).Range(“A1”).Select
End Sub
To test this macro, activate any cell except B1. The month names are entered in three cells, beginning with the cell that you activated.
Notice that the code generated by the macro recorder refers to cell A1. This may seem strange because you never used cell A1 during the recording of the macro. This is simply a byproduct of the way the macro recorder works.
What Gets Recorded?
When you turn on the macro recorder, Excel converts your mouse and keyboard actions into valid VBA code. I could probably write several pages describing how Excel does this, but the best way to understand the process is by watching the macro recorder in action. (Figure shows how my screen looked while I had the macro recorder turned on.)
Follow these steps:
1. Start with a blank workbook.
2. Make sure that the Excel window is not maximized.
3. Press Alt+F11 to activate the VBE (and make sure that this program window is not maximized).
4. Resize and arrange the Excel window and the VBE window so that both are visible.
For best results, position the Excel window on top of the VBE window, and minimize any other applications that are running.
5. Activate Excel and choose Developer➪Code➪Record Macro.
6. Click OK to start the macro recorder.
Excel inserts a new module (named Module1) and starts recording in that module.
7. Activate the VBE program window.
8. In the Project Explorer window, double-click Module1 to display that module in the Code window.
Jump back to Excel and play around for a while. Choose various Excel com-mands and watch the code being generated in the VBE window. Select cells, enter data, format cells, use the Ribbon commands, create a chart, change column widths, manipulate graphics objects, and so on — go crazy! I guar-antee that you’ll be enlightened as you watch Excel spit out the VBA code before your very eyes.
No comments:
Post a Comment