2 dimensional array (variant)

  • Thread starter Thread starter Guest
  • Start date Start date
G

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) ?
 
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
 
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)
 
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 ?
 
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
 
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
 
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
 
...... 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))
Click to expand...

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
 
Hi Rick,

Thx. for your clear answers. Figured out another 'direct' method:
MyArray=Evaluate("{1,2;a,b,c,d;6,7,8}")
 
Stupid spell checker. I didn't mean "My array will be ...", I meant "myArray
will be ...".

- Jon
 
Back
Top