You use this type of variable for creating a new instance of an object which will be necessary should you wish to communicate with other applications namely Microsoft Word, PowerPoint or any other external library.

Declaring Object Variables

Object Variables, similar to normal variables, are declared with the Dim or Public statement, for example:

Dim mycell As Range.

Assigning Object Variables

To assign an object expression to an object variable, use the Set keyword.

For example:

Set ObjectVariable = ObjectExpression

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

Example:

The following procedure will select the cell A1 on Sheet1, input the value 100 and format it with Bold, Italic and Underline.

Sub ObjectVariable()
    Worksheets("Sheet1").Range("A1").Value = 100
    Worksheets("Sheet1").Range("A1").Font.Bold = True
    Worksheets("Sheet1").Range("A1").Font.Italic = True
    Worksheets("Sheet1").Range("A1").Font.Underline = XlSingle
End Sub

The line of code Worksheets("Sheet1").Range("A1") is repeated four times within this procedure.

If we declare an Object Variable called mycell to be a range, we can then set mycell to be equal to Worksheets("Sheet1").Range("A1").

The procedure would then become:

Sub ObjectVariable()
    Dim mycell As Range
    Set mycell = Worksheets("Sheet1").Range("A1")
    mycell.Value = 100
    mycell.Font.Bold = True
    mycell.Font.Italic = True
    mycell.Font.Underline = xlSingle
End Sub

It is good practice to set all object variables to Nothing at the end of the lifecycle of the variable even though Visual Basic will destroy all variables and pointers automatically once the routine has ended.

It is possible to have an object variable allocated to memory after the event of an error occurring. If the error handler allows the procedure to continue, it may be necessary to re-set the same object variable. This is when an object should be destroyed and then re-initialised.

At the end of a procedure, destroy all object variables in the following manner:

Set objWSheet = Nothing

Generic objects are useful when you do not know the specific type of object the variable will contain or when the variable contains objects from several different classes.

For example:

Dim mycell As Object
Set mycell = Application.Worksheets(“Sheet1”).Range(“A1”)

Next Topic: Arrays

Want to teach yourself Access? Free online guide at About Access Databases

Note: You may want to search for more information on Object Variables especially to understand the difference between Late and Early Binding.