Is the poor performance of Excel VBA auto-instancing a myth?

Question

The accepted wisdom is that using a construct like Dim dict As New Dictionary is poorer in performance than Dim dict As Dictionary / Set dict = New Dictionary.

The explanation is that the former example - auto-instantiation - defers instantiation until the first usage of the variable dict. And thus, every time dict is referenced, the compiled code must first check whether dict is equal to Nothing.

But it occurs to me that compiled code does this anyway. You will get an error any time you try to make use of an object reference that is Nothing.

So, in tribute to science, I ran some tests. And the results suggest there is no performance difference between the two approaches. (Run on Excel 2007)

Call "create dictionary & add 2 items" 100,000 times.

• Explicit: 16,891ms / Auto: 16,797ms (Auto 94ms faster)
• Explicit: 16,797ms / Auto: 16,781ms (Auto 16ms faster)

Reverse the order of test calls:

• Auto: 16,766ms / Explicit: 16,812ms (Auto 46ms faster)
• Auto: 16,828ms / Explicit: 16,813ms (Explicit 15ms faster)

Call "create dictionary & add 6 items" 100,000 times.

• Auto: 17,437ms / Explicit: 17,407ms (Explicit 30ms faster)
• Auto: 17,343ms / Explicit: 17,360ms (Auto 17ms faster)

Create dictionary and add 100,000 items.

• Auto: 391ms / Explicit: 391ms (Same)

Create dictionary and add 1,000,000 items.

• Auto: 57,609ms / Explicit: 58,172ms (Auto 563ms faster)
• Explicit: 57,343ms / Auto: 57,422ms (Explicit 79ms faster)

I see nothing to indicate that auto-instantiation is a poor performing relation to explicit instantiation. (To be clear, for other reasons, I would avoid auto-instantiation but I'm just interested in the performance angle here.)

So is this a myth?

UPDATE

Let me lay out why the performance argument doesn't make sense to me. It is said that

x.Add("Key", "Item")

in an auto-instantiated object is equivalent to the following:

If x is Nothing then
    Set x = New Dictionary
End If
x.Add("Key", "Item")

which makes it look like "frightening overhead" if you're calling this thousands of times. But in the explicit instantiation case, it's exactly the form of logic generated in the compiled version of the code:

If x is Nothing Then
    Err.Raise "Object variable not set..."
End If
x.Add("Key", "Item")

It doesn't necessarily follow that auto is longer, which is why I'm asking whether there was any truth to this. I wonder if I've identified another one of the many untested performance myths.

Answer 1

I know there is the holy way over if it's okay or not to dim as new, but I've never heard of it being said to generate poor performance. The short answer is Not Really. Yes it does litter your code with unnessesary checks to see if it's not Nothing, but you wouldn't notice a speed difference thanks to today's machines. It's like saying "for looping over 10000 characters is faster than 10001. To start seeing any difference, you need to be looping your tests in higher terms, like millions and tens of millions.

That being said Dim as New is frowned upon but not for performance reasons.

• You lose the ability to control when it's initialized
• You lose the ability to check if an object is Nothing
• Speed difference or not, it does litter your code with unnessesary checking

Granted if you are just using VBA to automate some worksheet stuff or manipulate data, you probably won't care about these, but the moment you look at more sophisticated code, losing the ability to test if an object is Nothing and not controlling when it's initialized is huge and can generate unexpected behavior, not to mention make testing a pain in the butt. All that for saving a few lines of code.

Then there are the micro-optimizers who will argue that adding anything to your code that is not needed makes for poor performance. While they are right in some ways, you'll most likely saving 0.000000001 seconds in this case.

Answer 2

Reading this a decade later, I then came across the following in a book 14 years older, speaking of what the compiler does with auto-instancing:

You don’t have to write all that extra code to check for Nothing; Visual Basic writes it for you. But you do have to execute it. Why can’t the compiler see that you’ve already created the object variable after the first statement and quit checking? Because it’s a compiler. Consider this statement:

Dim thing as New CThing
If fHellFrozenOver Then thing.Title = "The latest thing"

The compiler can’t tell at compile time whether hell is frozen over. That won’t be known until run time. Thus it won’t know whether to create a new CThing object without checking. Theoretically, you could write an optimizing compiler that would analyze conditionals and eliminate redundant checks in cases where there was only one possible code path. But that compiler couldn’t work on p-code, where every statement has to stand on its own. You’re better off just using the Set statement to create the objects yourself.

Now, before you throw New out of your declarations toolbox, take a look at the results of the Performance sidebar on page 133. The real-world penalty for using New in declarations for compiled code just isn’t worth worrying about. It’s all but free.

That is from Bruce McKinney's Hard Core Visual Basic, second edition, pg. 122. He then notes that auto-instancing can be used to help with memory management, in the sense that you can set a variable to nothing when you're done with it and know it will be recreated later if needed. This is also kind of a twist on the view that references to uninitialized variables should be used only to highlight bugs.

Now, he doesn't directly address the point raised in the question that a logical test would need to be run in either case. Clearly the author agrees with the point that performance is not a significant concern.