Introduction to the Macro
Macros can be used to automate many of the mundane and repetitive tasks performed in excel through the record macro function.
Macros can be made readily available by assigning shortcut keys or allocating icons in the toolbar.
There are two methods that can be used to create a Macro: –
Recording via the record Macro function
Write one using visual basic programming language
How to record a Macro
TIP: – Before recording a Macro take your time to practise the steps required as every mistake you make will also be recorded and adds to the macro size.
Macro’s can be saved in the following places: –
- Personal macro workbook
- This makes the macro accessible from all workbooks within Excel.
- New Workbook
- The macro is stored in the new workbook only
- This workbook
- Stores the Macro in the current workbook only
To record the Macro: –
Select the first cell that you want to run a process on.
On the View ribbon, navigate to the Macros option and using the drop down arrow select the record Macro option.
Before recording a Macro it must be named. Macro names must start with a letter and must not contain spaces, underscores are allowed in the place of a space. Macro names also can not be the same as a cell and an error message will alert you to any errors in this regard.
The above example has been named Test_Macro and I have assigned Ctrl+k as the shortcut for this Macro.
A description can also be added to help identify the operations the macro undertakes at a later date.
Click to start recording.
Once the actions have been completed click stop recording.
Example of simple Macro
A very simple Macro may be to insert the date and your name to the top of a worksheet.
Selecting cell A1 and navigate to the record Macro button.
Add your name to cell A1, click return and then enter =today() in cell A2.
Then click stop recording
How to run a macro
In order to run the macro we have created we can either press Ctrl+M (the shortcut that we created) or click on the View Macros button.
When “View Macros” is selected the following pop up box becomes visible: –
Select the macro you wish to run and then click Run.
Add a Macro to the toolbar
To add a macro to the quick access toolbar, select the arrow next to the quick access toolbar and click more commands.
Open the more commands option and select the Macros from the choose command from
The available Macros will then be listed: –
The Page_setup macro has now moved to the right hand side box to indicate that it has been assigned to the Quick access toolbar.
Once OK is clicked we can seen that the macro has been added to the quick access toolbar.
Finding the VBA code for a Macro
The best place to start here is to view the VBA code for the macro we have just created.
In order to navigate to the VBA code of the Macro created above go to the View Macros option under the Macro option in the View Ribbon.
Hitting the edit button opens up the VBA editor displays the code for the Macro.
An explanation of VBA code and how to write it can be found in the writing VBA code page.