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.
- Excel VBA Macros - Free On-Line VBA training
- What is Excel VBA, and how can you use it? (this blog)
- Starting to Record Macros in Excel VBA
- 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.
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:
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:
You have to type in a row for the new contestant's scores
There are two main problems with this:
- It takes ages - with all the copying/pasting and formatting involved, at least 20 mouse clicks for each contestant.
- 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:
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
Title = "Search results"
Me.lblBreadcrumbs.Visible = False
Title = "Problem with page"
Me.lblBreadcrumbs.Visible = False
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:
'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
Fortunately for you (and everyone else), you don't have to know how to write macros - you can just record them.