Writing VBA Code

If you've been following this blog series so far you should have a newly created subroutine with a flashing text cursor poised and ready to write some real code.  This article will explain some of the basic rules of writing VBA and show you a couple of tricks to get you creating code as quickly as possible.

The Basic Grammar of VBA

VBA is a language, and like any language it has grammatical rules that you need to follow if you want to make sense when you're "speaking" it.  Generally when you're writing VBA programs, most of the time you'll be attempting to perform some kind of action on some sort of object.  The structure of a line of code that performs an action on an object is very consistent in VBA - you always start the sentence by referring to the thing you want to do something to, followed by the action you want to perform, and you use a full stop to separate the two.  So, very generally speaking, a basic sentence in VBA would look like this:

In VBA terms, the Thing part of the above sentence would technically be called either a collection or an object.  The Action part of the sentence would be referred to as a method.  So, the technical way of representing the above sentence would look like this:

Bearing this in mind, we're going to write a line of code that will apply the Add method to the Workbooks collection.

Writing Your First Line of Code

In our first line of code, the collection part of the sentence is the word Workbooks.  Workbooks is the name for the collection of all of the currently open Excel files. Type it into your code and follow it immediately with a full stop.

Typing in a full stop after a word that VBA recognises presents you with a list of other words you can use to finish the sentence.

After typing in the full stop you should see a list of keywords appear automatically.  This feature is referred to as Intellisense - horrible name, useful feature!  The next section describes several ways to use Intellisense to save you as much typing as possible.

Using Intellisense to Complete a Sentence

After the Intellisense list appears we can complete our sentence in a number of ways.  The method part of our line of code is the word Add - to get this word into your code you could do any of the following:

Probably the quickest technique to use in this example is to type in the letter A which will automatically select the word Add in the list, and then press Enter.

Congratulations, you've finally written your first line of code!  When we get around to running our subroutine, this line will create a new workbook.

In Word, the collection of open files is called Documents.

In PowerPoint, the collection of open files is called Presentations.

Changing Things in VBA by Using Properties

So far we've seen how to create a new workbook in Excel VBA by applying the Add method to the Workbooks collection.  Now that we've done this we need to add some text to some of the cells in the file that we've just created.  We're going to do this by modifying a property of an object.  Properties are like methods in that they always appear after the object or collection in a VBA sentence.  Unlike methods, you can assign a value to a property to change it in some way.  The basic grammar of a line of code to do this would look like this:

The object we are interested in is a cell, which is referred to as a Range in Excel VBA, and the property we want to change is called Value.  Type the following into your code:

You can identify the properties in the list by their "finger pointing at a piece of paper" symbol.

Referring to a Range object is slightly more complicated than referring to the Workbooks collection because we have to say which cell we are interested in.  We do this by typing a cell reference (here it is A1) inside a set of round brackets and double-quotes.  Next, we can type in a full stop to display the list of properties and methods.

The quickest way to select the Value property from the list is to do the following:

1. Type in the letter V to jump to the word Validation.
2. Press the down arrow key on the keyboard to select Value.
3. Press Tab.

This should leave you with a subroutine looking like this:

The only remaining thing is to say what we want to change the value of the cell to.

We can now say what text we want to appear in the cell.  To do this we need to type in an equals sign, = followed by the text.  All literal text in VBA must be enclosed in a set of double-quotes.  Type in the following and press Enter at the end of the line.

When you press Enter at the end of the line you should see a space appear on either side of the equals sign.

As a final flourish in our very basic program, we're going to write a line that will put today's date into another cell on the spreadsheet.  This line will look very similar to the one we've just created, so type in the following code.  Try to remember the quickest way to use the Intellisense list - hint: it doesn't involve the mouse!

Press Enter at the end of the line and you should see the word date becomes capitalised.

Rather than putting in the date as a string of literal text, we've used a built-in VBA function called Date.  This function calculates what today's date is each time the code is run (based on your computer's clock) and puts the result of the calculation into the cell.

Working Out How to do Other Things

Although we've barely scratched the surface of what you can do with VBA, you've learnt a couple of basic rules of grammar that will help you as you learn how to do new things.  One fairly good way to find out how to do other things in VBA is to record a macro.  Recording a macro means that you can do a bunch of things in an Office application, formatting some cells in Excel for instance, and have the application write out the VBA code for you as you are doing it.  This technique has some limitations, but it is a neat way to discover new methods and properties. 

Testing and Running a Subroutine

So you've written a program, but you don't have any idea if it's going to work!  The next article in this series shows you how to run a program from a developer's point of view and explains a few useful techniques for letting other people run the code you've written.