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

"Tom Emmery" <(E-Mail Removed)> wrote in message
news:318CF546-F64C-4D1F-878E-(E-Mail Removed)...
> 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) ?
>
>

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)

"Tom Emmery" <(E-Mail Removed)> wrote in message
news:318CF546-F64C-4D1F-878E-(E-Mail Removed)...
> 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) ?
>
>

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" wrote:

> 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
>
> "Tom Emmery" <(E-Mail Removed)> wrote in message
> news:318CF546-F64C-4D1F-878E-(E-Mail Removed)...
> > 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) ?
> >
> >
>
>

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

"Tom Emmery" <(E-Mail Removed)> wrote in message
news:3C7FECF8-E3A2-4C4D-B6EF-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>> "Tom Emmery" <(E-Mail Removed)> wrote in message
>> news:318CF546-F64C-4D1F-878E-(E-Mail Removed)...
>> > 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) ?
>> >
>> >
>>
>>

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



"RB Smissaert" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
> "Tom Emmery" <(E-Mail Removed)> wrote in message
> news:3C7FECF8-E3A2-4C4D-B6EF-(E-Mail Removed)...
>> 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" wrote:
>>
>>> 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
>>>
>>> "Tom Emmery" <(E-Mail Removed)> wrote in message
>>> news:318CF546-F64C-4D1F-878E-(E-Mail Removed)...
>>> > 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) ?
>>> >
>>> >
>>>
>>>
>

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Tom Emmery" <(E-Mail Removed)> wrote in message
news:318CF546-F64C-4D1F-878E-(E-Mail Removed)...
> 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) ?
>
>

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

Hi Rick,

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

"Rick Rothstein (MVP - VB)" wrote:

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

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Tom Emmery" <(E-Mail Removed)> wrote in message
> news:318CF546-F64C-4D1F-878E-(E-Mail Removed)...
>> 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) ?
>>
>>
>
>