Recording macros in Excel Visual Basic - VBA Macros
Part two 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. Excel VBA Macros - Free On-Line VBA training
  2. What is Excel VBA, and how can you use it? (this blog)
  3. Starting to Record Macros in Excel VBA
  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.

What is Excel VBA, and how can you use it?

You've probably heard of VBA (or Visual Basic for Applications, to give the programming language its full name), but what is it?  And how can it help you?

I'm aware that there are ways to accomplish the task set out below without using macros, but that's not the point of this blog!

Our Example - Copying, Pasting and Formatting Information

To see how VBA could help you, suppose that you are a data entry clerk for a major talent show on national TV.  Every time there's a vote, the scores are entered into a spreadsheet:

Voting form - VBA macro example

Whenever a vote is taken, the judges type their scores onto this spreadsheet


From here, your task is to copy the results faithfully into a results sheet:

Results sheet - VBA example

You have to type in a row for the new contestant's scores

There are two main problems with this:

  1. It takes ages - with all the copying/pasting and formatting involved, at least 20 mouse clicks for each contestant.
  2. It's prone to error - you've noticed that you start making mistakes when you're tired.

You start dreaming - wouldn't it be fantastic if you could just click on a button to copy the results across:

Input form with button to transfer results

The magical button (if only you knew how to create it) ...


This blog shows you how to automate tasks like the one above using Visual Basic for Applications!  But first, what is VBA?

The VBA Programming Language

You don't really need to know this, but it's good background ...

All computer programs are written using a programming language (examples are Fortran, C#, C, Pascal and Visual Basic).  The Visual Basic language - VB to its friends - is particularly favoured by Microsoft, and is used by millions of programmers around the world.  Here's a bit of VB:

'if no menu (eg search page), hide

Select value

Case 0

Title = "Search results"

Me.lblBreadcrumbs.Visible = False

Exit Property

Case 1

Title = "Problem with page"

Me.lblBreadcrumbs.Visible = False

Exit Property

Case Else

End Select

You can see that the language looks half like English and half like meaningless computer gibberish. 

Microsoft have included a cut-down version of this language within each Office application (Word, Access, Excel,  PowerPoint, SharePoint, Outlook and Visio all include variants of VBA).  This language is called Visual Basic for Applications, or VBA, because it's Visual Basic running within an application.  Here's an example of VBA from Excel, taken from a hangman game:

Function DrawScaffold()

'each part of the gallows

Dim Limb As Shape

' checks all the shapes on the worksheet and makes one of them visible

' has a shape been made visible in this function call?

Dim MadeVisible As Boolean

' assume no shapes have been made visible in this function call

MadeVisible = False

' loop through all shapes

For Each Limb In ActiveSheet.Shapes

' if the current shape is invisble and we havn't already made one visible

If Limb.Visible = msoFalse And MadeVisible = False Then

' …make it visible

Limb.Visible = msoTrue

' and make sure we leave the rest of them alone!

MadeVisible = True

End If

Next Limb

Fortunately for you (and everyone else), you don't have to know how to write macros - you can just record them. 

This blog has 0 threads Add post