564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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
How to record macros in Excel Visual Basic
Part three of a six-part series of blogs
If you're not sure what the Excel Visual Basic for Applications language is (or how it can help you) this blog attempts to explain! Learn what VBA is, and how you can record macros and then replay them again and again to automate work in Excel.
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:
|PowerPoint||Only up to version 2003|
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:
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.
|Parts of this blog|
25 Aytoun Street