Module 1 - Getting Started in VBA
Lesson 1.4 - How VBA Works
Topic 1.4.1 - Making Things Happen

In the first lesson of this module we wrote a simple procedure which performed a few basic actions with worksheets and ranges of cells.  Although the example was quite short, it contained several examples of a common sentence structure in VBA.  Understanding this sentence structure and its components is key to working in VBA effectively.  This part of the lesson describes the key building blocks of the VBA language.

Files Needed

You don't need any files for this page.

Completed Code

You can click here to download a file containing the completed code.

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:

Building Block Description Examples
Object Any single "thing" or item that you can manipulate in VBA. Object is a deliberately vague term which could represent almost anything in Excel; from physical things that you can interact with, to more abstract, intangible items. All objects are based on a class, which defines exactly how the object works A cell
A chart
A worksheet
A shape
 
Collection A collection is itself an object which you can manipulate in VBA. A collection is also a group of all of the objects of one specific type. Many common Excel objects belong to a collection. All open workbooks
All sheets in a workbook
All data series in a chart
All shapes on a worksheet
Method An action that you can apply to an object. Method names are usually verbs, indicating that you're doing something to an object. Select a worksheet
Copy a cell
Save a workbook
Property An attribute of an object which you can change to have another value. Some properties are read-only, meaning that you can't alter them. The value of a cell
The name of a sheet
The count of charts

There are plenty of other elements of the VBA language which don't correspond to one of these building blocks, but the items listed in the table above are the most important in terms of making things happen.

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:

'apply a method to an object

Object.Method

 

'change a property of an object

Object.Property = something

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:

Basic sub

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

 

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

Intellisense

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:

Choose Add method

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:

Refer to range

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 method

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:

Selection

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:

Merge

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:

Add comment

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:

Range

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:

Value

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:

Assign text

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:

Rowheight

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

 

Assign a number to the RowHeight property:

Assign number

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:

Values

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:

Refer to range

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:

Interior property

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

 

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

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:

Assign value

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:

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:

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:

Alter size

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:

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:

Autofit

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:

End result

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:

'format cells A2 and B2

Range("A2:B2").Interior.Color = rgbLightGrey

Range("A2:B2").Font.Italic = True

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

'add description of Object

Range("A3").Value = "Object"

Range("B3").Value = "A thing"

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

'add description of Method

Range("A4").Value = "Method"

Range("B4").Value = "An action"

 

'add description of Property

Range("A5").Value = "Property"

Range("B5").Value = "An attribute"

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

'Speak cell contents out loud

Range("A3:B5").Speak

The Speak method relies on the Text to Speech feature of Microsoft Windows.  You'll need your speakers switched on in order to hear the results!

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

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

 
This page has 0 threads Add post