Creating a basic macro

An Excel macro is, at its simplest, a set of several actions that can be combined into one command.

Macros can be created by ‘recording’ the required actions which Excel will ‘translate’ into Visual Basic for Applications (VBA) code, or you can write the code directly. Once created the macros can be attached to toolbar buttons, menus or form buttons, or objects. More advanced uses of macros enable them to be ‘triggered’ by specific events – such as opening a workbook. You can also use VBA to create your own user-defined functions which work in a similar way to Excel’s built in functions.

In this post we’re going to show you how to create a basic macro.

Before you start

The Excel default security setting is to disable all Macros, as macros can be used maliciously and their code can be triggered automatically when you open a workbook. It’s important be sure you trust the source of your workbook before running the macro.

To check and change your macro security settings go to File> Options> Trust Centre and click the Trust Centre Settings… button. This should take you to the Macro Settings section of the Trust Centre screen:

Select an appropriate option such as ‘Disable all macros with notification’. This will not allow macros to run automatically but will ask you whether you wish to allow the macros in the workbook to run.

Recording and viewing macros

You can include a button on the Status Bar to show whether or not a macro is in the process of being recorded and, if not, to start recording a macro. Alternatively you can access the basic macro options via the Macros group of the View ribbon tab:

Recording

When you start recording a macro, a square stop button appears on the Status Bar. Click on this to stop recording your macro and to save it. You can also stop a macro using the Stop Recording command in the Macros group of the View ribbon tab.

Assuming we are ready to record our macro; this is our workbook, a table of data that has been created or copied with blank rows between each row. We want to remove the blank rows.

Assuming that we start with a cell on the first invoice row selected, our actions will be:

• Move down one row
• Use Home ribbon tab, Cells group, Delete, Delete Sheet Rows

First we ensure cell A2 is selected then we go to the View ribbon, Macros group and select Record Macro:

As well as giving the macro a Macro name and Description, and possibly assigning a Shortcut key, the screen allows us to choose where to save the macro.

Macros are only available to Excel when the workbook containing the macro is open. As well as the current workbook (This Workbook) or a New Workbook, you can choose to save a macro in the Personal Macro Workbook. This is a file called Personal.xlsb saved as a hidden file in the XLSTART folder which means that it will be opened automatically whenever Excel itself is opened.

We will just save this macro in the current workbook and we will not assign a shortcut key. If you do try and assign a shortcut key, you need to do so with care. All the obvious shortcut key combinations are likely already to be assigned to some built-in action and overriding one of these existing assignments can cause considerable confusion.

Once we click the OK button, whatever we do will be recorded so we need to take care just to carry out the two actions outlined above and then use the Status Bar button or View> Macros options to stop our macro.

Once you have recorded a macro you can run it from the View> Macros>View Macros option:

Saving a workbook containing a macro

As another security measure, Excel will not allow ‘normal’ Excel workbooks to contain VBA projects – which all macros are. Accordingly, when you try and save a workbook to which you have added a macro for the first time, you will see this message:

To preserve your macro, you will need to click No which takes you to the Save As screen. Here you need to click on the Save as type: drop down and choose Excel Macro-Enabled Workbook. You will see that the file extension is changed from xlsx to xlsm to indicate that this workbook contains a macro which might, potentially, be dangerous.

Alan Gurney is AAT Comment’s Excel tips writer.