Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning big 2D arrays
Hi,
Assigning small 2D arrays is easy. This is a 3x6 array: Array_A = [{1, 3, 6, 10, 0, 0; 1, 1.2, 4.5, 6.8, 9.2, 10; 0, 0, 0, 0, 2, 3}] Every line is separated by ";". But when I type in bigger arrays and cut the line with a " _", as I do for all other lines in the code that becomes to long, I get an error message, "Missing end bracket" Can anyone tell me why this doesn't work? How can I assign a 4x49 (or even 4x97) element array? I can assign 1D arrays with 49 elements, but is it possible to build a 2D array out of these 1D arrays? Is that a way to get round the probelm? I have also problem with dimensioning these arrays. When I write Dim Array_A(1 To 4, 1 To 49) As Variant I get an error "Can't assign to an array" when I Assign to the array |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning big 2D arrays
sval = "6,1,5,6;5,1,5,25;4,1,5,43;7,1,6,10;5,3,6,38;" & _
"5,1,7,12;3,1,7,32;5,1,7,50;4,5,9,18;2,1,18,31;3,1 ,21" & _ ",28;5,1,23,12" varr = Evaluate("{" & sval & "}") is a way to use continuations, but I think this method runs out of steam very quickly, it won't handle a 4x49 array. For that I think you would be best to load a worksheet range, and load from there. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Sören_Marodören" wrote in message ... Hi, Assigning small 2D arrays is easy. This is a 3x6 array: Array_A = [{1, 3, 6, 10, 0, 0; 1, 1.2, 4.5, 6.8, 9.2, 10; 0, 0, 0, 0, 2, 3}] Every line is separated by ";". But when I type in bigger arrays and cut the line with a " _", as I do for all other lines in the code that becomes to long, I get an error message, "Missing end bracket" Can anyone tell me why this doesn't work? How can I assign a 4x49 (or even 4x97) element array? I can assign 1D arrays with 49 elements, but is it possible to build a 2D array out of these 1D arrays? Is that a way to get round the probelm? I have also problem with dimensioning these arrays. When I write Dim Array_A(1 To 4, 1 To 49) As Variant I get an error "Can't assign to an array" when I Assign to the array |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning big 2D arrays
"Sören_Marodören" skrev i en
meddelelse ... Hi, Assigning small 2D arrays is easy. This is a 3x6 array: Array_A = [{1, 3, 6, 10, 0, 0; 1, 1.2, 4.5, 6.8, 9.2, 10; 0, 0, 0, 0, 2, 3}] Every line is separated by ";". But when I type in bigger arrays and cut the line with a " _", as I do for all other lines in the code that becomes to long, I get an error message, "Missing end bracket" Can anyone tell me why this doesn't work? How can I assign a 4x49 (or even 4x97) element array? I can assign 1D arrays with 49 elements, but is it possible to build a 2D array out of these 1D arrays? Is that a way to get round the probelm? I have also problem with dimensioning these arrays. When I write Dim Array_A(1 To 4, 1 To 49) As Variant I get an error "Can't assign to an array" when I Assign to the array Hi Sören You can use this approach instead, but there is a limit to the number of linebreaks in a line (which I can't remember, 24???): Array_A = Evaluate("{1, 3, 6, 10," & _ "0, 0; 1, 1.2, 4.5, 6.8, 9.2, 10;" & _ "0, 0, 0, 0, 2, 3}") or something along these lines: Option Base 1 Sub test() Dim Array_A(1 To 3) As Variant Dim Counter As Long Dim Counter1 As Long Dim NewArray(1 To 3, 1 To 6) As Variant Array_A(1) = Array(1, 3, 6, 10, 0, 0) Array_A(2) = Array(1, 1.2, 4.5, 6.8, 9.2, 10) Array_A(3) = Array(0, 0, 0, 0, 2, 3) For Counter = 1 To 3 For Counter1 = 1 To 6 NewArray(Counter, Counter1) = Array_A(Counter)(Counter1) Next Counter1 Next Counter End Sub -- Best regards Leo Heuser Followup to newsgroup only please. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning big 2D arrays
Thanks for the help so far.
No, it can't handle my 4x49 array. The string can be maximum 253 char long. Do you know if/how I can use 4 different 1D arrays (1x49) and assign them one by one to each row in th 4x49 array? I can't load data from the worksheet. I want my data to be hardcoded in the VBA script. Regards, /Sören "Bob Phillips" wrote: sval = "6,1,5,6;5,1,5,25;4,1,5,43;7,1,6,10;5,3,6,38;" & _ "5,1,7,12;3,1,7,32;5,1,7,50;4,5,9,18;2,1,18,31;3,1 ,21" & _ ",28;5,1,23,12" varr = Evaluate("{" & sval & "}") is a way to use continuations, but I think this method runs out of steam very quickly, it won't handle a 4x49 array. For that I think you would be best to load a worksheet range, and load from there. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Sören_Marodören" wrote in message ... Hi, Assigning small 2D arrays is easy. This is a 3x6 array: Array_A = [{1, 3, 6, 10, 0, 0; 1, 1.2, 4.5, 6.8, 9.2, 10; 0, 0, 0, 0, 2, 3}] Every line is separated by ";". But when I type in bigger arrays and cut the line with a " _", as I do for all other lines in the code that becomes to long, I get an error message, "Missing end bracket" Can anyone tell me why this doesn't work? How can I assign a 4x49 (or even 4x97) element array? I can assign 1D arrays with 49 elements, but is it possible to build a 2D array out of these 1D arrays? Is that a way to get round the probelm? I have also problem with dimensioning these arrays. When I write Dim Array_A(1 To 4, 1 To 49) As Variant I get an error "Can't assign to an array" when I Assign to the array |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning big 2D arrays
This worked for me in VBS
Dim arr(3, 3) For k = 0 To 3 Select Case k Case 0 v = Array(11, _ 12, _ 13, _ 14) Case 1 v = Array(21, 22, 23, 24) Case 2 v = Array(31, 32, 33, 34) Case 3 v = Array(41, 42, 43, 44) Case 4 End Select For i = 0 To 3 arr(k, i) = v(i) Next Next MsgBox arr(0, 0) & vbCr & arr(3, 3), , "my VBS array" Regards, Peter T "Sören_Marodören" wrote in message ... Thanks for the help so far. No, it can't handle my 4x49 array. The string can be maximum 253 char long. Do you know if/how I can use 4 different 1D arrays (1x49) and assign them one by one to each row in th 4x49 array? I can't load data from the worksheet. I want my data to be hardcoded in the VBA script. Regards, /Sören "Bob Phillips" wrote: sval = "6,1,5,6;5,1,5,25;4,1,5,43;7,1,6,10;5,3,6,38;" & _ "5,1,7,12;3,1,7,32;5,1,7,50;4,5,9,18;2,1,18,31;3,1 ,21" & _ ",28;5,1,23,12" varr = Evaluate("{" & sval & "}") is a way to use continuations, but I think this method runs out of steam very quickly, it won't handle a 4x49 array. For that I think you would be best to load a worksheet range, and load from there. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Sören_Marodören" wrote in message ... Hi, Assigning small 2D arrays is easy. This is a 3x6 array: Array_A = [{1, 3, 6, 10, 0, 0; 1, 1.2, 4.5, 6.8, 9.2, 10; 0, 0, 0, 0, 2, 3}] Every line is separated by ";". But when I type in bigger arrays and cut the line with a " _", as I do for all other lines in the code that becomes to long, I get an error message, "Missing end bracket" Can anyone tell me why this doesn't work? How can I assign a 4x49 (or even 4x97) element array? I can assign 1D arrays with 49 elements, but is it possible to build a 2D array out of these 1D arrays? Is that a way to get round the probelm? I have also problem with dimensioning these arrays. When I write Dim Array_A(1 To 4, 1 To 49) As Variant I get an error "Can't assign to an array" when I Assign to the array |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning a Value | Excel Discussion (Misc queries) | |||
Working with ranges in arrays... or an introduction to arrays | Excel Programming | |||
Arrays - declaration, adding values to arrays and calculation | Excel Programming | |||
Assigning a value to a name | Excel Worksheet Functions | |||
Assigning range names to arrays | Excel Programming |