Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5568
  • Last Modified:

Arrays vs. Collections - Differences, pros and contras

Hi Experts.

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

Thanks a lot

  • 4
  • 4
  • 3
  • +2
1 Solution
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

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.
QuickstepbrAuthor Commented:
Hi Rockiroads.

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

DId you mean Dictionaries and Array's - that is the normal question?

Dictionaries are quicker and provide more flexibility, see Patrick's excellent Article

> 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



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
zorvek (Kevin Jones)ConsultantCommented:
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")

>> 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?

QuickstepbrAuthor Commented:
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
zorvek (Kevin Jones)ConsultantCommented:
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.

QuickstepbrAuthor Commented:
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?

zorvek (Kevin Jones)ConsultantCommented:
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.

QuickstepbrAuthor Commented:
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?

zorvek (Kevin Jones)ConsultantCommented:
>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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now