A simple sequence of actions that needs to be repeated several times can be written as program code and saved as a macro. If a sequence of actions is recorded in a macro, then it can be performed again and again by simply running this macro. This is much more efficient than doing the same steps manually over and over again.
To record a macro, you need to turn on the recording mode. This can be done on the tab. View (View) section Macros (Macros) or in the menu Service (Tools) if you have Excel 2003. The pictures below show what these menus look like.
- Recording a macro in modern versions of Excel (2007 and newer):
- Macro recording in Excel 2003:
Next, a dialog box will open. Macro recording (Record Macro) as shown in the picture below:
Here you can optionally enter a name and description for the macro. It is recommended to give the macro such a name that, returning to it after a while, you can easily understand what this macro is for. One way or another, if you do not enter a name for the macro, Excel will automatically name it Macro1, Macro2 and so on.
Here you can also assign a keyboard shortcut to run the recorded macro. Running a macro this way will be much easier. However, be careful! If you accidentally assign one of Excel’s predefined keyboard shortcuts to a macro (for example, Ctrl + C), then the macro may be run accidentally in the future.
Once you’ve given the macro an appropriate name and (optionally) a keyboard shortcut, press OKto start macro recording. From now on, every action (data entry, cell selection, cell format change, sheet scrolling, and so on) will be recorded in a macro and saved as VBA code.
When macro recording mode is enabled, a button appears in the status bar (bottom left). Stop. In Excel 2003, this button is on a floating toolbar.
- Button Stop on the status bar in Excel 2007 and newer versions:
- Macro recording toolbar in Excel 2003:
Press Stopwhen you have done all the actions that should be recorded in the macro. The recorded macro code is now stored in the Visual Basic editor module.
Relative Links option
If you turn on the parameter before starting macro recording Relative links (Use Relative References), then all references in the recorded macro will be created as relative. If the option is disabled, then absolute links will be created when recording a macro (for more information about these two types of links, see the articles on cell references in Excel).
Parameter Relative links (Use Relative References) is located under Macros (Macros) tab View (View). In Excel 2003, this option is located on the floating toolbar.
- Parameter Relative links (Use Relative References) in modern versions of Excel:
- Parameter Relative links (Use Relative References) on the floating toolbar in Excel 2003:
Viewing VBA Code
The VBA code written in the macro is placed in a module that can be viewed in the Visual Basic editor. The editor can be launched by clicking Alt + F11 (simultaneous key press Alt и F11).
The code is in one of the modules that are located in the window Project to the left of the edit area. If you double click on Module1 in the window Project, the code of the recorded macro will appear on the right.
Run a recorded macro in Excel
When recording a macro, Excel always creates a procedure Sub (not Function). If a keyboard shortcut was attached to it when creating a macro, then it will be the easiest way to run the macro with it. There is another way to run a macro:
- Press Alt + F8 (simultaneously press the keys Alt и F8);
- In the list of macros that appears, select the one you want to run;
- Нажмите кнопку Вexecute (Run).
Restrictions
Excel’s macro recording tool is a very simple way to create VBA code, but it’s only suitable for creating the most basic macros. The fact is that this tool does not know how to use many VBA features, such as:
- Constants, variables and arrays;
- Expressions IF;
- Cycle;
- Calls to built-in functions or external procedures.
As already mentioned, the macro recorder can only create procedures Sub, since it cannot return a value. procedures Sub no arguments can be passed, although they can recognize the currently active cells, ranges, or sheets, as well as values stored in workbook cells. In addition, it must be said that the generated code is not always optimal for the considered sequence of actions.
Excel’s automatic VBA code generation works great for simple macros, but if you need to build a more complex macro, you’ll have to learn how to write the VBA code yourself. However, recording a macro in Excel is a great tool with which you can create the initial code, and later correct it or insert it into more complex macros.