Chapter 15 Object Oriented Programming

15.1 The object model in VBA

In lectures we went through some of the basic philosophy of object oriented programming (OOP). Here we will concentrate more on the practical implications in VBA itself. This will, inevitably, only be a rather superficial review, as there is a wealth of information concerning the object model in VBA. We will concentrate on explaining how to access more information from VBA itself.

The main tool that VBA provides to help with the object model is the Object Browser. This can be accessed from the View menu in the main ribbon in the editor. Upon opening this browser you will be presented with an enormous quantity of objects and their properties which appear in VBA and Excel. The opening part of this is shown in Figure 15.1.

The Object Browser

Figure 15.1: The Object Browser

At the top of the Browser are various search options. It is possible to filter by the library (and so restrict to objects in VBA, or Excel), but usually it is easier to search for something of interest in the textbox just below. After entering the search string and pressing the binocular icon a list of places where that string appears will be displayed.

Suppose we are interested in knowing the various properties, methods, and events which can be associated to a Range object. So far we have only seen a handful, and have restricted ourselves entirely to the Range.Value property.

In Figure 15.2 we see just a very small part of the long list of attributes which are associated to the Range Object. Most of these are rather technical, and we will rarely need to use more than a small number, but if you want to manipulate an object, or find out some property of it, the object browser will provide an idea of what kinds of options might be available to you.

Some of the attributes of a Range object

Figure 15.2: Some of the attributes of a Range object

Looking down the list we see various attributes whose meaning we can guess. There are properties such as RowHeight, Value, VerticalAlignment, and WrapText, which are fairly clear, and others such as Style, Top, Value2, and SoundNote, which sound like they might be useful but are less obvious. (There are also others like ServerActions and XPath which are very likely to be technical properties we should never need to know.)

Unfortunately the inbuilt help function in VBA is not particularly helpful. (One might argue that it is a step backwards compared to earlier versions!) All it does is provide a front end to a web search of various online resources; however, having found an attribute that might be useful the online help will usually tell you all you need to know about how it works.

Note the various icons that appear in our list. We have scrolled down to the end of the Properties part of the list, which is followed by the Method part of the list. These can be distinguished by the different icons appearing in the left-hand margin. Events (which are not shown here) are distinguished by a lightning bolt icon.

So far we have seen plenty of object properties, such as their value or their width. Examples include

  • Range("A1").ColumnWidth=10
  • Range("A1").Font.Name="Arial"
  • Worksheets("Sheet1").Columns("A").ColumnWidth=10

We are also very familiar with objects acting on other things (although we may not have thought of it in these terms before). All of the uses of Application.WorksheetFunction which we have seen fall into this category. Other examples include the Range.Copy command we saw in lectures, and

Worksheets("Sheet1").Copy After:Worksheets("Sheet3")

which creates a copy of Sheet1 and places it after Sheet3.

Events on the other hand seem less familiar. It is not so easy to remember examples where objects have changed in reaction to an event. The example we saw in lectures of the Worksheet.Calculate command is not an obvious one.

When a user clicks on a button we would expect some property of the object (such as their value) to change, and this is exactly what we mean by an event attribute for an object. A slightly different kind of example is

Private Sub Worksheet_Calculate()
Columns("A:F").AutoFit
End Sub

Here the width of the first 6 columns is auto-fitted each time the values in the Worksheet are recalculated.

If we use the full name of an object then this can get rather cumbersome. It is possible to define object variables to make our life easier. For example, the code

Dim WB As Object
Set WB = Workbook("Labsession5.xlsm")

defines the variable WB as an abbreviation for the more lengthy Workbook("Labsession5.xlsm")

Objects can be organised in collections. Members of a collection have to be at the same level in the object hierarchy, and can be referred to just by a number. There are also lots of standard collections: the Worksheets collection consists of all of the Worksheet objects in a particular Workbook, while the Charts collection refers to all of the Chart objects in a Workbook. We have used the Worksheets collection syntax repeatedly to refer to objects in various earlier examples.

Collections are useful because they can be combined nicely with Loops. If we have a collection then it is possible to rum through all of the objects in that collection using the syntax

For Each element In collection

at the start of the loop and

Next element

at the end. We will illustrate this with two examples taken from Walkenbach (page 159). The first deletes the first row from each worksheet in the active workbook:

Sub DeleteAllRow1s()
Dim WkSht As Worksheet
For Each WkSht In ActiveWorkbook.Worksheets
    WkShy.Rows(1).Delete
Next WkSht
End Sub

Note the use of an action (.Delete) on a subobject (WkSht.Rows(1)) of the object in the loop. The second example i a little more complicated. Suppose that we wish to replace all of the numbers in a range by their negatives.

Sub ChangeSign()
Dim Cell As Range
For Each Cell in Range("A1:Z26")
    If Not Cell.HasFormula Then
        If IsNumeric(Cell.Value) Then
            Cell.Value = Cell.Value * -1
        End If
    End If
Next Cell
End Sub

We can check for the numbers using IsNumeric, but this will also catch any formulas in the sheet and overwrite them as numbers (which is probably not what we want). So we use the Range collection for our loop, the Cell.HasFormula property to detect whether there is a formula, and the Cell.Value property to test for numbers and to modify the entries. Here we see a variety of aspects of the object model in action.

It is natural to ask if we can create new objects in our VBA code. This would be the obvious thing to do if we want to work in the spirit of the object oriented philosophy. It turns out that we can indeed define objects, and write code to describe their properties, methods, and events. To do this we use the Class Module option from the Insert menu in the editor. However, the details of this get quite complicated, and it falls beyond the scope of this module.