Dim As New

Never declare a Reference data type using the "New" keyword.
Never declare a new object when it is declared.
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.


Collections

Dim oMyCollection As New Collection          ' Do Not Use This  

You should use these 2 lines instead

Dim oMyCollection As Collection 
Set oMyCollection = New Collection
...
Set oMyCollection = Nothing

Dictionaries

Dim dicFSODict As New Scripting.Dictionary   ' Do Not Use This  

You should use these 2 lines instead

Dim dicFSODict As Scripting.Dictionary 
Set dicFSODict = New Scripting.Dictionary
...
Set dicFSODict = Nothing

Classes

Dim oEmployee As New class_Employee          ' Do Not Use This  

You should use these 2 lines instead

Dim oEmployee As class_Employee 
Set oEmployee = New class_Employee
...
Set oEmployee = Nothing

Objects

Dim oMyObject As New MyObject 


Best Practice

Always declare the object on one line and then set the object before you actually need it on another line,
Always remember to set the object to Nothing when you have finished with it.


Checking for Nothing

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

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.


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