This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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.

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

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

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:

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!

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.

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.

Parts of this blog
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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR