Enable Dictionary Object in VBA

Question

When I initiate a dictionary object in Excel VBA, I found out two methods:

1. Use CreateObject("Scripting.Dictionary") (no "Microsoft Scripting Runtime");
2. Turn on the reference "Microsoft Scripting Runtime" first, then use Dim dict As New Scripting.Dictionary

Both of them work on my machine. I am wondering is there any difference between these two methods?

Answer 1

The only difference I know is the first one uses Late bind and the second one uses Early bind. The difference between the two is explained here.

Early bind has advantages. One of which is you can use Intellisense to guide you on the available properties of the bound object that you can use in coding. Also, it is said that it is faster in terms of performance. You can also use built-in constant as is. No need to check it's equivalent value. This is discussed here.

Late bind has advantages as well specially if you bound objects that have different versions. This reduces the risk of runtime errors due to version incompatibility (also mentioned in the 1st link).

As for me, I always use Early bind during development so I can utilize Intellisense. If there is a need to convert to Late bind, I'll do it later after I or the customer have done robust testing.

Additional:

When you use Early bind Don't Use Auto-Instancing Object Variables as discussed by CPearson in his post here. Below is the excerpt from his blog.

For object type variables, it is possible to include the New keyword in the Dim statement. Doing so create what is called an auto-instancing variable. Again, while this may seem convenient, it should be avoided. Contrary to what some programmers may believe, the object isn't created when the variable declaration is processed. Instead, the object is created when it is first encountered in the code. This means that, first, you have limited control when an object is created. Second, it means that you cannot test whether an object is Nothing, a common test within code and a common testing and diagnostic technique.

So a better way to set your variable is:

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary