-1

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

ghassen boughanmi
  • 1
  • 1
    `l = Array(Array(1, 0, 0), Array(0, 1, 0), Array(0, 0, 1))` – Warcupine Dec 02 '21 at 13:41
  • 1
    Dim My2DArray(1 To 3, 1 To 3) As Integer – Brian M Stafford Dec 02 '21 at 13:47
  • 1
    @Warcupine - that's a 1D jagged array, not a 2D array. – BigBen Dec 02 '21 at 13:52
  • @BigBen But it is the vba equivalent of the python list in the question. – Warcupine Dec 02 '21 at 13:59
  • @Warcupine - fair. Maybe semantics at this point. Technically if OP wants a 2D array in VBA, then Brian's suggestion may be better. But either should work. – BigBen Dec 02 '21 at 14:06
  • 1
    @bigben Yeah, his list is a jagged list it would be access `l[0][0]`, mine would be `l(0)(0)` and Brian's would be `l(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. – Warcupine Dec 02 '21 at 14:13
  • @Warcupine - exactly, that hits the nail right on the head. – BigBen Dec 02 '21 at 14:13
  • It depends if this 2D array is to interact with a worksheet table or just be processed within VBA. There are rules (like it cannot be a jagged array) if you are to interact with Excel. – JAlex Dec 02 '21 at 14:26

1 Answers1

5

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.

JAlex
  • 1,486
  • 8
  • 19
  • 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. – freeflow Dec 02 '21 at 14:28
  • @BigBen - thanks, fixed. – JAlex Dec 02 '21 at 15:33
  • 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. – Toddleson Dec 02 '21 at 15:57
  • @Toddleson - true, and hence I always use `Variant` for all arrays except when required otherwise, like with `VBA.Split()`. – JAlex Dec 02 '21 at 16:06
  • well my use is i defined an arrray this way { Public a() as variant } and set it up in a public worksheet for example{ a=array(1,2,3) } but then for some reason everything stored inside gets lost or rather i cant acces it in another sub (the other sub is in a classmodule) – ghassen boughanmi Dec 03 '21 at 20:10
  • @ghassenboughanmi If the array `a` is declared inside a function then when the function goes out of scope the contents are lost. So declare the array as a global in a module, or as a member variable to a worksheet (under the worksheet code, but outside the function). – JAlex Dec 06 '21 at 14:55
  • 1
    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](https://stackoverflow.com/questions/24584088/how-to-initialize-a-multidimensional-array-variable-in-vba-for-excel/66969472#66969472 @JAlex – T.M. Dec 10 '21 at 16:48