Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
581 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
If you've heard of Visual Basic for Applications macros (and feel perhaps you should be using them to be more productive), then this blog is for you! It explains how to record macros in Excel, and how to play them back to automate common tasks.
First, however, some ground rules:
The blog series tells you what VBA is, how you might use it and how to record and run macros. If you find the blog inspiring, the next stage is to learn how to write macros in VBA from scratch.
A good place to start, then, is to find out what VBA actually is and does.
You've probably heard of VBA (or Visual Basic for Applications, to give the programming language its full name), but what is it? And how can it help you?
I'm aware that there are ways to accomplish the task set out below without using macros, but that's not the point of this blog!
To see how VBA could help you, suppose that you are a data entry clerk for a major talent show on national TV. Every time there's a vote, the scores are entered into a spreadsheet:
Whenever a vote is taken, the judges type their scores onto this spreadsheet
From here, your task is to copy the results faithfully into a results sheet:
You have to type in a row for the new contestant's scores
There are two main problems with this:
You start dreaming - wouldn't it be fantastic if you could just click on a button to copy the results across:
The magical button (if only you knew how to create it) ...
This blog shows you how to automate tasks like the one above using Visual Basic for Applications! But first, what is VBA?
You don't really need to know this, but it's good background ...
All computer programs are written using a programming language (examples are Fortran, C#, C, Pascal and Visual Basic). The Visual Basic language - VB to its friends - is particularly favoured by Microsoft, and is used by millions of programmers around the world. Here's a bit of VB:
'if no menu (eg search page), hide
Select value
Case 0
Title = "Search results"
Me.lblBreadcrumbs.Visible = False
Exit Property
Case 1
Title = "Problem with page"
Me.lblBreadcrumbs.Visible = False
Exit Property
Case Else
End Select
You can see that the language looks half like English and half like meaningless computer gibberish.
Microsoft have included a cut-down version of this language within each Office application (Word, Access, Excel, PowerPoint, SharePoint, Outlook and Visio all include variants of VBA). This language is called Visual Basic for Applications, or VBA, because it's Visual Basic running within an application. Here's an example of VBA from Excel, taken from a hangman game:
Function DrawScaffold()
'each part of the gallows
Dim Limb As Shape
' checks all the shapes on the worksheet and makes one of them visible
' has a shape been made visible in this function call?
Dim MadeVisible As Boolean
' assume no shapes have been made visible in this function call
MadeVisible = False
' loop through all shapes
For Each Limb In ActiveSheet.Shapes
' if the current shape is invisble and we havn't already made one visible
If Limb.Visible = msoFalse And MadeVisible = False Then
' …make it visible
Limb.Visible = msoTrue
' and make sure we leave the rest of them alone!
MadeVisible = True
End If
Next Limb
Fortunately for you (and everyone else), you don't have to know how to write macros - you can just record them.
Before we look at how to record a macro, let's see first what recording is.
In some Microsoft Office applications, you can record macros:
Application | Recording possible? |
---|---|
Access | No |
Excel | Yes |
Outlook | No |
PowerPoint | Only up to version 2003 |
SharePoint | No |
Word | Yes |
Within your computer is a man called Bob (or a woman called Bobette, if you prefer). Bob is sitting with his pen poised, ready to write out the Visual Basic commands corresponding to everything you do in Excel (or Word). All you need to do is to press the Record button.
For example, suppose that you click on the cell C4 in the input form and type in Spiderman. Here's what Bob will write down:
Range("C4").Select
ActiveCell.FormulaR1C1 = "Spiderman"
So all that you need to do is to record copying and pasting the information for our example, then formatting the results cells to get:
What you should get at the end of the process
So now we know what recording is - it's time to do it!
To begin recording, click on the icon shown below (in Excel 2003 you can accomplish the same thing by selecting Tools -> Macro -> Record New Macro...).
Click on the icon shown below to begin recording a macro
You can now complete the dialog box which appears:
The following options are explained in more detail below:
Here's how to fill in the 3 parts of the dialog box shown above.
Macro names are subject to certain restrictions - the main one being that they can't contain spaces. Many people capitalise each part of their macro names:
This method of naming things is called camel case, after the ups and downs of a camel's back. Under this naming convention, English would include words like LeapFrog, RollerCoaster, CupBoard and LifeStyle.
Thus good names for our macro would be:
Note that many people use underscore characters to make macro names more readable - for example, Copy_Scores_To_Results_Sheet.
If you don't like your macro name, it's easy to change it when you've finished recording.
You can assign an upper or lower case letter to run your macro, but I strongly recommend the former!
Hold down the SHIFT key when typing a short-cut key into the box to insert an upper case letter.
The reason is simple - many lower case letters are reserved. For example, many people use CTRL + R in Excel to copy cells to the right, and it will confuse people if this short-cut key runs your macro instead!
You have 3 choices:
Storage location | What it means |
---|---|
Personal macro workbook | This will save your workbook in a special file called Personal.xlsm. The advantage of using this file is that Excel will automatically make its macros available to you whenever you have Excel open. |
This workbook | This will store your macros within your current workbook (obviously). |
New workbook | This will create a new workbook and record your macros into that. |
If you're new to Excel VBA macros, I'd recommend that you record into the personal macro workbook.
Now that you've started recording, you can perform a series of actions in Excel, knowing that the long-suffering Bob inside your computer will transcribe each into VBA's macro language.
Once you've started recording a macro, Excel (or the Bob/Bobette inside your computer) will transcribe everything you do into Visual Basic, until you stop recording. For this reason, you should proceed carefully!
For our example, here's what you should do.
Remembering that you are recording, you should follow these steps exactly to make your recorded macro do what you want:
First, click on the first cell to be copied (C4 on the Input Form worksheet), and copy it:
Right-click on each cell to be copied, and choose to copy it using the short-cut menu (as here) or any other way
Now go to the top of the list of names in the Results sheet:
Select the top "person" in the list of names
You now want to select the first non-blank cell in the list. To do this, you want to record the instructions:
Go down to the bottom of the block; then
Go down one cell further
rather than the instructions:
Go to cell B8 (which will always take you back to the same cell, whenever you run the macro)
You therefore need to switch from absolute to relative recording.
To change the way Excel records macros:
Choose the Developer tab on the ribbon (if you can't see this, see below).
Choose to use relative references.
If you can't see the Developer tab, see below
In Excel 2003, you can accomplish the same thing by clicking on the tool shown (this only appears once you have started recording).
If you can't see the Developer tab on your ribbon, you need to enable it. If you're using Excel 2007, first choose to show Excel options:
You can now show the Developer tab in the ribbon:
Tick the box shown to display the Developer tab on your Excel ribbon
Switching between absolute and relative recording is one of the few things for which Excel will not record commands.
You can now paste in the name you've copied:
| |
Go to the top of the column of names | Press CTRL + the down arrow to go to the bottom of the block, then press the down arrow again to go to the first blank cell |
If you patiently repeat the steps above, you will eventually build up a row of copied data:
The results of copying and pasting - the right data, but with the wrong formatting
You can now format the cells using the standard formatting tools so that:
the first cell is in a larger font;
the other cells are vertically aligned centrally; and
the whole row has an outline border.
Here's what the final result could look like:
The final result, correctly formatted
You can now stop recording!
To stop recording, click on the tool shown below (in Excel 2003 this tool is on its own toolbar, which appears when you start recording).
Click on the button shown to stop recording
You should now have a macro which copies a contestant's score to the results sheet. It's time now to tidy it up!
You could omit this last stage, but it's likely that you made several mistakes in the recording process which you'll need to rectify, and it can't do any harm to check what you've done!
Alexander Pope wrote:
You can't edit a macro until you can find it. To do this, press ALT + F11 to go into the VBA code editor (this opens up a separate application). You should then find your macro:
Expand the workbook into which you recorded your macro (here the personal macro workbook), then expand the Modules category. You can then double-click on Module1.
A module is the name given to the place in which VBA programming code is stored. Think of it as a blank sheet of paper into which Excel writes instructions in its own special language.
Here are some ideas for how to simplify your code! Note that your macro won't look exactly the same as this one, but you should be able to get ideas from what follows. Don't worry about making changes - you can add, delete and edit lines in the code window as you see fit, to change the way that your macro behaves.
Perhaps a good start is to remove unnecessary comments:
Comments are the lines which begin with an apostrophe - ' - and are in green. The ones shown selected add no value, and can be deleted!
The next bit copies a contestant's name - I've added a comment before it:
'copy the contestant's name
Range("C4").Select
Selection.Copy
The next thing is to go to the first blank cell on the Results sheet, and paste (again, I've added comments):
'go to the results sheet
Sheets("Results").Select
'select the top of the list of names, and
'from there go down to the bottom (note that we switched from
'absolute to relative recording mode while recording this)
Range("B4").Select
Selection.End(xlDown).Select
'go one cell further (to first blank cell)
ActiveCell.Offset(1, 0).Range("A1").Select
'now paste in contents of clipboard
ActiveSheet.Paste
When the macro has finished copying and pasting, it's time to look at the formatting. Firstly, my macro removes any background colour from the pasted cells:
'clear the "dancing ants" effect - we've finished copying
Application.CutCopyMode = False
'select the whole row to be formatted
ActiveCell.Offset(0, -3).Range("A1:D1").Select
'remove any background colour
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
The code to change the font size of the left-hand cell is a fair bit longer than it needs to be:
'if you change the font size, Excel records a command
'for every part of the FORMAT CELLS dialog box
'all we need is ...
Selection.Font.Name = "Calibri"
'the original code is left in here for reference
' With Selection.Font
' .Name = "Calibri"
' .Size = 14
' .Strikethrough = False
' .Superscript = False
' .Subscript = False
' .OutlineFont = False
' .Shadow = False
' .Underline = xlUnderlineStyleNone
' .ColorIndex = xlAutomatic
' .TintAndShade = 0
' .ThemeFont = xlThemeFontMinor
' End With
The code to set vertical alignment likewise reproduces every part of the Alignment tab of the FORMAT CELLS dialog box:
'change the vertical alignment to centred
'(all unnecessary lines commented out)
With Selection
' .HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
' .WrapText = False
' .Orientation = 0
' .AddIndent = False
' .IndentLevel = 0
' .ShrinkToFit = False
' .ReadingOrder = xlContext
' .MergeCells = False
End With
The code to set borders carefully considers every possible part of a cell's borders:
When setting the borders for a cell, you can set:
Based on this information, you should be able to simplify these lines of code!
Excel will automatically save any changes you've made to your personal macro workbook when you exit the application (or you could press CTRL + S or click on the usual disk icon to save your changes).
It's now time to run your macro to see what it does! There are several ways to do this ...
You can play a macro that you've recorded (and possibly edited) in several ways:
The rest of this blog shows how to do each of these things.
If you've assigned a short-cut key to your macro, you can press it now. In our case, it was SHIFT + CTRL + R.
The steps to assign a macro to a button are shown below:
To insert a button:
You can now click and drag to form the outline of your button:
Click and drag with the plus symbol to draw your button.
When you release the mouse button, you can choose which macro to assign to your button:
Choose from the list of macros that you've written - this appears automatically.
Finally, you can change the text displayed on your button:
Click and drag across the text of the button, and type something else in.
You've now got a clickable button! Note that in Excel 2003 you can achieve the same thing from the Forms toolbar:
Right-click on any Excel 2003 toolbar and choose to display the Forms toolbar (from this you can insert a command button).
The only hard thing about assigning a macro to a picture is choosing the right picture!
Paste any picture onto your worksheet, then right-click on it to assign a macro to it as shown here.
This option is different - and more complicated - in Excel 2003, so only the 2007/2010 method is shown here.
Our aim is to get an icon to appear on the Quick Access toolbar, so start by right-clicking on it:
Right-click anywhere on the toolbar at the top left of Excel to customise it
You can now choose a macro to assign to it:
Follow the numbered steps below!
The numbered steps shown above are:
If you follow these steps carefully, you'll end up with a custom icon:
Here I've gone for a pi symbol (or should that be tau?), for no particular reason. Clicking on this tool will run the macro.
Your customised quick access toolbar will remain visible even when the workbook containing your macro is closed down. If you click on the icon in this case, Excel will automatically open the workbook and then run the macro, which is pretty much what you'd want and expect.
So that's how to record macros! The next step, perhaps, is to learn how to write macros ...
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.