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