Introduction to Visual Basic for Applications (VBA)
Part one of a five-part series of blogs

If you've heard people in your office talking about macros or VBA but don't understand what they mean, this blog series will make everything clear. It's an introduction to the most basic skills you'll need to start creating your own programs in any of the Microsoft Office applications.

  1. Introduction to VBA (Visual Basic for Applications) (this blog)
  2. Creating Your First VBA Program
  3. Writing VBA Code (Visual Basic for Applciations)
  4. Running a Subroutine in VBA macros
  5. Problems When Running VBA Code

Posted by Andrew Gould on 01 July 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.

Introduction to VBA

Visual Basic for Applications, or VBA for short, is a powerful programming language that is embedded in every Microsoft Office product.  Whether you are using Word, Excel or PowerPoint, you are only ever a few mouse clicks away from starting to write your own programs.

Because we use VBA in so many of our blogs, we thought it would be useful to have an article that introduces the uninitiated to the most fundamental techniques you'll need to start working with VBA.  So even if you've never written a line of code, or recorded a single macro, fire up your favourite Office application and read on to take your first steps on the way to becoming a VBA programmer!

Getting to the Visual Basic Editor

Whichever Microsoft Office application you happen to be using, you can be sure that embedded within it is another application called the Visual Basic Editor.  This is the application that you use to write your VBA code.  There are various menu or Ribbon options that will take you to the VB Editor, depending on which application and which version of Office you are using, but you can always get to the VB Editor with a keyboard shortcut.  To do this, hold down the ALT key on the keyboard, and then press F11.

The Visual Basic Editor

This is how the VB Editor should look the very first time you open it.

In the above diagram, the two windows that we've highlighted are:

1. The Project Explorer

2. The Properties window

The VB Editor application is the same regardless of which Office application you are in when you open it.  You will see slightly different things in the Project Explorer and Properties window depending on which application you are using - the diagram above is using Excel 2007.

You can use the shortcut ALT + F11 at any time to toggle between the VB Editor and the Office application you are working in.  Much quicker than clicking with the mouse!

If you can't see the two windows that we've highlighted in the image above, you can go to the View menu at the top of the VB Editor to display them.

Displaying the Project Explorer and Properties window

You can click the options shown here to show the two relevant windows, or use the keyboard shortcuts that are listed next to the options in the menu.


Working with Projects

A project is the name for the collection of VBA objects that are part of the file you are working on.  A project is created automatically when you open a new Office file (such as an Excel workbook, Word document, or PowerPoint presentation), and you can only have one project associated with one file.

The only useful thing you can really do with a project at this point is to rename it.  To do this:

Renaming a project

1. Click on the project in the Project Explorer.


Although you can only have one VBA project for each Office file, you might also see other projects listed when you go into the VB Editor.  These extra projects include things like the Excel Personal Macro Workbook, the Word Normal template, Excel Add-Ins, and the Project Global template.

The Project Explorer in Microsoft Projectg The Project Explorer in Microsoft Word
The VB Editor in Microsoft Project always contains the ProjectGlobal VBA project.  This project is attached to the Global.MPT template file. In Microsoft Word you will always see a VBA project associated with the template your document is based on - usually this will be the Normal template.

Working with Modules

Before you can start writing any code, you need to insert a module.  A module is the electronic equivalent of a blank piece of paper.  Each project can contain multiple modules - the exact number you have will depend on the size and complexity of the system you are developing, as well as your personal preference on how to organise your code.

To insert a new module, right-click on the VBA project you want to insert the module into, and from the menu choose: Insert -> Module

Inserting a module

You can actually right-click anywhere in the project you are working on to do this, but it's easier to spot the bold text of the project name when you have several files open.

When you've done this, you'll see a new item in the Project Explorer window, and the great white expanse of the module you've just created taking up most of the right hand side of your screen.

Renaming a module

You can rename a module in the same way you renamed a project earlier: select the module in the Project Explorer, and type in a new name in the Properties window.  Again, you can't use spaces or most of the punctuation characters - stick to text and numbers to be safe!

Now that you've inserted and renamed a module, you can click into it with the mouse and start typing your VBA code.  Read the next part of this series to find out exactly what you need to type!

This blog has 0 threads Add post