2 dimensional array (variant)

[Guest]

How to create a 2-dimensional array, initialize it and read from it ?

Example:
fixed number of rows(3), variable number of values per row (max 6)

I've tried the following code:
dim MyArray as variant
redim MyArray(1 to 3, 1 to 7)

How to assign values to it ?
row 1: (1,"h", 2)
row 2: (4,"i",5,"j",6,"k",1)
row 3: (7,"z",3,"y",3)

Then how to select "j" (row 2, index 4) ?

 

[RB Smissaert]

Dim MyArray(1 To 3, 1 To 7)

MyArray(1, 1) = 1
MyArray(1, 2) = "h"
MyArray(1, 3) = 2
MyArray(2, 4) = 5

Msgbox MyArray(2, 4)


RBS

 

[Chip Pearson]

Try something like


Dim Arr() As Variant ' or As Long or As Double
Dim RNdx As Long
Dim CNdx As Long

ReDim Arr(1 To 3, 1 To 6)
' load the array
For RNdx = LBound(Arr, 1) To UBound(Arr, 1)
For CNdx = LBound(Arr, 2) To UBound(Arr, 2)
Arr(RNdx, CNdx) = CNdx * RNdx ' some value
Next CNdx
Next RNdx
' read the array
For RNdx = LBound(Arr, 1) To UBound(Arr, 1)
For CNdx = LBound(Arr, 2) To UBound(Arr, 2)
Debug.Print Arr(RNdx, CNdx)
Next CNdx
Next RNdx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Is it possible to load the array in a smart way, like:
MyArray = array(1,"h", 2), array(4,"i",5,"j",6,"k",1), array(7,"z",3,"y",3)

Would this work ?

 

[RB Smissaert]

That doesn't look that smart to me as how are you loading the arrays that
you call array?
What are you trying to achieve or what particular problem are you trying
to solve?

RBS

 

[Rick Rothstein \(MVP - VB\)]

There is a method to do what the OP asked, but to address the "array" that
is produced requires the use of an odd syntax. Consider this...

Dim VariantArray As Variant
VariantArray = Array(Array(1, "h", 2), _
Array(4, "i", 5, "j", 6, "k", 1), _
Array(7, "z", 3, "y", 3))

Each internal Array function call defines a row in the "master" array; there
are variable number of columns in each row. Now, to address these, you would
use a double set of parentheses system to specify the index values. For
example, if you wanted to retrieve the "k" value above which is at "column"
6 of "row" 2; then, assuming the default Option Base of 0 (meaning
zero-based arrays), this is how you would do it...

Debug.Print VariantArray(1)(5)

To get the "y" from the 3rd row, 4th column, you would do this...

Debug.Print VariantArray(2)(3)

again, assuming zero-based arrays. Now, because each row has a different
upper bound, this is how you would query the array for the upper bound of
the array making up the 2nd row (again, assuming zero-based arrays)....

Debug.Print UBound(VariantArray(1))

That's it... looks screwy, but it does work.

Rick

 

[Jon Peltier]

An alternative option. Put the array into a worksheet, say range A1:G3 of
Sheet2. Use this to populate the array:

Dim myArray As Variant
myArray = Worksheets("Sheet2").Range("A1:G3").Value

My array will be a 1-based array with the dimensions of the referenced
range, that is, MyArray(1 to 3, 1 to 7)

- Jon

 

[Rick Rothstein \(MVP - VB\)]

...... Now, because each row has a different upper bound, this is

how you would query the array for the upper bound of the array
making up the 2nd row (again, assuming zero-based arrays)....

Debug.Print UBound(VariantArray(1))

I guess I should have mentioned that since the lower bound is dependent on
the Option Base setting (hence it can be either 0 or 1), you can also test
for the lower bound using similar syntax...

Debug.Print LBound(VariantArray(1))

Here, for eat lower bound, testing any one array is sufficient... the lower
bound will be the same for **all** arrays within the program... it is only
the upper bound that can vary (because each "array row" can have a different
number of "array columns").

Rick

 

[Guest]

Hi Rick,

Thx. for your clear answers. Figured out another 'direct' method:
MyArray=Evaluate("{1,2;a,b,c,d;6,7,8}")

 

[Jon Peltier]

Stupid spell checker. I didn't mean "My array will be ...", I meant "myArray
will be ...".

- Jon