Initializing Multidimensional Array at Once

[kpark91]

Hello, MrExcel!

One simple questions about VBA arrays.
Is there a way I can initialize all the elements inside an array at once, specifically multidimensional array?

For example, in C/C++

int c[2][5] =
{
{1,2,3,4,5},
{2,3,4,5,6},
};

What about in VBA??

Dim arr(1 To 10, 1 To 3) As Integer

All I know for initializing a VBA array is by setting value for every single element separately.
I.e:

arr(1,1) = 256
arr(1,2) = 244
arr(1,3) = 8

Is there a short way of doing this?
Otherwise, I will need 30 lines;;; which is... bad;;

Thank you in advance!
kpark

 

[kpark91]

Found the solution:
http://www.mrexcel.com/forum/showthread.php?18225-Initializing-Arrays-with-a-single-statement

Too bad, the variable needs to be first declared as a variant.. I don't like that datatype altho it can be convenient at times.

 

[Rick Rothstein]

Found the solution:
http://www.mrexcel.com/forum/showthread.php?18225-Initializing-Arrays-with-a-single-statement

Too bad, the variable needs to be first declared as a variant.. I don't like that datatype altho it can be convenient at times.

Well, the array could be declared as String and populated the way I showed in my posting to that thread... VB will be happy to coerce your "text numbers" to real numbers when you involve the members of the array in calculations.

 

[Norie]

kpark

I don't see a post for populating a multi-dimensional array in that thread.

Can you post the method you are using?

 

[shg]

    Dim av As Variant
    
    av = [{1,2,3;4,5,6;7,8,9}]

 

[kpark91]

Well, the array could be declared as String and populated the way I showed in my posting to that thread... VB will be happy to coerce your "text numbers" to real numbers when you involve the members of the array in calculations.

Variant magic never ceases to amuse me
Thank you!

kpark

I don't see a post for populating a multi-dimensional array in that thread.

Can you post the method you are using?

I just used combination of Array() and then used it like arr(i)(j) to look into the values.

    Dim av As Variant
     
     av = [{1,2,3;4,5,6;7,8,9}]

!!!!!! Another black variant magic. Nice to know! Will use this method
Thank you!

 

[shg]

Not so magic; it works the same as if you selected a 3x3 array of cells and array-entered ={1,2,3;4,5,6;7,8,9}

The brackets are just a shortcut to the Evaluate function.

 

[Ad Huikeshoven]

c=Array( _
    Array(1,2,3,4,5), _
    Array(2,3,4,5,6))

 

[Rick Rothstein]

c=Array( _
    Array(1,2,3,4,5), _
    Array(2,3,4,5,6))

You have posted a response to a nearly 6 year old question. What I cannot figure out is if you are trying to offer a solution with the code you posted or if you have a question related to the topic of this thread and forgot to include a worded description of your question.

 

[Ad Huikeshoven]

You have posted a response to a nearly 6 year old question. What I cannot figure out is if you are trying to offer a solution with the code you posted or if you have a question related to the topic of this thread and forgot to include a worded description of your question.

My response in an answer (to a six year old) question: how to initialize a multidimensional array at once, with a data set used in the original question.