Arrays vs. Collections - Differences, pros and contras

Posted on 2010-08-30
Medium Priority
Last Modified: 2012-08-13

Hi Experts.

What are differences between Arrays and Collections in VBA and which are the pros and contras?

Thanks a lot

Question by:Quickstepbr
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
LVL 65

Expert Comment

ID: 33563564
something simple

arrays - most commonly used but cons are it must be dimensioned to a known size and in order to find something you have to iterate thru the array

collections - slower than arrays but quicker to reference and I believe arrays can be multidimensional, collections can not. collections are handy in that they are useful to store objects as opposed to data types like ints and strings. easier to add to collections, slower on arrays as you need to redimension

LVL 65

Expert Comment

ID: 33563567
both are good in their own way so use whatever is right for the job. if you want to hold just some numbers or values etc then arrays would be a better choice.

Author Comment

ID: 33563570
Hi Rockiroads.

Thanks for the tips.
But how can I make my Array be available in other procedures like a public variant variable?

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 50

Expert Comment

by:Dave Brett
ID: 33563582
DId you mean Dictionaries and Array's - that is the normal question?

Dictionaries are quicker and provide more flexibility, see Patrick's excellent Article http://www.experts-exchange.com/blogs/matthewspatrick/B_2752-New-Article-Using-the-Dictionary-Class-in-VBA.html

> But how can I make my Array be available in other procedures like a  public variant variable?

You can use Public Dictionaries, just as you can use Public variant arrays

LVL 19

Expert Comment

ID: 33563586


1) You must specify size of array
2) Data access is serial , you should know the location for search or iterate for all items in array.
3) If you want to insert an item in a specified location, you have to move all items after this location
4) If you want an array with unique values , you have to validate data by iterating all values


1) Dynamic , you may add as many items you need without defining the size as arrays.
2) Data access is much faster, you can reach each items without iterating all items in collection
3) You can add items in any place in the collection.
4) You can use collection keywords to insure data uniqueness.

conclusion : Prefer using collection over arrys
LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 2000 total points
ID: 33563627
Arrays are generally faster than collections. Accessing an array in a For/Next loop is about 30% faster than accessing a collection. However, when building an array, it is much more efficient to pre-allocate the array prior to filling it versus redimensioning it with each new element. For example, creating a 1,000 element array by redimensioning it each time a new element is added is about 1.8 times slower than building a collection with 1,000 elements. If the array is predimensioned to the final size before loading then loading the array takes about 30% of the time that loading the collection does.

When building an array with an unknown number of elements try to allocate a maximum and then redimension it to a smaller size when finished. Alternatively, increase the array size by blocks versus by a single element. Incrementing in blocks of 10 achieves about the same efficiency as a collection. Incrementing in blocks of 100 takes about 50% of the time that a collection does.

If the array or collection will be used to randomly check whether or not elements exist then a collection is vastly superior to an array. With 100 elements, checking if random keys exists takes about 30% of the time that an array of strings takes, and about 10% of the time than an array of variants takes. With 1,000 elements a collection takes about 6% of the time an array takes.

You might also consider the scripting dictionary. The scripting dictionary has a couple of significant advantages over collections: it is faster for numbers of elements less than 40 or 50 thousand and it allows for testing whether or not a key exists. The scripting dictionary can store any type of variable or object but it is best suited for storing objects that need to be accessed with keys. Arrays are a much more efficient method for storing simple variables.

To use the scripting dictionary a reference must be established to the "Microsoft Scripting Runtime" library unless late binding is used (see below). If compiling a VB application the installer will need to include the file SCRRUN.DLL. The sample code below illustrates how to define, initialize, load, access, and clear a scripting dictionary using objects.

   Dim Dictionary As Scripting.Dictionary
   Dim Index As Long
   Dim ClassObject As clsClass

   ' Initialize the dictionary
   Set Dictionary = New Scripting.Dictionary

   ' Load the dictionary with objects using an index value for a key
   For Index = 1 To 100
      Set ClassObject = New clsClass
      Dictionary.Add CStr(Index), ClassObject
   Next Index

   ' Read a specific element
   Set ClassObject = Dictionary("23")
   ' Read each object - this requires an intermediate variant because the default type
   ' of a dictionary's item is not compatible with an object of a specific type
   Dim ElementObject As Variant
   For Each ElementObject In Dictionary.Items
      Set ClassObject = ElementObject
   Next ElementObject

   ' Test if a key exists
   If Dictionary.Exists("23") Then
      MsgBox "Key 23 exists"
   End If

   ' Set the compare mode to TextCompare
   Dictionary.CompareMode = TextCompare

   ' Change the key of an element
   Dictionary.Key("23") = "ABC"

   ' Extract items into an array
   Dim DictionaryItems As Variant
   DictionaryItems = Dictionary.Items

   ' Extract keys into an array
   Dim DictionaryKeys As Variant
   DictionaryKeys = Dictionary.Keys

   ' List all key value pairs
   Dim Key As Variant
   For Each Key In Dictionary
      Debug.Print "Key=" & Key & " Value=" & Dictionary.Item(Key)
   Next Key

   ' Clear an element from the dictionary

   ' Clear the entire dictionary

   ' Use late binding (no reference to the "Microsoft Scripting Runtime" library is required)
   Dim Dictionary As Object
   Set Dictionary = CreateObject("Scripting.Dictionary")

LVL 65

Expert Comment

ID: 33563667
>> But how can I make my Array be available in other procedures like a public variant variable?

well you more or less on the right lines

define as global but define the type you want eg to hold a list of longs

Public MyGlobalArray(1000) As Long

What is the purpose of your use?


Author Comment

ID: 33563696
Basically I Want my script to do following:

- Get the client number in a column
- Search for that number on another sheet, get a specific sheet name for that client
- go to that specific sheet
- copy data from a previously opened workbook to this sheet based on client and date

We are talking about 10-50 elements in an array, collection or dictionary, that will be used trigger my search, copy and paste procedures
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 33563707
Well then, I would not consider speed relevant to your decision. In fact, I recommend not using any of the above and just working directly with the cells on the worksheet. There will be no perceptible difference in speed and the array/collection/dictionary will only be more work.


Author Comment

ID: 33563711
Hi Kevin.

I lost you in your very good answer.
I only use VBA.
Do I need this SCRRUN.DLL? If yes, how do I do this?
What is clsClass?

LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 33563726
Forget all that I said about arrays/collections/dictionaries. You don't need them. To pull the values from a range of 10 to 15 cells:

For Each Cell In MySheet.[A1:A10]
Next Cell

To lookup the cell value in table:

Row = Application.Match(Cell.Value, OtherSheet.[A1:A100], 0)

To pull the client name:

MsgBox OtherSheet.[B1:B100].Rows(Row).Value

And there you go. Easy.


Author Comment

ID: 33563792
Ok. Got the idea.

Total new perspective here.
Have to reorganize my thinking totally.

Do not know what this line exactly does : Row = Application.Match(Cell.Value, OtherSheet.[A1:A100], 0)

Couple of questions:

Regarding: For Each Cell In MySheet.[A1:A10]
Next Cell

Can I exchange the [A1:a10] using a range that I got before?

LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 33563889
>Can I exchange the [A1:a10] using a range that I got before?


>Do not know what this line exactly does : Row = Application.Match(Cell.Value, OtherSheet.[A1:A100], 0)

I'm leveraging a worksheet function, MATCH, in VBA. You can use most of the worksheet functions by qualifying them with the Application object. In the sample I am matching the value in Cell to the list of values in A1:A100 on tab "OtherSheet". Row is set to the index of the matched value in that range.


Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question