2

I would like to know if using Dictionaries in VBA could bring any drawbacks to my code? Like compatibility issues, since it's not native from VBA

I'm no expert in programming, but I'm used to Python and its dictionaries. Now I'm learning VBA because I need to automate the design (using excel) and drawing (AutoCAD) of some geotechnical structures.

I found they could be used in VBA by setting a reference to MS Scripting runtime. Should I have any concerns about using them? Like compatibility issues in future versions of windows or office?

Teamothy
  • 2,000
  • 3
  • 16
  • 26
cepeda
  • 51
  • 1
  • 4

1 Answers1

2

Your question is very general to I'll try to answer it this way ....

Two things to think about when using any object class in any language as the way you use it can pose drawbacks:

  • Is the object you're using native to that language or is it a general/generic OS object? - In this case the Scripting.Dictionary object is on the OS level and not the VBA/Excel level. That makes it more generic when working across windows versions in your code and more stable as well as faster. The execution of the underlying code is at the OS / complied level and not at the Excel/VBA uncompiled level. Only drawback is when running code on completely different OS like Mac as noted by Rory above.
  • How is the object used and allocated in memory? - People can write books on this but suffice to say how is the object allocated a space in memory. A dictionary does grow in size as you add too it so it does it's increased memory allocation (which can be slower for very large Datasets); however having said that a dictionary is actually better suited to use than a Collection or an Array even though a collection may be faster.
  • Late vs Early Binding - Early binding is where you find at tick the reference to the dll in Tools -> References This allows the Intelli-text of the class objects to show up which is nice and it binds the dll at launch, the drawback is when you move to a different system on the same OS the same version of the dll may not be present and will case your code to break. Late Binding on the other hand is when you create an instance of the object at run-time thought saying set MyDictionary = CreateObject("Scripting.Dictionary") this will find the currently available dll located on the OS as linked in the OS registry, the pro is it's much more compatible across different platforms, however you won't have the Intelli-Text and binding will take place at run-time (hardly drawbacks though).

Some things to note

  • A dictionary stores objects as well as pointers to objects in memory, this means that if you have a class of your own stored in a dictionary and you continue to work on the class (i.e. update parameters values at Run-Time) then the class in the dictionary will change as well unless you start working on a new class.

  • To optimize your dictionary in VBA just remember (it's not necessary but it helps and is good practice) to tell the dictionary how to evaluate the key. i.e. 

Set MyDictionary = New Dictionary
MyDictionary.CompareMode = <TextCompare|BinaryCompare|DatabaseCompare>`

In summary though a dictionary is the best object to use when storing info that may or may not be sorted and that may be undefined in size and type.

Here are some posts that discuss a Dictionaries vs other storage types.

Teamothy
  • 2,000
  • 3
  • 16
  • 26
zatbusch
  • 324
  • 2
  • 10