Solved

Arrays vs. Collections - Differences, pros and contras

Posted on 2010-08-30
13
2,516 Views
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

0
Comment
Question by:Quickstepbr
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 65

Expert Comment

by:rockiroads
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

0
 
LVL 65

Expert Comment

by:rockiroads
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.
0
 

Author Comment

by:Quickstepbr
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?

Quickstepbr
0
 
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

Dave
0
 
LVL 19

Expert Comment

by:elimesika
ID: 33563586
HI

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

Collection:

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
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 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
   Dictionary.Remove("23")

   ' Clear the entire dictionary
   Dictionary.RemoveAll

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

Kevin
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 65

Expert Comment

by:rockiroads
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?

0
 

Author Comment

by:Quickstepbr
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
0
 
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.

Kevin
0
 

Author Comment

by:Quickstepbr
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?

Thanks,
0
 
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.

Kevin
0
 

Author Comment

by:Quickstepbr
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?


0
 
LVL 81

Expert Comment

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

Yes.

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

Kevin
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now