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.

introduction to macross

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.

Excel macro

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.

introduction to macros

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.

macro excel

Add your name to cell A1, click return and then enter =today() in cell A2.

Date macro

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.

time macro

When “View Macros” is selected the following pop up box becomes visible: –

run macro box

Select the macro you wish to run and then click Run.

Add a Macro to the toolbar

add macro to toolbar

To add a macro to the quick access toolbar, select the arrow next to the quick access toolbar and click more commands.

macro 9

Open the more commands option and select the Macros from the choose command from

add to quick access toolbar

 

The available Macros will then be listed: –

quick access options

added to custom toolbar

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.

excel 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.

Accessing VBA

Hitting the edit button opens up the VBA editor displays the code for the Macro.

VBA code

An explanation of VBA code and how to write it can be found in the writing VBA code page.