Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What's the difference between Dim As New vs Dim / Set

Tags:

vba

In VBA, I can create objects one of two ways:

'First way
Dim myCol1 As New Collection

'Second way
Dim myCol2 As Collection
Set myCol2 = New Collection

myCol1.Add "AAA"    'Works
myCol2.Add "BBB"    'Works as well

Is the second way just a more verbose version of the first way, or is there actually a difference between the myCol1 and myCol2 objects?

like image 569
Combinatix Avatar asked Mar 07 '17 19:03

Combinatix


People also ask

What is the difference between dim and set?

Dim is used for declaring the object name and type, "Set" is to assign a value to it.

What is dim as new?

The following declares a variable for a new instance of a worksheet. Dim X As New Worksheet. If the New keyword is not used when declaring an object variable, the variable that refers to the object must be assigned an existing object by using the Set statement before it can be used.

What does it mean to dim as double?

For example, if we wanted to create a variable for a Stock Price, we could write “Dim stockPrice as double”. This creates a variable called the stockPrice, which takes on the data type double. A double data type is one of the data types that allows for decimals, as opposed to the integer data type.

What does set mean in VBA?

VBA Set is a statement which is used to assign any value key says an object or a reference to a variable, we use this function to define the parameter for a certain variable, for example, if we write Set M = A which means now M reference has the same values and attributes similar to what A has.


2 Answers

There are several key differences. You should definitely prefer the second Dim/Set approach.

Reason 1 - With As New, the object doesn't get created until a property or method of that object is called, but look at this example where setting the object to Nothing, and then calling a property/method causes the object re-instantiate itself:

Sub ShortcutInstantiation()

  Dim x As New Collection

  x.Add "FOO", "BAR"
  Set x = Nothing

  'This line implicitly recreates a new Collection
  Debug.Print x.Count

  Debug.Print x Is Nothing 'Prints False

End Sub

Sub SafeInstantiation()

  Dim x As Collection
  Set x = New Collection

  x.Add "FOO", "BAR"
  Set x = Nothing

  'Throws error because x is nothing
  Debug.Print x.Count

End Sub

Reason 2 The As New approach is slower, because VBA needs to check if it has instantiated the object before every single property or method call.

Look at this pseudo code to see what VBA is doing behind the scenes:

Sub NotSoShortcutInstantiation()

  Dim x As New Collection

  If x Is Nothing Then Set x = New Collection
  x.Add "FOO", "BAR"

  If x Is Nothing Then Set x = New Collection
  x.Add "FIZZ", "BUZZ"

  If x Is Nothing Then Set x = New Collection
  x.Add "CAR", "DOOR"

  If x Is Nothing Then Set x = New Collection
  Debug.Print x.Count

End Sub

Reason 3 There can be critical timing differences if you object constructor does something after you expect it to, rather than when you explicitly instantiate it:

Compare the results of this code:

Sub InstantiationTiming()

  Dim foo As String

  Dim x As New Class1
  Debug.Print Format(Now(), "hh:mm:ss") & " x should be ready"
  foo = x.foo

  Dim y As Class1
  Set y = New Class1
  Debug.Print Format(Now(), "hh:mm:ss") & " y should be ready"
  foo = y.foo

End Sub

The As New approach prints:

06:36:57 x should be ready
06:36:57 Class Initialized

The Set y = New approach prints:

06:36:57 Class Initialized
06:36:57 y should be ready
like image 100
ThunderFrame Avatar answered Sep 22 '22 01:09

ThunderFrame


The As New construct has legitimate uses. In a class, with a module level variable when you do not know which method will be called first then it saves some lines of code. So some code snippet I have lying around in a class is given here

Option Explicit

Private mdicQueryStringParams As New Scripting.Dictionary

Function SafeItem(ByVal sKey As String, ByRef pvItem As Variant) As Boolean

    If mdicQueryStringParams.Exists(sKey) Then
        pvItem = mdicQueryStringParams.Item(sKey)
        SafeItem = True
    End If

End Function

Imagine very many more methods that rely upon an initialised mdicQueryStringParams. You'd have to write guard code to ensure that it was created in all of these methods.

Now at this point you're saying but you can use Sub Class_Initialize to New up upon class creation. Like this

Private Sub Class_Initialize()
    Set mdicQueryStringParams = New Scripting.Dictionary
End Sub

But suppose I want to recycle/reset part of the class's state then I could write a Clear method which sets mdicQueryStringParams to Nothing . In this case Sub Class_Initialise won't run again. Here on SO Mat's Mug has taught me that Static classes are possible in VBA (thanks!) so sometimes Sub Class_Initialise will only run once.

(^ Admittedly I could set it to a New instance in the Clear method, yes, yes, I know, I know)

The point is with the As New syntax you get a resurrecting variable as well as auto-initialisation. Surely as developers this is another technique/pattern in our toolbox that we should exploit and not ban.

In truth, I use it infrequently but I just don't like banning stuff.

like image 45
S Meaden Avatar answered Sep 24 '22 01:09

S Meaden