Assigning big 2D arrays

[Guest]

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

 

[Bob Phillips]

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)

 

[Leo Heuser]

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

 

[Guest]

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

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)

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

 

[Peter T]

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

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

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)

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