Assigning

After a variable has been declared (and initialised with its default value) it can be assigned a different value.
For Value Types / Not Objects, a variable is a pointer to the memory location directly.

Dim myLong As Long 
myLong = 20

For Reference Types / Objects, an object variable is a pointer to the address of the memory location.

Dim objWorkbook As Workbook 
Set objWorkbook = ThisWorkbook

Object Variables

It is more efficient to create an object variable if you are going to refer to an object more than once.
Since object variables are pointers it is possible for more than one object variable to refer to the same object.
If you are using object variables then it is important to keep track of what they are referencing.
It is not a good idea to have more than one object variable pointing to the same object.


Declaring an Object Variable

All variables need to be declared before they can be assigned, and object variables are no different.
When an object is assigned to an object variable this process is called binding.
An object is early bound (and uses Early Binding) when it is assigned to a variable declared to be of a specific object data type (eg Excel.Workbook).
An object is late bound (and uses Late Binding) when it is assigned to a variable declared to be of data type Object.


Assigning an Object Variable

When it comes to assigning a value to a variable the syntax is slightly different for objects and non objects.
The Set statement must be used to assign an object reference to an object variable.

Dim objWorkbook As Excel.Workbook 
Set objWorkbook = ThisWorkbook

If you don't know the data type, you can use the Object data type.

Dim objWorkbook As Object 
Set objWorkbook = ThisWorkbook
Call MsgBox(objWorkbook.Name)

It is more efficient to use a specific data type if you can.


As New

Never declare an Object data type using the "New" keyword.
Including the New keyword in the Dim statement creates what is called an auto-instancing variable.
This does not create the object when the variable declaration is processed but instead automatically when the variable is first encountered in code.
This also means that you are unable to test whether the variable Is Nothing.

Dim objSomething As New MyObject 

versus

Dim objSomething As MyObject 
Set objSomething = New MyObject

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