Recording Macros

Say you want a macro that types six month names as three-letter abbreviations, Jan to Jun, across the top of your worksheet, starting in cell B1. I know this is rather a silly macro because you could do this easily with an AutoFill operation, but this example will serve to show you some important general concepts:

□ First, think about how you are going to carry out this operation. In this case, it is easy—you will just type the data across the worksheet. Remember, a more complex macro might need more rehearsals before you are ready to record it.

□ Next, think about when you want to start recording. In this case, you should include the selection of cell B1 in the recording, because you want to always have Jan in B1. If you don't select B1 at the start, you will record typing Jan into the active cell, which could be anywhere when you play back the macro.

□ Next, think about when you want to stop recording. You might first want to include some formatting such as making the cells bold and italic, so you should include that in the recording. Where do you want the active cell to be after the macro runs? Do you want it to be in the same cell as Jun, or would you rather have the active cell in column A or column B, ready for your next input? Assume that you want the active cell to be A2, at the completion of the macro, so you will select A2 before turning off the recorder.

□ Now you can set up your screen, ready to record.

In this case, start with an empty worksheet with cell A1 selected. If you can't see the Developer tab above the Ribbon, you will need to click the round Microsoft Office button that you can see in the top-left corner of the Excel screen shown in Figure 1-1. Click Excel Options at the bottom of the dialog box and select Personalize. Select the checkbox for Show Developer tab in the Ribbon and click OK. Now you can select the Developer section of the Ribbon and click Record Macro to display the Record Macro dialog box, shown in Figure 1-1.

Figure 1-1

In the Macro name: box, replace the default entry, such as Macro1, with the name you want for your macro. The name should start with a letter and contain only letters, numbers, and the underscore character, with a maximum length of 255 characters. The macro name must not contain special characters such as exclamation points (!) or question marks (?), nor should it contain blank spaces. It is also best to use a short but descriptive name that you will recognize later. You can use the underscore character to separate words, but it is easy to just use capitalization to distinguish words.

Call the macro MonthNamesl, because you will create another version later.

In the Shortcut key: box, you can type in a single letter. This key can be pressed later, while holding down the Ctrl key, to run the macro. Use a lowercase m. Alternatively, you can use an uppercase M. In this case, when you later want to run the macro, you need to use the keystroke combination Ctrl+Shift+M. It is not mandatory to provide a shortcut key; you can run a macro in a number of other ways, as you will see.

In the Description: box, you can add text that will be added as comments to the macro. These lines will appear at the top of your macro code. They have no significance to VBA, but provide you and others with information about the macro.

All Excel macros are stored in workbooks. You are given a choice regarding where the recorded macro will be stored. The Store macro in: combo box lists three possibilities. If you choose New Workbook, the recorder will open a new empty workbook for the macro. Personal Macro Workbook refers to a special hidden workbook, which is discussed in a moment. Choose This Workbook to store the macro in the currently active workbook.

When you have filled in the Record Macro dialog box, click the OK button. You will see a new Stop Recording button appear on the left side of the status bar at the bottom of the screen, as shown in Figure 1-2. You will also notice that the Start Recording button in the Ribbon has been replaced by a new Stop Recording button.

i On



T -

7 Book! - Microsoft Excel



risert Page Layout Formulas Data Review View


m y

Visual Basic

— J Stop Recording | 1 ®îpQPET^'es ffï* ¡?¡ Use P.efattve References l:Lacmi ^ Vfe^Code Macros Insert Design ii Macro Security . J Mou

i E/pânsicin Packs SóbjcE ,f Dncumenl Pine!



■ ML

[[ j MûdEfy




A _J B

C 1 D 1 E.




1 J


1 !


Feb Mar Apr



L fl


3 !

1 1

5 1


6 1

? 1

S 1

9 ¡

10 !

11 1


13 1

14 1

_ 1

« i ► Sheet 1 Sheet? BhaeQ J




Ja a

Figure 1-2

You should now click cell B1, type in Jan, and fill in the rest of the cells as shown in Figure 1-2. Then select B1:G1 and click the Bold and Italic buttons on the Home tab of the Ribbon. Click the A2 cell and then stop the recorder. You can stop the recorder by clicking the Stop Recording button on the Ribbon or by clicking the Stop Recording button on the status bar.

It is important to remember to stop the recorder. If you leave the recorder on and try to run the recorded macro, you can go into a loop where the macro runs itself over and over again. If this does happen to you, or any other error occurs while testing your macros, hold down the Ctrl key and press the Break key to interrupt the macro. You can then end the macro or go into debug mode to trace errors. You can also interrupt a macro with the Esc key, but it is not as effective as Ctrl+Break for a macro that is pausing for input.

You could now save the workbook, but before you do so, you should determine the file type you need and consider the security issues covered in the next section.

You can't save the workbook as the default Excel Workbook (*.xlsx) type. This file format does not allow macros to be included. You can save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) type, which is in XLM format, or you can save it as an Excel Binary Workbook (*.xlsb) type, which is in a binary format. Neither of these file types is compatible with previous versions of Excel. Another alternative is to save the workbook as an Excel 97-2003 Workbook (*.xls) type, which produces a workbook compatible with Excel versions from Excel 97 through Excel 2003.

0 0

Post a comment