BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- How to Record Macros in Excel Visual Basic
- What is Excel VBA, and how can you use it?
- Starting to Record Macros in Excel VBA (this blog)
- Recording your Macro and Finishing
- Tidying up your macro that has been recorded
- Playing back your macro recorded in Excel VBA
Posted by Andy Brown on 30 June 2011
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Starting to Record Macros in Excel VBA
Before we look at how to record a macro, let's see first what recording is.
What is Recording? Introducing Bob and Bobette
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!
Starting to Record
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.
1 - Choosing a Macro Name
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.
2 - Choosing a Short-Cut Key
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!
3 - Choosing where to Store your Macro
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.