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.

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.

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.

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:

1. Click on the project in the Project Explorer.

2. Type in a new name for the project in the Properties window.

A project name can't contain spaces and various other punctuation characters - it's best to stick to text and numbers.

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

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.

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!

Comments on this blog

This blog has 16 comments: