Macros are essential when we perform repetitive tasks. Ｗe can create a macro in Excel that simply records a series of actions and then stores them in the same file. The Macro Recorder will automatically create the VBA code, instead of manually typing a few lines of VBA code to create a subroutine or short program. We can run these recorded steps repeatedly when we need to quickly complete repetitive tasks.
Recording a macro
In this example we use absolute references to record your first macro.
- Open a blank workbook in Excel. Excel selects cell A1 by default.
- On the Developer tab, click the Record Macro button in the Code group to launch the “Record Macro” dialog box.
- In this example, the Macro name type "Helloworld", Shortcut key type lowercase j, Store macro in This Wordbook, and Description type "This is my first macro".
- Click OK to start recording the macro.
- In active cell A1, type "Hello World" and press Ctrl+Enter to accept the entry and stay in the same cell.
- Change the font format of active cell A1 to underlined, italicized, and bold.
- Click the Stop Recording button in the Developer tab.
You have just successfully recorded a macro.
The “Record Macro” dialog box settings
If this is your first time recording a macro, the default "Record Macro" dialog box settings are as follows.
Macro name
Be sure to type continuous characters (without a space). A macro name can include letters, numbers, and the underscore character. The name cannot start with a number. Excel assigns a default name to your macro, such as Macro1, but you should assign a name to the macro that better describes its actual function. For example: FormatReport, FormatTable.
Shortcut key
This is an optional field. If you type lowercase j in this field and press Ctrl+J later, the macro runs. But be careful, because Ctrl+A through Ctrl+Z (except for Ctrl+J) are assigned to other tasks in Excel. If you assign the macro to Ctrl+C, you will no longer be able to use Ctrl+C for copy. An alternative is to assign the macro to Ctrl+Shift+A via Ctrl+Shift+Z. To assign the macro to Ctrl+Shift+C, type Shift+c in the shortcut box.
Store macro in
This Workbook is the default option. Storing a macro in this workbook means that the macro is stored with the active Excel file. The next time you open that particular workbook, the macro will be ready to run.
The Personal Macro Workbook (PERSONAL.XLSB) is not a visible workbook, it is created if you choose to save the recording in the Personal Macro Workbook. This workbook is used to save macros in a workbook that is automatically opened when you start Excel so that you can use the macros. When Excel starts, the workbook is hidden. If you want to show it, select Unhide from the View tab.
My recommendation is that you store macros related to a particular workbook in This Workbook.
Description
This is an optional field, but it will come in handy if you have a large number of macros in your spreadsheet, or if you need to provide your users with a more detailed description of the macro's functionality.
Excel Macro Recorder Limitations
Excel Macro Recorder can "faithfully" record the operation in Excel, but also has its own limitations.
The code generated by the Macro Recorder may not be identical to the user's operation. For example, the password entered by the user when setting up a protected worksheet cannot be recorded in the code; the code cannot be generated by setting the properties of the worksheet controls.
Generally speaking, the code generated by Macro Recorder can achieve the relevant functions, but it is often not the optimal code because the recorded code often has a lot of redundant code. For example, actions such as the user selecting only one cell or scrolling the screen will be recorded as code, and by removing these redundant codes, the macro code will be able to run more efficiently.
Usually the execution efficiency of the code generated by Macro Recorder is not high for the following two reasons: first, the code uses many methods such as Activate and Select, which affects the execution efficiency of the code and needs to be optimized accordingly in practical applications; second, Macro Recorder cannot generate code to control the program flow, such as loop structure, judgment structure, etc.