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 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. How to Record Macros in Excel Visual Basic 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 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 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.

How to Record Macros in Excel Visual Basic
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

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

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:

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.

Parts of this blog
How to Record Macros in Excel Visual Basic 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

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

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

Our Example - Copying, Pasting and Formatting Information

The VBA Programming Language

Head office

London

Manchester