Vfrdtyky

First of all, I don´t want to use nested For-loops, because I read that these would become slower with bigger amount of data (n can be up to 10k). So, now, I have an array arrData(n,2), while n is variable, and I want to go through this array per row, using the for each statement.
So here is my code. For simplification I inserted an (2,2)-array:

Sub test()



    Dim arrData(2, 2) As Variant



    arrData(0, 0) = 0

    arrData(0, 1) = 0

    arrData(0, 2) = 0

    arrData(1, 0) = 1

    arrData(1, 1) = 1

    arrData(1, 2) = 1

    arrData(2, 0) = 2

    arrData(2, 1) = 2

    arrData(2, 2) = 2



    For Each Element In arrData

        MsgBox Element

    Next Element



End Sub

I get 012012012, but I want to get 000111222.

First, let's address this misconception:

I read that these would become slower with bigger amount of data (n can be up to 10k)

This is simply not true. Multi-dimensional arrays are only "slower" than single dimensional arrays because the memory address of the indexer has to be calculated (more on this later). What you are most likely referring to is the computational complexity of a nested loop - the number of iterations increases as the product of bounds of each loop. You have a fixed number of elements, so this will be the same regardless of how they are accessed. If you want to perform operation o on every member of a two dimensional array, you will perform that computation b1 * b2 times. Period.

Now, to explain the result that your sample code gives, lets look at how VBA lays out an array in memory. I'll simplify this a bit by only looking at the data area (there is also a SAFEARRAY structure that holds meta-information about the array, but that's not really germane). An array with a single dimension is laid out as a contiguous area of memory, and VBA maintains a pointer to the first element. For example, a one dimensional array of Long would look like this (Dim foo(4) As Long):

The SAFEARRAY structure holds a pointer to 'Element 0', and when you access this in your code, it multiplies the indexer by the length of the element type in bytes, and then returns the value at that memory address. So if the first element were at memory address 0x0000 and you accessed foo(2), it would multiply 2 by 4 (the length of a Long, add that to 0x0000, and give you the 4 bytes beginning at 0x0008.

Basically, A + (L * E1), where A is the base address, L is the element length, and E1 is the element you're requesting.

The second dimension adds N copies of this layout in memory where N is the number of elements in the second dimension. So, the array in your example code is laid out like this (Dim foo(2, 2) As Long) :

VBA indexes this the same as the one dimensional array, except for the second dimension, it adds the product of the indexer for the second dimension and the total length of a complete first dimension to the address calculation for the element in dimension one.

Basically, A + (L * E1) + (L * B1 * E2), where B1 is the element count of the first dimension and E2 is the index for the second. So, if you were accessing foo(1, 1) from a base address of 0x0000, it would be 0 + (4 * 1) + (4 * 3 * 1), or
0x0010.

Quick aside - this is why you can't Redim Preserve anything other than the top dimension of an array - that's the only case where it's a simple memory allocation and copy.

So, pivoting to your example, your values are stored like this this in memory:

When you use For Each, VBA's array iterator simply gives you back each element in memory order, so you get 012012012. For your specific example, you can get them back in 000111222 order by transposing it - what you are calling the "row", is actually the first dimension in your example:

 Sub Example()

    Dim arrData(2, 2) As Variant



    arrData(0, 0) = 0

    arrData(1, 0) = 0

    arrData(2, 0) = 0

    arrData(0, 1) = 1

    arrData(1, 1) = 1

    arrData(2, 1) = 1

    arrData(0, 2) = 2

    arrData(1, 2) = 2

    arrData(2, 2) = 2



    For Each Element In arrData

        Debug.Print Element

    Next Element

End Sub

This lays the array out like so in memory:

That said, there's more overhead to the For Each loop than there is for a simple For loop because VBA has to use an array enumerator and push _NewEnum calls onto the stack. While you might see a tiny performance increase in the indexing because it is only adding an offset to the memory address instead of performing the longer calculation each time, this is more than outweighed by repeatedly pushing and popping the call stack. So, long story short, just nest the loops:

Dim outer As Long

Dim inner As Long

For outer = LBound(arrData, 1) To UBound(arrData, 1)

    For inner = LBound(arrData, 2) To UBound(arrData, 2)

        Debug.Print arrData(outer, inner)

    Next

Next

In your case, you would "transpose" the array by swapping the inner and outer loops.

NOTE: I am not using "row" in the context of Excel (although it would be the first rank), and by "transpose", I do not mean using Excel's Transpose function - that would have worse performance than either alternative.

I am a little confused. I don't think using a nested structure to loop rows and columns will prove that significant a performance hit. If you have lots of data you have lots of data.

You need to loop rows and columns in order to access all items in an efficient manner i.e. nested loop structure. I don't know of a more efficient manner to access each item from a 2D array.

Where you can improve your code is to use a For Loop which is faster than a For Each when dealing with arrays.