Category Archives: Macros
In this blog, I am going to introduce the concept of macros in Excel, and show you how to record and run basic macros – if you already write your own code, then read no further.
Macros are a great feature in Excel (and any other Microsoft application for that matter). They are simply a series of steps which have been automated. So instead of you clicking several buttons, or highlighting data and formatting it in some way, a macro can complete all of those actions with the single click of a button – so they are potentially huge time savers.
The recording of macros is very simple, but it is limited, and ultimately, if you want to automate your work in Excel you need to write code from scratch.
The simplest way to create a macro is to record it. You can do this via the VIEW tab and click on MACROS, and select RECORD MACRO.
Although you can access everything through this button, I would recommend turning on the DEVELOPER tab. This give you access to more things relating to macros and VBA as well as form controls. If you’re using 2007, click on the OFFICE button and click on EXCEL OPTIONS. From the POPULAR tab tick the SHOW DEVELOPER TAB IN THE RIBBON option. For those on 2010 or 2013, right click on the RIBBON, select CUSTOMIZE THE RIBBON and tick the DEVELOPER box in the right hand window.
Time to start recording…
First of all plan what you are about to record. I say this because the recorder records everything you do…including mistakes, re-edits etc. It just means that every time you run the macro it will repeat all the mistakes and the consequent corrections (albeit at high speed), so plan and if necessary do a couple of practice runs so you know which command/icon to click on, what to select etc. The recording is not done in “real time”, so if you pause to answer the phone, or have a cup of tea in the middle of your recording it won’t record several minutes of inactivity. It only records actions on screen, and an important thing to bear in mind here, it records ONLY the actions you perform in Excel. Opening Windows explorer, or Word or anything else for that matter will not be included as part of the recorded macro. Remember, I said it was limited.
Assuming I have planned and rehearsed where necessary, it’s time to do the actual recording.
Click on the VIEW tab, MACROS, RECORD MACRO, or go to the DEVELOPER tab and click on RECORD MACRO.
You should then see the following window;
Give your MACRO a NAME. Using the default name is fine, but once you have a collection of macros all called Macro1, Macro2 etc. it will be very hard to tell one from another unless you look at the code to work out what each one is doing. So give it a helpful name. Note that no spaces are allowed in macro names. If you do want a space of some sort, use an underscore (_). Personally, I use something that is called Camel Hump Text i.e. capiltalise the first letter of each word that makes up the name of your macro. YouWillBeSurprisedAtHowEasyReadingTheNameIsDespiteBeingContinuousText…I rest my case.
The choice as always is down to your own personal preference.
You can assign a shortcut key to your macro. All I will say here is “good luck” finding a free combination of keys. Easier to just add a button to your Quick Access Toolbar or create new tabs on your Ribbon (2010+) and add buttons there.
Now for the important bit: STORE MACRO IN. You will be presented with 3 options;
- Personal Macro Workbook
- This Workbook
- New Workbook
If the macro is going to work exclusively in, or from within the current workbook, then select THIS WORKBOOK. So if the macro is unique to the current workbook and won’t be used elsewhere or the macro (for example) imports data from other sources, but ultimately does all the work in the current workbook then THIS WORKBOOK is the best choice.
NEW WORKBOOK is a bit of a pointless choice in my opinion as the new workbook effectively becomes the new THIS WORKBOOK. End result is same as above.
If, however, you want your macro to work in any workbook at any time, or it involves opening a workbook for example, then the best place is in your PERSONAL MACRO WORKBOOK. This is a blank workbook that sits in the background to Excel and is used purely to store your macros in “folders” called MODULES. By default, PERSONAL does not exist, you need to record something in it to get it kick started. Once created, you can either record or write code directly inside the modules. PERSONAL, is as the name suggests, personal to your Excel. It resides on your computer alone.
If you choose the “wrong” place by mistake you can easily copy/cut and paste code from one workbook to another (assuming PERSONAL has been created).
The final box is for comments. This serves no purpose other than to add a note to say what the macro can do or just some information that may be of use to you or any other user of the macro.
Click on OK to start recording.
Do whatever it is you need to do, and remember to STOP RECORDING once you have done . Click on STOP RECORDING in the DEVELOPER tab or the small square in the bottom left hand corner of the screen.
To run the macro you have just recorded, go to the DEVELOPER tab and click on MACROS, click on the macro name and then click on RUN.
As this is quite a long winded way of running a macro, add a button to your Quick Access Toolbar (QAT) or add a new tab to the Ribbon.
Here is a quick video showing a macro being recorded and a shortcut button added to the QAT.
If you want to view the code that has been recorded then go to the DEVELOPER tab and click on VISUAL BASIC or use ALT + F11. This will open the Visual Basic editor. Take a look at the code. It’s not as difficult to decipher as you may think –
- Font.Bold = True
Without knowing any code, these sort of bits of code are self-explanatory. You may not know the details or the why but you can get a pretty good idea of which bit does what in your recorded code. Like any language, you need to practice it to become “fluent” and VBA is no different. The more you see it, write it etc. the better you will get…simple as that.
So what are the downsides to recording macros?
- Redundant code – the recorder has a tendency to record a lot more than is necessary. Try recording a macro where you do a bit of PAGE SETUP for printing. The amount of stuff it records is ridiculous.
- Absolute references – unless you click on USE RELATIVE REFERENCES (and even that has limited use in the real world), the recorder does everything in absolute terms. So if you record putting in AUTOSUM in line 100, the next time you run the macro and the worksheet has 500 lines in it, it still places the AUTOSUM in line 100. So it’s not dynamic or clever enough to work out there is additional data.
So yes it does have limitations but recording macros does have its uses. If you have a report that is in a fixed format but needs to be adjusted each time it is run, then a recoded macro will do a great job as the report will be in the same format each time. Recording is also very useful if you are unsure what code is needed. Just record a couple of clicks, stop recording and view the code. This limits the amount of possible redundant code that can be generated and helps you focus on just two or three lines of code that will help you.
Your long term plan with macros though should be to learn how to write code from scratch. Like that you get exactly what you want and with practice you may well find that writing a 3-5 line macro and running it is quicker than doing some jobs manually.