Creation of one dimensional array is straightforward :
Dim A As Variant
A = Array(10,20,30)
Is it possible to create two dimensional array with Array() ? Is there any other method ?
Add a comment
|
3 Answers
I knew I'd seen this somewhere before, but had to search for a bit:
Sub test()
Dim A As Variant
A = [{1,2,3;4,5,6;7,8,9}]
Debug.Print A(2, 2)
End Sub
3 Comments
John Alexiou
Nice workaround. The
[..] expects a worksheet range, but you provide an value array instead. Awesome!
Tom Robinson
Note that this technique creates a 1-based array, unlike the Array() function which returns a zero-based array. Also, it works in Excel but not in Access.
Doug Glancy
Tom, I agree and like your answer best.
You can create an array of arrays using the Array() function syntax:
x = Array(Array(0, 1, 2, 3, 4), Array(100, 101), Array(200, 201, 202))
debug.print x(2)(1)
201
This not actually a two-dimension array. This technique is used a lot in other languages.
1 Comment
Doug Glancy
Tom, this is the best answer. I tried to do this (it doesn't come up in day-to-day work for me, so haven't memorized it) but somehow botched the syntax.
I think it is not possible. With Array function You can only specify a list of values, not dimemsions.
Array Function Syntax
Array(arglist)
The required arglist argument is a comma-delimited list of values that are assigned to the elements of the array contained within the Variant.
And you can affect lower bound. Use Option Base 1 then the Array() function returns array whith lower bound 1. Only if Array() function is qualified with type library name e.g. VBA.Array() then Option Base does not affect the lower bound of the array returned by Array() function.
To create 2D array simply specify dimensions e.g. like this
Dim multiA(1 To 2, 1 To 2) As String
Or use ReDim like this
Dim multiA As Variant
ReDim multiA(1 To 2, 1 To 2)
