in python it is possible to declare a list in a list for example like this l=[[1,0,0],[0,1,0],[0,0,1]] how do i do the same but in VBA excel
1 Answer
Declaring an array in VBA is different from initializing its contents (unlike python).
You declare a variable to be a 2D array of size N, M with
Dim a(1 to N, 1 to M) as Variant
but this creates a fixed sized array and not a dynamic array. It is better to declare an array as follows
Dim a() as Variant
ReDim a(1 to N, 1 to M)
and to fill the array with values you need a double loop, or assigning elements manually
a(1,1) = ...
a(1,2) = ...
Notice that I declared arrays that are 1-based, instead of 0-based (first element is a(1,1), and not a(0,0)) because when reading in a table from an Excel worksheet with
a = Range("A2").Resize(N,M).Value
Debug.Print a(3,1)
it creates such an array.
Note that it is possible to declare a dynamic array and fill it in with jagged form (array of arrays) using the following code
Dim a() as Variant
a = Array( Array(1,2,3), Array(4,5,6), Array(7,8,9) )
which you have access to in VBA with the following form
Debug.Print a(2)(0)
' 7
Notice that they arrays produced by the Array() command are 0-based with indexes varying between 0..N-1. Additionally, since the above is jagged array (array of arrays), the elements are accessed using sequential indexers as in a(i)(j) instead of a(i,j)
So it really depends on how you want to use the 2D array to decide how to best declare it.
7 Comments
freeflow
Just be aware that with VBA you can set negative value for the start and end of a dimension. i.e. Dim myArray(-10 to -5) is perfectly legal.
jalex
@BigBen - thanks, fixed.
Toddleson
Warning when using
Array(). The arrays produced by Array() are also Variant type. If you need an array of some other type you have to use one of the other methods of declaring and filling the array.jalex
@Toddleson - true, and hence I always use
Variant for all arrays except when required otherwise, like with VBA.Split().
T.M.
It might be of some interest that you can transform the jagged array (assigned via
a=Array( Array(1,2,3), Array(4,5,6), Array(7,8,9) )) into a 2-dim array simply via a= Application.Index(a, 0, 0). - See my related answer [How to initialize a multidimensional array](stackoverflow.com/questions/24584088/… @JAlex |
l = Array(Array(1, 0, 0), Array(0, 1, 0), Array(0, 0, 1))l[0][0], mine would bel(0)(0)and Brian's would bel(0, 0)(or 1, 1 with his indexing). I don't think core python has a true multidimensional one, I've only seen it in numpy, but I'm not sure about that.