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:

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:

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:

• CopyResults
• CopyScoresToResultsSheet
• CopyAndFormatScores

Note that many people use underscore characters to make macro names more readable - for example, Copy_Scores_To_Results_Sheet. 

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:

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.