Manual Entry of Row and Column Array Values

[SidewinderAM9M]

In MatLab, I could fill an array using the following syntax My_Array = [1, 2, 3; 4, 5, 6; 7, 8, 9; 8, 7, 6] which would result the 4 x 3 matrix as follows.

|1 2 3|
|4 5 6|
|7 8 9|
|8 7 6|

I cannot seem to find the equivalent column separator (the colon [;] in MatLab) for VBA's array function.

1) Does it exist and if so what is it?

2) How might I create an array this way without using code such as
My_Array(1,1)=1
My_Array(2,1)=4
My_array(3,1)=7
My_array(4,1)=8

I would prefer not to use a loop as the values I need to enter are often text nor would prefer (in some cases) to enter the values into a spreadsheet and use a loop to fill the array. Obviously, if that is the only way then that is what I will have to do, but I would be much appreciative if someone could tell me how to do it otherwise.

Thanks in advance.

[kalak]

Excel/VBA isn't very well set up to handle arrays that way. At least not up to version 2007.

You can use jack-ups on the lines of the code below (just run it and see) to get the sort of result you seem to want. But obviously not very satisfactory.

It is very easily done using loops. These work fine with text, which is no obstacle whatever to their use.

Sub jacked_up_array()

Const a As String = "1, 2, 3; 4, 5, 6; 7, 8, 9; 8, 7, 6"

With Cells(1).Resize(1 + Len(a) - Len(Replace(a, ";", vbNullString)))
    .Cells = Application.Transpose(Split(a, ";"))
    .TextToColumns Cells(1), _
        comma:=True, fieldinfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
End With

End Sub

[SidewinderAM9M]

Not a bad idea. I found that you can create a single dimensional array and use a counter variable and a loop to assign the variables in the single dimensional array. Not exactly ideal but certainly does what I wanted. Thanks.