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

by

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

More Related Contents:

  1. Why Use Integer Instead of Long?
  2. Loop through files in a folder using VBA?
  3. How to error handle 1004 Error with WorksheetFunction.VLookup?
  4. How can Excel VBA open file using default application
  5. How to compare two entire rows in a sheet
  6. Run same excel macro on multiple excel files
  7. VBA : save a file with UTF-8 without BOM
  8. Excel tab sheet names vs. Visual Basic sheet names
  9. IsDate function returns unexpected results
  10. Saving a Excel File into .txt format without quotes
  11. Excel VBA “Method ‘Document’ of object ‘IWebBrowser2’ failed”
  12. VBA – Range to jpg picture
  13. How to add data validation to a cell using VBA
  14. Getting AD Details based on username
  15. Difference in type between using and not using Set keyword
  16. How to do late binding in VBA?
  17. Environ Function code samples for VBA
  18. What is the best way to access a serial port from VBA?
  19. How to loop through all controls in a form, including controls in a subform – Access 2007
  20. How to copy a chart from Excel to PowerPoint?
  21. vba paste not working
  22. Continue For loop
  23. mac excel vba loop : from list & then export as pdf
  24. Can Advanced Filter criteria be in the VBA rather than a range?
  25. How to add an embedded image to an HTML message in Outlook 2010
  26. Speed up multiple replacement
  27. In Excel VBA, what is the way to check if the web page is fully loaded?
  28. Excel VBA: auto click and open file from website
  29. How to have vba execute every 10 minutes?
  30. Read and write from/to registry in VBA

Leave a Comment