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.


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


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


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


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