Array - Collection - Dictionaries?

[Uncle Gizmo]

Paul Kelly asks:- "Should you use an Array, a Collection or a Dictionary?"

This is an important question.

Because if you can answer it then you are well on your way to becoming an advanced VBA programmer.

There are many resources that explain the different between these 3 data structures. But listing the differences is not really that helpful.

What you want to know is which one is better for your particular task.

In Paul Kelly's latest video below he covers these structures wth real-world examples of how to use them.

 

[Ranman256]

I use collections. (arrays are from middle ages)
collections are keyed and you can just ask for what you want instead of searching: vName = colNames("bob smith")

it also knows how many items it holds.
Collections!!

 

[The_Doc_Man]

Each has their uses. Arrays can be the fastest for VBA usage, but collections give some extra tools. The down side is that collections kind of have to be specific. Dictionaries have their uses as well, but you are limited in what data types you can store in a dictionary.

The trick is not to have each in the tool box, but rather is having them and known when / when not to use them.

 

[Isaac]

I have only used Collections and Dictionaries in ooooold code that I copied and pasted back in my early VBA days, before I adopted a rule for myself of NEVER using code I didn't personally understand every line of it.

That's not to say I am authoritative on which one is "old" versus "new and shiny", just that arrays have always been my tool of choice and seem to work fine.

you can just ask for what you want instead of searching: vName = colNames("bob smith")

This is a good tip, Ranman. And compelling for usage - thanks. I'll have to try them out more next time I have an occasion.

Curious, why do you say Arrays are old? In all honesty, are you saying that more from the perspective of knowledge of multiple other languages, (I suspect), or are you saying Arrays are old-ish even only within the context of VBA? (where....let's face it...has anything ever really changed??)

 

[moke123]

Dictionaries, hands down.

 

[MajP]

but you are limited in what data types you can store in a dictionary

Not true at all

dictionaryobject

Use: Required

Data Type: Dictionary object

A reference to a Dictionary object.

key

Use: Required

Data Type: String

A unique string key for this Dictionary object.

item

Use: Optional

Data Type: Any

The data associated with key.

Property Data Type​

Any.

 

[moke123]

Not true at all

That's what I thought too but I couldn't find a ready reference to back it up. It was my understanding the only thing you couldn't store in a dictionary was an array, but not sure where I saw that.

 

[KitaYama]

This is a comparison posted by @moke123 here .
It helped me a lot to understand their pros and cons.
I'm surprised why he didn't post it here. Thanks to him.

 

[moke123]

I'm surprised why he didn't post it here.

Couldn't find it

 

[The_Doc_Man]

Not true at all

You are correct, MajP - I always confuse Dictionaries and TempVars. It is the latter that has the stronger restriction on data types.

 

[Galaxiom]

Dictionaries provide far superior facilities for management through keys than collections.

Presumably collections use less resources if those key management facilities are not relevant.

The advantage of arrays is they are multidimensional.

 

[moke123]

It was my understanding the only thing you couldn't store in a dictionary was an array, but not sure where I saw that.

Now I know where I saw it. It was in the graphic @KitaYama posted. You cant use an array as a key value which makes sense.

 

[Uncle Gizmo]

Up-and-coming webinar about dictionaries by Paul Kelly...

Follow the link for registration, time and date. I note that there's a button you press which will tell you the webinar time in your time zone! That is impressive. I've never seen that before!

https://register.gotowebinar.com/register/9080640462301380958?__s=24kkred6wvln7oczmkyk

For UK it's:-

Wed, Dec 21, 2022 8:00 PM - 9:30 PM GMT

 

[JonXL]

Why use a collection when there are dictionaries? I haven't been able to see the purpose of the former in a world containing the latter.

Is that like folks who still declare variables as integers?

Or am I missing something?

 

[MajP]

Why use a collection when there are dictionaries? I haven't been able to see the purpose of the former in a world containing the latter.
Is that like folks who still declare variables as integers

You motor home has way more features (toilet, shower, TV, bed) than your economy car, so why ever drive the economy car? You can just zip down to the grocery store in the motor home to get that loaf of bread. Dodge traffic, burn some gas, and find a parking space. Or you can take a week long road trip in the economy car and just sleep in the back seat surrounded by luggage.
Personally I would pick the most efficient, best performing, and easiest to use tool for the job.

Most of the discussion so far has focused only on the different features, but that is only part of the story. Need to look at and weigh the following based on what you are doing.

• ease of use
• efficiency
• performance.

Ease of use. If I do not need to worry about keys than I am not going to pick a data structure that requires me to provide a unique key. If I have to dynamically add and remove items than maybe I would pick a collection over an array.

Speed comes in many forms, and there are vast differences depending on what you are doing:
Loading items into the structure
Retrieving items from the structure
Sorting the structure
Looping the entire structure
Determining if item is in the structure

If I only need to get items into a list and read the whole list or get an item by index then an array is the lightest, fastest most efficient. However if I need to pull an item out by key or dynamically add and remove, that efficiency "may" be lost and the additional overhead of collection may be better. If doing a lot of inserts where need to determine if an item exists then go with a dictionary.

 

[The_Doc_Man]

Why use a collection when there are dictionaries? I haven't been able to see the purpose of the former in a world containing the latter.

Is that like folks who still declare variables as integers?

Or am I missing something?

MajP enumerates many good issues of a practical nature. I think that taking too much of a short-cut in data declaration is just a very dangerous choice and betrays a certain level of either laziness or a lack of understanding of WHY things are done as they are.

Why use a screwdriver when there are hammers? Why use a fly-swatter when there are shotguns? Why use a pry-bar when there are explosives?

Using a variable type as INTEGER (or, for that matter, SINGLE) might be appropriate in a given moment. Optimizing data storage might lead you to choose a particular data type over another. Using a variable type as VARIANT when you use a data type that has "baggage" is dangerous because it is perfectly fine to change a variant from one thing to another - without warning you. If that variant variable you just used had previously been a DOUBLE but now is going to be an INTEGER, you had better hope you initialized it correctly for your new use.

I believe Niklaus Wirth, the "Father" of the PASCAL language, was quoted as saying "80% of all programming errors are due to data errors." (Could have been someone else, but regardless of who said it, the statement IS the truth.) We make a distinction between counting numbers and scientific numbers for a reason. Among other things, the infinities for integers are not the same as the infinities for scientific numbers. VBA just happens to gloss over that difference.

Why use a strong data-typing language when VBA allows something called "Let-coercion" (that automagically converts expressions to the correct data type for the targeted Let-variable)? The whole point of using one thing over another is that it fits the situation better and is less likely to have side effects.

I used to love/hate PL-1/G because in that language, even two different strings declared with different data lengths were considered to be different data types in certain types of expressions, leading to type mis-match errors. I hated all the errors - but when I got a clean compile, I knew I had ironed out a LOT of data conversion issues. That language made you think hard about what you were doing at a mechanical as well as at a logical level. There is method in the madness of strict data typing.

 

[Isaac]

I think it was someone on UA years ago who convinced me there was little use in declaring VBA numeric variables as anything other than Long.
I switched to that and have never regretted it.
This article explains one possible reason why one might do so: https://stackoverflow.com/questions/26717148/integer-vs-long-confusion

The article makes a good point that, despite how it is saved in memory, it is still type checked as the declared type.

But what good does that do when comparing the numeric types? Is the Compiler going to actually tell me I've declared a variable as Byte and assigned it 999999 ? No! It does not do that at all. The compiler is not that cool. Am I going to get any different behavior from Intellisense? I doubt it, not anything I've ever noticed.
So why use Byte?

So why did I switch to Long for everything?

Well, some might call it "laziness" as Doc has done. But doesn't "laziness" fit into Ease of Use?
Part of development is efficiency. If I can't find a tangible benefit to differentiating them, and if the time it takes me to keep them all straight in my mind and sort through those differentiations mentally while developing IS a tangible benefit/burden, then I guess you can call me lazy, because I do do the occasional thing in development only because I see little benefit in memorizing the 8 ways to do it. That principle is something you can go crazy with and overdo (of course), but that doesn't make it totally without merit.

I love strong typing, I feel it engenders strong principles in otherwise lazy developers.
But as MajP says, everything in its place.

When you have to complete 12 huge SQL queries a week and test and return the results, you DO think about things like speed
I can't tell you how many times a month I type Decimal(19,2) not because the precision and scale is perfect for the need, only because I know it will work and there is no good reason to take the time to do further research.

 

[Galaxiom]

Is that like folks who still declare variables as integers?

Integers use integer arithmetic operators. No concern for the vagaries of imprecise floating point arithmetic.

Declaring a counter as anything other than an integer seems a dumb idea to me.

 

[JonXL]

Integers use integer arithmetic operators. No concern for the vagaries of imprecise floating point arithmetic.

Declaring a counter as anything other than an integer seems a dumb idea to me.

See the link in @Isaac's post for why I don't use integer for anything.

 

[The_Doc_Man]

There is a difference here between declaring a variable as INTEGER or LONG and declaring a field as INTEGER or LONG. I would typically agree with letting most counting variables be LONGs. However, if you want specific behavior of numbers then data typing of your variables becomes important. If there is a reason to consider the range of a number, you should "use the right tool for the job" - and variable sizes are one way to assure that. If you want to store something in a record, you know that the record has a specific data layout and you probably should try to match up with that. If all you are doing is counting something for program - but not for storage - purposes, count in a LONG. If you have scientific numbers, work in DOUBLEs all of the time - unless and until you have to store that scientific number in another format.

Is the Compiler going to actually tell me I've declared a variable as Byte and assigned it 999999 ? No! It does not do that at all. The compiler is not that cool.

Very few compilers ARE that cool. In my lifetime I have worked with at least three FORTRAN compilers (IBM and DEC), ALGOL (DEC), BASIC (DEC and Microsoft), P/L-1G (DEC), PASCAL (BORLAND), VBA (MS)... - and not a single one cared about whether my expression was numerically kosher at compile time. They waited until run-time to lower the boom on me if I transgressed.

When I said "Lazy" it was in the sense of "not planning ahead." The issue will always be "Have you thought this through to the point that you know it is SAFE to do it THIS way... or to not care?" In that sense, I do not retract the word "Lazy" (but I wasn't pointing fingers at you, Isaac!)

There are many project management rules that have been around since the beginning of programming - and other kinds of projects that have nothing to do with computers. Chief among them is "plan ahead." Failure to plan ahead is insane and self-limiting. The 6 P's of any project are "Pathetically Poor Planning Prevents Proper Production." (There are other versions of this, some a bit pithier than others.)