Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 dimensional array (variant)
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) ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 dimensional array (variant)
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" wrote in message ... 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) ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 dimensional array (variant)
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" wrote in message ... 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) ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 dimensional array (variant)
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" wrote in message ... 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) ? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 dimensional array (variant)
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" wrote in message ... 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" wrote in message ... 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) ? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 dimensional array (variant)
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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) ? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 dimensional array (variant)
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" wrote in message ... 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) ? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 dimensional array (variant)
...... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 dimensional array (variant)
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 dimensional array (variant)
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" wrote in message ... 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" wrote in message ... 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) ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
ReDim Object array as parameter of Variant array | Excel Programming | |||
Create One-Dimensional Array from Two-Dimensional Array | Excel Programming | |||
variant array containing cel adresses convert to actual ranges-array | Excel Programming |