Excel VBA: Object-Oriented Programming

You’ve been using Excel for some time, but you probably never thought of it as an object. The more you work with VBA, the more you start seeing Excel in those terms. You’ll understand that Excel is an object and that it contains other objects. These objects, in turn, contain even more objects. In other words, VBA programming involves working with an object hierarchy.

At the top of this hierarchy is the Application object, which is essentially Excel itself. The Application object contains other objects such as Addin, Window, Workbook, etc. Each object contained within the Application object may contain other objects. For example, a Workbook object contains objects like Chart, Name, and Worksheet. In turn, each of these can also contain more objects. Consider a Worksheet object contained in a Workbook object, which is itself within the Application object. Some of the objects that can be found within a Worksheet are Comment, Hyperlink, Name, PivotTable, and Range.

Object Collections in VBA Programming

Collections are another key concept in VBA programming. In object-oriented programming, a collection is an object that contains a group of similar objects. To add to the confusion, a collection is itself an object.

Here are some examples of commonly used collections:

• Workbooks: A collection of all currently open Workbook objects.
• Worksheets: A collection of all Worksheet objects within a specific workbook.
• Charts: A collection of all Chart (chart sheets) objects in a particular workbook object.
• Sheets: A collection of all sheets contained in a particular workbook object.

Whichever collection you want to work with, you can perform the following actions:

• Refer to a specific object in a collection using an index value. For example, to refer to the second object in the worksheet collection, use one of the following statements:

Worksheets(2).Select 
'or 
Worksheets("Sheet2").Select

• Determine the number of items in a collection using the Count property. For example, in the Immediate Window, the instruction:

?Worksheets.Count

will return the total number of worksheets in the current workbook.

• Insert new items into a collection using the Add method. For example, in the Immediate Window:

Worksheets.Add

VBA will insert a new worksheet into the current workbook. The Worksheets collection now contains one additional item.

• Loop through all objects in a collection using the For Each…Next loop. Suppose you’ve opened a workbook containing five worksheets named: “Daily Salary”, “Weekly Salary”, “Monthly Salary”, “Yearly Salary”, and “Bonus”. To delete all worksheets whose names contain the word “Salary”, you can write the following procedure:

Sub DeleteSheets()
    Dim ws As Worksheet
    Application.DisplayAlerts = False
    For Each ws In Worksheets
        If InStr(ws.Name, "Salary") Then
            ws.Delete
        End If
    Next
    Application.DisplayAlerts = True
End Sub

Comments

• The line Application.DisplayAlerts = False is used to suppress prompts and alerts that Excel normally displays during code execution. In this case, it prevents the confirmation message when deleting sheets.
• The InStr function is useful for string comparisons because it allows you to find a string within another string. The expression InStr(ws.Name, "Salary") tells Excel to check whether the worksheet name (stored in the variable ws) contains the string « Salary ».

NOTE
Collection names are always plural.

Working with Collections

Declaring and Using a Custom Collection

To create a user-defined collection, begin by declaring an object variable of type Collection:

Dim myCollection As Collection
Set myCollection = New Collection
'or
Dim myCollection As New Collection

Adding Items to a Custom Collection

After declaring the Collection object, you can insert new items using the Add method. The syntax is:

object.Add item[, key, before, after]

You only need to specify the object (collection name) and the item to add. The optional arguments (key, before, after) are also very useful.

Items in a collection are automatically assigned index numbers starting from 1. However, each item can also be assigned a unique key. Instead of accessing an item by index (1, 2, 3, etc.), you can assign a custom key when adding it. For example, when creating a custom collection of worksheets, you could use the sheet name as the key. In a student or employee collection, you could use their ID number as the key.

If you want to specify the position of the object in the collection, use the before or after argument. The before argument specifies the object before which the new object is added; after specifies the object after which it is added.

Removing Items from a Custom Collection

To remove an item, use the Remove method:

object.Remove item

The object is the name of the collection. The item is the object you want to remove from the collection.

Referring to Objects in a Collection

Referring to an object is essential because you need to identify which object you’re working with. To reference a single object in a collection, use the object’s name or index inside parentheses after the collection name:

Worksheets("Sheet1")

Note that the sheet name must be enclosed in quotation marks. If you omit them, Excel won’t recognize the object. If “Sheet1” is the first (or only) sheet, you can also write:

Worksheets(1)

In this case, the number is not quoted. Use quotes for names, no quotes for index numbers.

Another collection, Sheets, contains all types of sheets (worksheets and chart sheets). If “Sheet1” is the first sheet in the workbook, you can also refer to it as:

Sheets(1)

DIFFERENCE BETWEEN WORKSHEET AND SHEET

• Worksheet represents a worksheet only.
• Sheet represents any sheet (either worksheet or chart sheet).

So, if your workbook has three worksheets and one chart sheet, you have:

• 3 Worksheets
• 4 Sheets

The Worksheets collection is a subset of the Sheets collection.

Navigating the Hierarchy

The object model in any VBA-enabled application is hierarchical. This hierarchy determines the proper syntax for your VBA macros.

For example, to enter the word “Bonjour” in cell A1 of Sheet1 in the currently open workbook, the full line of code would be:

Application.Workbooks("Workbook1.xlsx").Worksheets("Sheet1").Range("A1").Value = "Bonjour"

NOTE

In Excel versions prior to 2007, the file name in the code should be:

Workbook1.xls instead of Workbook1.xlsx

Workbook, Worksheet, Range, and Value are English terms meaning respectively: Classeur, Feuille de calcul, Plage de cellule, and Valeur in French.

This code literally means that we are entering the value “Bonjour” in cell A1 (Range("A1")), which is on the sheet “Sheet1” (Worksheets("Sheet1")), inside workbook “Workbook1.xlsx” (Workbooks("Workbook1.xlsx")), which itself is part of Excel (Application).

If you were to fully qualify every object reference, your code would become lengthy and harder to read.

Fortunately, VBA is smart. It knows you’re working in Excel, so you don’t always need to specify the Application object. The above code can be simplified as:

Workbooks("Workbook1.xlsx").Worksheets("Sheet1").Range("A1").Value = "Bonjour"

If you’re sure that « Workbook1.xlsx » is the active workbook, you can omit this reference:

Worksheets("Sheet1").Range("A1").Value = "Bonjour"

And if you’re currently working in « Sheet1 » when the code is executed, you can simplify it further. If the parent Worksheet object is not specified, VBA assumes you are referring to the active sheet:

Range("A1").Value = "Bonjour"

NOTE

Excel does not have a Cell object. A cell is an element of the Range object, which represents a cell or group of cells.