Link to home
Start Free TrialLog in
Avatar of Quickstepbr
QuickstepbrFlag for Brazil

asked on

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

Avatar of rockiroads
rockiroads
Flag of United States of America image

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.
Avatar of Quickstepbr

ASKER

Hi Rockiroads.

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

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

Dictionaries are quicker and provide more flexibility, see Patrick's excellent Article https://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
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
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>> 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?

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


>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