Macros in Excel present automated steps that we can repeat whenever we want. They are often used for performing repetitive tasks, which significantly saves time, and also when learning VBA.
Here are some tips on how to start working with Excel macros. It might look hard at the beginning, but with more practice, you will be amazed with all the possibilities.
1) Enable the Developer tab
The Developer tab, from which all the developer and VBA functionalities are offered, is hidden by default. Here is how you can make it visible.
First go to File – Options.
Choose the Customize Ribbon tab and check the Developer option in the main ribbon.
Now, the new Developer tab will appear in the main menu.
2) Record a macro
In the new Developer tab there is a button Record a macro that starts the recording. Turn on the button and start recording.
3) Use relative references
If you regularly use Excel, you are probably familiar with relative references that are often used in Excel. Macros can be better implemented in the future using relative references. So, in this case we suggest starting from the field A1 and turning the relative reference field on while recording.
Next time you want to repeat the macro steps, you don’t have to worry if you entered a value in the cell A1 or cell B2; the macro will perform all other steps relative to the first field.
4) Name and description
It is of utmost importance to enter a good and detailed description of what a macro does, along with an appropriate name, because once the number of macros pile up, you won’t be sure which macro is for what. Prevent this by entering macro descriptions in great detail.
Also, if you plan to reuse it for other documents, make sure that the macro is saved in your personal space.
5) Edit a macro
Sometimes a macro needs to be edited after recording. This is easily done by going to the Macros section, choosing the relevant section and clicking on Edit. The Step into field is useful as well, because it allows you to see all the steps in the VBA code and analyze them if you need.
If you receive an error that a workbook is hidden, go to View – Unhide and choose the Personal macro workbook.
Now you will be able to edit or delete existing macros.
6) Run a macro
To run a macro, all you need to do is to choose it from the list and click Run. Have in mind that whenever you record a macro, before closing the Excel completely, you need to save all changes made to the Personal workbook. This way all macros will be saved and available for later use.
There are other ways to run a macro, like using buttons or other features, but we will talk about it in our future articles.