Using Object Variables

In addition to storing values, a variable can store a reference to an object. You assign an object to a variable for the same reasons you assign any other value to a variable:

• Variable names are often shorter and easier to remember than the values they contain (or, in this case, the objects they refer to).
• Variables can be changed to refer to other objects while your code is running.
• Referring to a variable that contains an object is more efficient than repeatedly referring to the object itself.

Declaring Object Variables

You declare an object variable the same way you declare other types of variables. For example, to declare the object variable mySheet, you could write the following code.

Dim mySheet As Object

This declaration uses the generic Object type specifier. Generic object variables are useful when you don't know the specific type of object the variable will contain, or when the variable must at different times contain objects from several different classes. For example, if you set mySheet to an object returned from the Sheets collection, it could be one of four classes: Worksheet, Chart, Module, or DialogSheet.

You can also declare an object variable using a specific class name, as shown in the following code.

Dim mySheet As Worksheet

In this example, if the code tries to set mySheet to an object other than a Worksheet object, a run-time error occurs (error 13, "Type mismatch"). You can see this by running the following Sub procedure.

Sub ListSheetTypes()
    Dim mySheet As Worksheet
    For Each mySheet In ActiveWorkbook.Sheets
        MsgBox TypeName(mySheet)
    Next
End Sub

The run-time error occurs when the For Each...Next loop tries to set mySheet to a member of the Sheets collection that's not a Worksheet object.

Declaring object variables as specific classes can help you find bugs in your code, because the run-time error indicates that the code is doing something other than what you expected.

Assigning Object Variables

You assign an object to an object variable using the Set statement.

Set variable = object

For example, the following code sets the object variable myRange to the object that refers to cell A1 on Sheet1.

Set myRange = Worksheets("Sheet1").Range("A1")

You must use the Set statement whenever you want an object variable to refer to an object. If you forget the Set statement, several different errors can occur. For example, if you previously declared the object variable, a run-time error occurs (error 91, "Object variable not set") when you run the following code.

Dim myRange As Object
myRange = Worksheets("Sheet1").Range("A1")    ' error 91 occurs here

If you haven't previously declared the object variable, it will be a Variant variable by default. In this case, if you forget the Set statement, Visual Basic attempts to use the default property of the object to assign a value to the variable. For example, the following code causes Visual Basic to use the default property of the Range object (which is the Value property) to assign myRange the value of cell A1.

myRange = Worksheets("Sheet1").Range("A1")    ' forgot the Set statement!

In this example, no error occurs in the line of code in which you forgot the Set statement. However, several different run-time errors can occur later, depending on how your code uses the variable myRange. The most common errors in this case will be error 424, "Object required" and error 13, "Type mismatch." When you see either of these errors, check your code for missing Set statements.