Quickstepbr
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
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.
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> 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?
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?
ASKER
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
- 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/dictionar y will only be more work.
Kevin
Kevin
ASKER
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,
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/diction aries. 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.Val ue, OtherSheet.[A1:A100], 0)
To pull the client name:
MsgBox OtherSheet.[B1:B100].Rows( Row).Value
And there you go. Easy.
Kevin
For Each Cell In MySheet.[A1:A10]
Next Cell
To lookup the cell value in table:
Row = Application.Match(Cell.Val
To pull the client name:
MsgBox OtherSheet.[B1:B100].Rows(
And there you go. Easy.
Kevin
ASKER
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.Val ue, 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?
Total new perspective here.
Have to reorganize my thinking totally.
Do not know what this line exactly does : Row = Application.Match(Cell.Val
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.Val ue, 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
Yes.
>Do not know what this line exactly does : Row = Application.Match(Cell.Val
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
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