Data Type - Object

This data type contains a reference (or an address) to an actual object.
This data type uses 4 bytes
The default value is Nothing


Just as the Variant data type can store any type of variable, the Object data type can store any kind of object.
Whenever you use this data type it will always use late binding
The Nothing keyword can be used to disassociate an object variable from an actual object.
The Set statement assigns an object reference to an object variable.

If (oMyObject) Is Nothing Then 
   Set oMyObject = Nothing
End If

You can find out if an object variable has been set by using the "Is Nothing" keywords.
Using the generic Object data type is much less efficient than using the specific object declaration. This method is referred to as late binding and should be avoided


All VBA Objects fall into one of two classes. Singular objects or objects within a collection.
Single objects are referenced by name.
Objects in a collection are referenced by an index.


To avoid long duplicate references when qualifying objects use the Set statement to create an object variable. Using object variables can also help your code execute more quickly.


Early Binding vs Late Binding

More Details
Object variables are declared in much the same way as other variables.
There are three ways to declare object variables:

Dim oMyObject As Library.ClassName       'early bound, no instance is assigned / Nothing  

Dim oMyObject As New Library.ClassName 'early bound, instance is assigned.
'The object is not actually created until the object variable is used

Dim oMyObject As Object 'late bound, no instance is assigned / Nothing

To assign a reference to a real instance of a class you must use the Set statement.
This creates an early bound reference to the object

Set oMyObject = Library.ClassName 

When you are using early bound then you must use the References dialog box to add a reference.
When you are using late bound then you must use the CreateObject or GetObject functions.


It is also possible to declare any application specific objects using the generic object data type. This however is much less efficient than specifying the actual object. This method relies on late binding and can make the program run significantly slower.
Using object variables can greatly improve readability when you need to refer to the same object more than once. Using object variables can also significantly increase the execution time of your code if not used effectively. Every reference that has to be resolved requires a hit to the OLE IDispatch interface. The fewer object references that have to be resolved the quicker the code will run.
Most methods return values, however some return objects. You cannot assign an object to a variable using an equals sign. Instead you must use the "Set" statement.
You can use the VarType() function to discover how a variant is actually represented.


Visual Basic supports both the creation of both specific and generic object variables. To obtain the type of an object use the TypeName function
Never declare a new object when it is declared, (eg Dim object as New Connection). Always declare the object and then set the object before you actually need it and always remember to set it to NOTHING (eg set object = Nothing) when you have finished with it.
It is possible to declare any object using the generic Object data type
Using the Object data type is much less efficient than a specific object declaration.
The reasons for this is because Excel cannot tell what type of object the variable refers to until the program is running, which obviously consumes some execution time.
This is referred to as late binding and can make programs run significantly more slowly.


Dim oWorkbook As Object 
Dim oWorkbook As Workbook

Using the generic object data type should be avoided.



© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext