The Macro Recorder can be used to record simple macros.
Recording macros is very useful even if you are a very experienced programmer, although there are some limitations that you need to be aware of.
If you are relatively new to macros it is worth rehearsing your steps first before recording them.
This will help you to understand the code that is generated and to help make your code as efficient as possible.
When you record a macro Excel is basically recording the keystrokes, menu commands exactly as they are pressed.
After you have recorded a few single steps you can run the macro to perform the required task again and again.
Once you start the Macro Recorder all your keystrokes will be recorded and converted into VBA code.

Record your Macro

Select (Tools > Macro > Record New Macro) to display the Record Macro dialog box.
Alternatively you could press the "Record Macro" button on the Visual Basic toolbar.

The name of your macro will automatically default to "Macro1", "Macro2", etc depending on the number of macros that have been recorded in that specific workbook.
Macro names must begin with a letter and cannot resemble cell addresses (i.e. A1, B5, BT100 etc).
Macro names cannot include spaces although the underscore character can be used (e.g. "macroname_2"). You can also include numbers in your macro names.
It is sometimes easier to accept the default name and then change the name of the procedure later.
The shortcut key and description are optional so do not worry about these at this point, however it is very important to remember where your macro will be stored.

Stop Recording

You can stop recording at any time by selecting (Tools > Macro > Stop Recording).
Alternatively you can use the first button on the Stop Recording toolbar. The toolbar will then disappear.
There is also a Stop Recording button on the Visual Basic toolbar that can be used as well.
It is very important to stop recording once you have finished otherwise Excel will carry on recording your keystrokes indefinitely.
Once you have finished recording you can cut and paste the code (via the clipboard) to different code modules.


The macro recorder cannot generate code for any of the following:
Performing any loops or repeating statements (If, Do, While, Select etc)
Create or assign variables
Display any dialog boxes
Contain any error handling
You can only create subroutines (it is not possible to create functions)
Code is not an efficient as writing it from scratch
Code cannot be placed inside existing macros or subroutines.
Local settings or user options will be taken into account
If you use the Macro recorder to create procedures the default names are Macro1, Macro2 etc.

© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext