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

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

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

"JDB" wrote:

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

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

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


"Gregg Roberts" wrote:

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

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




"Neal Zimm" <Neal wrote in message...
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

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

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

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"keepITcool" wrote in message
.com...

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

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 :

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

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


"Dana DeLouis" wrote:

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

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"keepITcool" wrote in message
.com...

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

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


"keepITcool" wrote:


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

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


"Jim Cone" wrote:

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




"Neal Zimm" <Neal wrote in message...
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

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 :

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

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


"keepITcool" wrote:


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 :

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