Assign value to multiple dimension array

[Verald]

Hello, Using VBA in Excel 2010:

I am trying to find syntax to define a 2-dimension array by rows. For the below array

Dim matrix(1, 3) As Integer

I know I can assign each item individually such as:
matrix(0, 0) = 1
matrix(0, 1) = 2
matrix(0, 2) = 3
matrix(0, 3) = 4

I'm looking to do the same thing but easier to enter:
such as
matrix(0)=(1,2,3,4)
matrix(1)=(5,6,7,8)



Is this possible?

 

[Llupo01]

Hi,

if you have range A1 to D1 with your numbers, this will assign your arrays to matrix(1,1), matrix(1,2),matrix(1,3),matrix(1,4)...hope you can use it:

Dim matrix()

matrix = Range("A1", Range("A1").End(xlToRight))

Regards,
Tom

 

[shg]

This is about as close as you'll get:

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

 

[Rick Rothstein]

This is about as close as you'll get:

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

I think shg's method above will produce the actual array you want to end up with, so you should strongly consider moving ahead with it; however, there is a method to assign values along the lines of what you posted, namely similar to this manner...

matrix(0)=(1,2,3,4)
matrix(1)=(5,6,7,8)

although the syntax to address the resulting array will be a little bit "odd". The method I am thinking about uses the Array function to load up your matrix array. Consider this (and note that matrix is a one-dimensional array)...

Dim matrix() As Long
....
....
ReDim matrix(0 to 1)
matrix(0) = Array(1,2,3,4)
matrix(1) = Array(5,6,7,8)

The matrix array is a one-dimensional array each element of which contain a one-dimensional array of numbers. Now, because of this structure, you have to use a somewhat odd syntax to retrieve any particular grid element. Let's say you wanted to retrieve the 7 from the array, this is the syntax to address is (remember, the Array function will produce a zero-based array unless you have Option Base 1 in effect)...

MsgBox matrix(1)(2)

The first parenthesized number refers to the index into the one-dimensional matrix array and the second parenthesized number refers to the index into the array stored in that matrix array element.

 

[Verald]

Rick,

This is close enough to what I need to work. I can adjust the remainder of my code to the "odd" syntax. My main concern was easier to initialize the matrix. Thank you so much for your help. I'm so glad this resource is available.