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.

  1. How to Record Macros in Excel Visual Basic
  2. What is Excel VBA, and how can you use it?
  3. Starting to Record Macros in Excel VBA (this blog)
  4. Recording your Macro and Finishing
  5. Tidying up your macro that has been recorded
  6. Playing back your macro recorded in Excel VBA

This blog is part of our Excel macros online tutorial series - complemented, of course, by Wise Owl's excellent training courses in VBA and Excel for businesses like yours!

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:

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:


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:

Example of final results

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...).

Icon to start recording

Click on the icon shown below to begin recording a macro

You can now complete the dialog box which appears:

Dialog box for recording macros

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:

Camel case - picture of camel

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:

  • CopyResults
  • CopyScoresToResultsSheet
  • CopyAndFormatScores

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!

Assigning short-cut key to macro

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.

This blog has 0 threads Add post