# initializing a multi-dimensional array

#### SAnna

##### New Member
What is the correct VBA syntex for the following array (which is from some Matlab code)....

L3_terms =[289.0 5.844 6283.076
35 0 0
17 5.49 12566.15
3 5.2 155.42
1 4.72 3.52
1 5.3 18849.23
1 5.97 242.73];

This doesn't seem to work...
Dim L3_terms()
ReDim L3_terms(7,3)
L3_terms = {{289.0, 5.844, 6283.076}, {35, 0, 0}, {17, 5.49, 12566.15}, {3, 5.2, 155.42}, {1, 4.72, 3.52}, {1, 5.3, 18849.23}, {1 5.97 242.73}}

Also, is there a continuation line that can also be used to help so the code reads better?

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
There is no shortcut method to make that assignment to a normal 2-D array, but you can do this (the space/underline at the end of a code line is how you do line continuations in VB)...

Code:
``````L3_terms = Array(Array(289#, 5.844, 6283.076), _
Array(35, 0, 0), _
Array(17, 5.49, 12566.15), _
Array(3, 5.2, 155.42), _
Array(1, 4.72, 3.52), _
Array(1, 5.3, 18849.23), _
Array(1, 5.97, 242.73))``````

However, the syntax to reference of the array elements is a little "odd". Remember, the LowerBound is 0 (unless you specify Option Base 1), so, here it what the syntax would look like to reference the 5th record down in the 3rd field over...

Code:
``Value = L3_terms(4)(2)``

Note the element numbers are specified in double parentheses (rather than the normal method of comma delimited element numbers).

If the numbers are coming from somewhere (like a .csv file) you could also dump them into an Excel worksheet and then pull them back into a variant array (which is always one based) using a single line. So, for example - if you put those values into range A1 to C7 you could then;

Code:
``````Dim myArray as variant
myArray = Range("A1:C7").Value``````

Thank you. Makes sense. Kinda ugly though. Looks like it would be easier to have the array values in a worksheet and grab them from there. Or I could use the brute force method.....
L3_terms(1,1) = 289
L3_terms(1,2) = 5.844
L3_terms(1,3) = 6283.076
etc

I'm puzzled over your bolding of the word normal (..."normal 2-D array"...). Is there some other kind of array I could use that would be easier to initialize/work with?

Thank you. Makes sense. Kinda ugly though. Looks like it would be easier to have the array values in a worksheet and grab them from there. Or I could use the brute force method.....
L3_terms(1,1) = 289
L3_terms(1,2) = 5.844
L3_terms(1,3) = 6283.076
etc

I'm puzzled over your bolding of the word normal (..."normal 2-D array"...). Is there some other kind of array I could use that would be easier to initialize/work with?
The brute force arrays you posted above would be "normal" as it is a single 2D array of values... the technique I posted is actually a 1D array whose elements are themselves 1D arrays which can be addressed like it was a 2D array so long as you use the "double element" referencing syntax I showed.

Another way:

Code:
``````Sub SA()
Dim av As Variant

av = [{289.0, 5.844, 6283.076;  35, 0, 0; 17, 5.49, 12566.15; 3, 5.2, 155.42; 1, 4.72, 3.52; 1, 5.3, 18849.23; 1, 5.97, 242.73}]

Debug.Print av(1, 1)
Debug.Print av(7, 3)
End Sub``````

Or, with some white space,

Code:
``````  av = Evaluate("{289.0, 5.844, 6283.076; " & _
"35, 0, 0; " & _
"17, 5.49, 12566.15; " & _
"3, 5.2, 155.42; " & _
"1, 4.72, 3.52; " & _
"1, 5.3, 18849.23; " & _
"1, 5.97, 242.73}")``````

Another way:

Code:
``````Sub SA()
Dim av As Variant

av = [{289.0, 5.844, 6283.076;  35, 0, 0; 17, 5.49, 12566.15; 3, 5.2, 155.42; 1, 4.72, 3.52; 1, 5.3, 18849.23; 1, 5.97, 242.73}]

Debug.Print av(1, 1)
Debug.Print av(7, 3)
End Sub``````
@shg,

@SAnna,

For syntax reasons, you cannot use Line Continuation on the code line shg posted (it is actually a single line of code that this forum's reply window word-wrapped); however, you can shorten it by removing the spaces if you like.

Edit Note: shg posted the expanded Evaluate syntax showing the way you could use Line Continuations for it.

Last edited:
I like SAnna's suggestion best -- a worksheet is the perfect place to store random data.

Thanks all. Very helpful. I opted to place my data in a worksheet and pull them into VBA arrays. I would rather have had the arrays already in VBA and not on a worksheet (they don't change; and data on worksheets gives others an opportunity to mess with or delete (granted I can lock or hide the worksheet)). I just wish VBA had a slick way of initializing an array like Matlab does.

Replies
5
Views
483
Replies
3
Views
269
Replies
1
Views
492
Replies
3
Views
1K
Replies
7
Views
404

1,196,079
Messages
6,013,309
Members
441,760
Latest member
Sharina

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back