Collection vs. Array of Arrays, nszim

[Neal Zimm]

I am mostly self taught in vba, but with other programming experience.
In my app, I have a need to store character and integer variables in arrays.
To date, since the numbers are small I dim an example array as:
dim Array(10,6) as string
When I 'read' from the array I convert the 'string' numbers into integer
vars, and vice-versa when I put values into the array. It's getting to be a
pain to do this.
In reading MSoft docum in vba, I guess I could make an integer array and
a string array and 'combine' them into a holding array for transport in
called subs. e.g.
dim IntArray(x,y) as integer
dim StringAry(a,b) as string
dim master(2)
master(1)=intarray
master(2)=stringary
....
then, call mySub(inputstuff,master)
where mysub operates on the elements of master individually.

I've read a little bit about collections and that function appears to do
about the same thing as the above.
1. WHAT ARE THE BIG PRO'S AND CON'S OF COLLECTIONS VS. AN ARRAY OF
ARRAYS?

2. I've been looking for, in the "collections arena" of Excel docum. how
to
"directly" address the elements of an array that is part of a collection. I
have not found any and indeed do not know it it's even possible. In other
words,
if the collection is composed of these two arrays: and the collection is
named:
COLL,
IntArray(x,y) as integer (item 1 in COLL)
StringAry(a,b) as integer (item 2 in COLL)
IS THERE A WAY TO ADDRESS IntArray(3,4) by using the name of the
Collection? Kinda like: NewVar = COLL(1, ????) to try and grab the value
in IntArray(3,4) without using the IntArray 'name'?

Thanks.
Neal Z

 

[JDB]

I use an array as variant -seems to work good.

Dim Array(x,y) as variant

I then load mixed cargo in that - strings and integers

good Luck

 

[Gregg Roberts]

Can you use the variant data type? It allows you to refer to data as either
integer or string without having to explicitly convert it.

Gregg Roberts

 

[Jim Cone]

Neal,
re: how to directly address...
'-------------------------------------
Sub TestCollectionAccess()
Dim col As VBA.Collection
Dim intArray() As Integer
Dim strarray() As String
Dim x As Long
Dim y As Long
Dim z As Variant
Dim zz As Variant

ReDim intArray(1 To 5, 1 To 5)
ReDim strarray(1 To 5, 1 To 5)

'Load the integer array
For x = 1 To 5
For y = 1 To 5
intArray(x, y) = x * y
Next
Next

'Get value
z = intArray(3, 4)

'Load the collection
Set col = New Collection
col.Add intArray
col.Add strarray

'Get value
zz = col(1)(3, 4)

MsgBox z & " and " & zz & " should be the same "
Set col = Nothing
End Sub
'-----------------------------
Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




I am mostly self taught in vba, but with other programming experience.
In my app, I have a need to store character and integer variables in arrays.
To date, since the numbers are small I dim an example array as:
dim Array(10,6) as string
When I 'read' from the array I convert the 'string' numbers into integer
vars, and vice-versa when I put values into the array. It's getting to be a
pain to do this.
In reading MSoft docum in vba, I guess I could make an integer array and
a string array and 'combine' them into a holding array for transport in
called subs. e.g.
dim IntArray(x,y) as integer
dim StringAry(a,b) as string
dim master(2)
master(1)=intarray
master(2)=stringary
....
then, call mySub(inputstuff,master)
where mysub operates on the elements of master individually.
I've read a little bit about collections and that function appears to do
about the same thing as the above.
1. WHAT ARE THE BIG PRO'S AND CON'S OF COLLECTIONS VS. AN ARRAY OF
ARRAYS?

2. I've been looking for, in the "collections arena" of Excel docum. how
to
"directly" address the elements of an array that is part of a collection. I
have not found any and indeed do not know it it's even possible. In other
words,
if the collection is composed of these two arrays: and the collection is
named:
COLL,
IntArray(x,y) as integer (item 1 in COLL)
StringAry(a,b) as integer (item 2 in COLL)
IS THERE A WAY TO ADDRESS IntArray(3,4) by using the name of the
Collection? Kinda like: NewVar = COLL(1, ????) to try and grab the value
in IntArray(3,4) without using the IntArray 'name'?
Thanks.
Neal Z

 

[keepITcool]

if you need to store and manipulate data, i'd stick with arrays.
as a first thought i would NOT go with your array of arrays approach,
but use a simple 2 dimensional variant array.
(variants are fractionally slower than strongly typed arrays,
but can hold all data types)

You'd use a collection if you need to repeatedly READ items from a
datasource, and need indexed access. Changing data in a collection is
much slower and trickier than changing data in an array.

in your example using a collection to hold 2 arrays is counter
productive, why not simply use

call mysub(inputstuff,intArray,stringAry) ???

(i'd use more structured naming but that's a different matter




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Neal Zimm wrote :

 

[keepITcool]

Jim,
as explained in my post the drawback to using a collection
is that you can't (directly) change the data once inside the collection.

Alternative is a user defined type....

Option Explicit

Type MyType
IntArray() As Integer
StrArray() As String
End Type

Sub Test()
Dim i%
'UserType
Dim uTest As MyType
With uTest
ReDim .IntArray(5)
ReDim .StrArray(10)
For i = LBound(.IntArray) To UBound(.IntArray)
.IntArray(i) = i
Next
For i = LBound(.StrArray) To UBound(.StrArray)
.StrArray(i) = i
Next
End With
Call ModifyType(uTest)

'Collection
Dim col As Collection
Set col = New Collection
col.Add uTest.IntArray, "int"
col.Add uTest.StrArray, "str"

Call ModifyCol(col)

End Sub


Sub ModifyType(uIS As MyType)
'Can change data in the defined type's array
uIS.IntArray(3) = 999
Debug.Print uIS.IntArray(3); "<< s/b 999"
End Sub

Sub ModifyCol(col As Collection)
'Cant change data inside a collection..
col("int")(3) = 111
Debug.Print col("int")(3); "<< s/b 111 but isnt"
End Sub

 

[Dana DeLouis]

...the drawback to using a collection

is that you can't (directly) change the data

Just an idea. For more complicated tasks, I like to use a Dictionary.
Here's a small demo. Not sure if this is what the Op wants though.
Note that the arrays are zero based.

Sub Demo()
'// Dana DeLouis
Dim d, t
Set d = CreateObject("Scripting.Dictionary")

d.Add 1, Array(Array(11, 12, 13), Array("Alpha", "Beta", "Charlie"))
d.Add 2, Array(Array(21, 22, 23), Array("Delta", "Echo", "Foxtrot"))

'// Change "Charlie" to "Zulu"
t = d(1)
t(1)(2) = "Zulu"
d.Item(1) = t

'// To check...
t = d(1)
End Sub

 

[keepITcool]

yep.

I'm a big fan of dictionaries.
(I've recently build a proc that stores (lots of)
range objects in them...)

I think OP needs neither. A typed variable will suit
him much better.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Dana DeLouis wrote :

 

[Neal Zimm]

I can use a variant, but the docum says that's slower, but I've not had
experience to know how much slower. Others posts for this question indicates
it's not that much slower. I'll try it out. I've got some pretty large macros
and am concerned about the extra bytes that variant data takes up. Thanks for
the response,
Neal

 

[Neal Zimm]

Thanks, JDB, pls see reponse to 'Gregg' above. I'll do some testing to see if
there's any appreciable degradation in processing times with variant data.
Neal

 

[Neal Zimm]

Dear Jim,
Thanks SO MUCH for the completeness of your response. I'll try it out
later today.
It looks to be just what I need,
Again thanks,
Neal

 

[Neal Zimm]

Dean Cool,
Thanks for the response. I can use your approach since I have some
arrays that are 'read only' in what I'm developing. I'll try it out.
Thanks again,
Neal

 

[Neal Zimm]

Dear Dana,
Thanks so much for the response. I'll try it later today since I do the
need to access some 'read only' data in the app I'm developing. In the cases
where the arrays I use are both input and output I'll see how much slower
using variant data type is.
Again Thanks,
Neal

 

[Neal Zimm]

Hi KIC,
thanks so much for your reply. Since I'm very much in learning mode, I
asked the general question. I can use a collection for some data that is
'read only' in the appl. I'm developing. The original mistake that I made
was that I got tired of typing is so many var names when I call a procedure:
call mysub (var1, var2, etc......)
so I declared a lot of variables and arrays and then found out this was not
a great idea.
I use a LOT of called procs in my macros and what was looking for the
best way to minimize the re-writing I have to do to get rid of all the
variables in the declarations section that should not be there.
I know that I can use this approach:
call mysub(inputstuff,intArray,stringAry)
and am analyzing what will be best for the work I have to do.
Thanks again for your response,
Neal

 

[vba.NetPlease]

Hi Neal,

I use variant arrays to store minute by minute failure data for steam
nominations over an entire month. I tried using 2 arrays and then combining
them and the total time to process 44700 loops, 5 times was 16 seconds.

The variant array takes 16 seconds. I guess it depends on what manipulation
your doing but heres mine:

MNomarray(9, i) = RampAdjustNom(MNomarray(4, i), pFinalSteamNom,
pRampAdjustNom, pRampUpMin, pRampDownMin)
MNomarray(7, i) = RampUPMin(MNomarray(4, i), MNomarray(9, i),
pRampUpMin)
MNomarray(8, i) = RampDownMin(MNomarray(4, i), MNomarray(9, i),
pRampDownMin)
MNomarray(5, i) = LowerVariance(MNomarray(4, i), MNomarray(9, i))
MNomarray(6, i) = UpperVariance(MNomarray(4, i), MNomarray(9, i))

Its nothing that special but its basic number crunching and the variant isnt
any slower from my experience.

The data i pull comes from the getrows function of a recordset.

Mike