initializing a multi-dimensional array

SAnna

New Member
Joined
Mar 7, 2014
Messages
10
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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).
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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}")
 
Upvote 0
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,

Yes, I had forgotten about "Evaluate"... very good.


@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:
Upvote 0
I like SAnna's suggestion best -- a worksheet is the perfect place to store random data.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top