How much time do you spend on repetitive tasks in Microsoft Excel?Do you ever suspect that there must be a better way? If this sounds familiar, then it’s time to learn to use Macros – and don’t panic if the name sounds complex or scary.
An Excel Macro is, at its simplest, a set of several actions that can be combined into one command. Macros can automate almost anything you can do in Excel and even enable you to do things you never imagined were possible.
Macros can be created by ‘recording’ the required actions which Excel will ‘translate’ into Visual Basic for Applications (VBA) code. Although you can write the code directly, the great thing about Macros is that you don’t have to be a developer or possess programming knowledge to use them.
Once created, Macros can be attached to toolbar buttons, menus, form buttons or objects, and ultimately increase the quality of your spreadsheets and your own productivity.
Before you start: Security settings
The Excel 2013 default security setting is to disable all Macros. This is because, as well as being useful, Macros can also be used maliciously and such code can be triggered automatically when you open a workbook. Accordingly, it is essential to be sure that you trust the source of any workbook where you allow Macros to run.
If you want to check and change your Macro security settings, go to FILE > Options > Trust Center and click the Trust Center Settings button. This will take you to the Macro Settings section of the Trust Center screen:
Choose an appropriate option such as ‘Disable all macros with notification’. This will not allow Macros to run automatically, however 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 one. Alternatively, you can access the basic Macro options via the Macros group of the VIEW ribbon tab:
Saving a Workbook Containing a Macro
Excel also doesn’t 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 notification:
To preserve your Macro, click No which takes you to the Save As screen. Now click on the Save as type: drop down and select 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.
AAT members have free access to this and many other Excel tips through their MyAAT account. Macros are mentioned multiple times in the Filtered Microsoft Excel course as they are extremely useful. If you are not a Macros expert yet, familiarise yourself by looking at what they can do for you (Macros > View Macros > Tell me more).
Paolo Lenotti is the Head of Marketing & PR at Filtered.com.