The Building Blocks of VBA

VBA is an example of an object-oriented programming (OOP) language.  Roughly speaking, this means that the language has several characteristic building blocks, as described in the table below:

Basic Sentence Structure

In order to make something happen using VBA you can either apply a method to an object, or change a property of an object.  To do this, you'll write an instruction which has one of two common structures:

Of course, things can become more complicated than this - your instruction may need to refer to multiple methods and properties in the same sentence - but the basic building blocks are always the same.

Applying Methods

A method is an action that an object can perform.  One way to make something happen in VBA is to apply a method to an object.  Start by creating a new workbook and in the VBE insert a new module.  Create a new subroutine and add a comment which resembles the one shown below:

You can give your subroutine a different name if you prefer.

Next, refer to the Worksheets collection object and enter a full stop:

The IntelliSense list displays the methods and properties of the object you've just referenced. You can identify the methods by their distinctive, "flying green brick" icon.

Type in or select the Add method from the list:

You can either type the method name, or select it from the list.

When we run the subroutine later, this instruction will create a new worksheet and make it the active sheet.  Next, we'll apply a method to some cells on the worksheet.  Start by referring to cells A1 and B1 as a Range object:

The IntelliSense shows the methods and properties of the Range object you have referenced.

We want to apply the Select method to the cells we've referenced.  You can either type the name of the method or select it from the list:

Select is definitely a method - it has a flying green brick next to it!

For the last example of a method we'll apply the Merge method to the cells we've just selected.  You can refer to the selected cells as the Selection object:

Type the word selection followed by a full stop.

Unfortunately, when you enter a full stop after the word selection you won't see the IntelliSense prompting you with the available methods and properties.  This is because in Excel you can select many different types of object, each with a different list of methods and properties.  The Selection keyword doesn't know what type of object will be selected and so doesn't show a list of options.  You can still type in the name of a method and trust that you'll have the correct type of object selected when your code is running:

As the previous line selects a range of cells, we can be fairly confident that this will be the type of object that the Selection keyword will refer to!

Changing Properties

A property is an attribute of an object.  You can make something happen in VBA by assigning a value to a property.  Let's see some examples to demonstrate this.  Start by adding some new comments to the subroutine you're writing:

The comment is just a reminder of the sentence structure for changing the value of a property.

Next, refer to cell A1 as a Range object and enter a full stop:

The IntelliSense list appears after typing a full stop. The properties are displayed with an icon which shows a hand pointing at a list. Naturally.

The first property we'd like to change is called Value.  This property allows you to change the contents of a cell:

You can either type in or select the Value property from the list.

To assign something to the property, enter an = sign followed by the value you want to use:

Here we're assigning a string of text to the Value property. In VBA, strings are enclosed in double-quotes.

Next we'll change the height of the cell by changing the RowHeight property.  Start by referring to the Range("A1") object again:

Enter a full stop and then type or select the RowHeight property.

Assign a number to the RowHeight property:

The RowHeight property can be changed to a number which represents a size in points.

Add two further lines which change the Value property of Range objects:

The two new lines are very similar to the first.

Using Multiple Methods and Properties

All of the instructions we've written so far have used either a single method or property.  You'll often find that you need to combine a sequence of methods and/or properties in order to achieve the result you want.  Let's add some examples to our code.  Start by adding a new comment and then refer to the Range("A1") object:

Just for reference, here's all the code we've written so far.

We'd like to change the fill colour of the range we've referenced, which involves referring to two properties.  The first one is called Interior:

You'll find the Interior property in the IntelliSense list.

Next, enter another full stop and look for the Color property:

The Interior property returns a reference to an object. You can then apply further methods and properties to that object.

You can then assign a value to the Color property:

You can press Ctrl + Spacebar to see the IntelliSense list after typing =

For the next example, we'll combine two more properties to change the size of the text in a cell. Start by referring to Range("A1") and then look for the Font property:

Referring to the Font property returns a reference to an object to which you can apply further methods and properties.

We can then refer to the Size property:

There are many other properties you could change to affect the appearance of the font, but we'll stick with Size for now.

You can then assign a number to the Size property:

The number you enter here represents the size of the font in points.

For the next example we'll combine a property and a method to alter the width of column B.  Start by referring to Range("B2") and then look for the EntireColumn property:

The EntireColumn property returns a reference to an object to which we can apply further methods and properties.

We can now apply the AutoFit method to the object returned by the EntireColumn property:

The AutoFit method changes the width of a column to fit its widest entry.

At this point we can run the subroutine and check that it performs its tasks as expected.  You can press F5 to run the entire procedure in one go, or press F8 to execute the instructions one-by-one.  The end result of the procedure should resemble the example shown below:

It's far from the most complicated thing you'll do in VBA, but it's a good demonstration of the basic building blocks of the language.

To practise working with objects, methods and properties you can extend the subroutine that we've been writing in this section of the lesson.

1. Start by adding some new code to the subroutine to change some formatting properties of cells A2 and B2:

2. Change the Value property of two cells to add a description of an object:

3. Use the same technique to add descriptions of methods and properties:

4. As a frivolous extra, apply the Speak method to the range of cells to hear the contents spoken by your computer:

5. Run the subroutine to see (and hear!) the results:

A very brief summary of the basic building blocks of VBA.