How to make the most of Excel Macros

How to use and install macros

I get many questions from Australian CPA’s and sometimes the solution involves a macro. Not everyone knows how to install and run a macro. This post will take you through the basics.

Let’s say you want to apply the red brackets format for negatives. This is not a standard built-in format but it is a popular format.Red brackets format

The following recorded macro will apply the format with 2 decimals places to the selected range.

Sub MinusRedBrackets()
 
    Selection.NumberFormat = "#,##0.00;[Red](#,##0.00)"
 
End Sub

Now how do you use this?

The easiest way is to save it to your Personal Macro Workbook (PMW). This is a special system-generated file that is created by Excel when you record your very first macro. It’s a file that exists in the XLSTART folder and it opens every time you open Excel. Its default setting is hidden, so you can’t see it when it opens. It’s there in the background once you create it. The instructions below apply to Excel 2007 and 2010. The macro will work in Excel 2003 but the instructions are different and not included in this post.

The PMW is the best place to save all those macros that you want to use in all of your files. (If you’re a more advanced macro user then there is another, even better method – see the bottom of the post for what it is)

How to create the Personal Macro Workbook (PMW)

  1. To create the PMW all you need to do is record a macro. See if you have the Record macro button on the bottom left of your screen – see image below.Record Macro buttonIf it is there click it and go to step 2. If it’s not then right click the bar below the sheet tabs (it’s called the Status Bar) and tick the Macro Recording option.Macro recording Option
    Now click the Record Macro button.
  2. You will see the dialog below.
    Record Macro Dialog
    Click the drop down for Store macro in and choose Personal Macro Workbook and click OK.
  3. There’s no need to actually do anything just click the Stop recording button (it replaced the Record Macro button) at the bottom of the screen – see image below.Stop Recording button
    Done and dusted – congratulations – the Personal Macro Workbook has been created. Be aware that now the PMW has been created, whenever any changes are made to it you will be asked to save the changes when you close Excel.

 How to store a macro in the PMW

  1. Copy the macro at the start of the this blog post, or from where ever you have found another macro.
  2. Navigate to, or open Excel, and hold the Alt key and press the F11 function key. This will open the VBA window.
  3. On the left of the VBA screen there should be a VBA Project for Personal.XLSB see image below.VBA Window
    Open the Modules folder underneath it by double clicking it. Then double click on Module1. This opens the code window on the right.
  4. Click the inside the code window on the right and under the existing macro. Press Ctrl + c or right click and Paste. Click the Save icon on the toolbar of the VBA window.
  5. Done – the macro is now created and ready to use. You can Press the red x (top right) of the VBA window to close it.

How to use the macro

  1. Back in Excel you can press Alt + F8 to see also a list all macros.
  2. Simply double click the macro name to run it. That’s a few too many keystrokes, so see below for an even quicker way.

How to link a macro to an icon on your Quick Access Toolbar (QAT)

Click here to read more about the QAT

  1. Right click the Ribbon and select Customize Quick Access Toolbar
  2. In the Choose commands from drop down select MacrosMacro Option
  3. Select the macro from the list and click the Add button.Select macro
  4. With the macro selected on the right of screen click the Modify button and select an icon to use and click OK twice.Choose icon
  5. Done – the icon will be added to the QAT and the macro is now ready to run with just one click.

Advanced Macro Users

If you already use the PMW but are after an alternative, try creating and installing an Excel add-in for all your common macros. If you want to share your shortcuts the add-in is ideal as it can be placed on a server and shared between users.

Click here to see how you can run a Macro off a Quick Access Toolbar icon.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *