For Each - evaluate - order of values depending if spilling function from worksheet or string parameter

Question

I need a function to extract values from a dynamic range generated by an excel spilling function and saving them into a dynamic array. For this purpose I use a For Each loop. It should work independently if the dynamic range comes from a EXCEL spilling function taken directly from a string parameter or through a reference to a spilling function in a worksheet. For some strange reason the order is different.

The cell A1 in the active sheet contains following formula:

=SEQUENCE(5,4,1,1)

Sub eval1()
    Dim v As Variant
    Dim s As String
    For Each v In Evaluate("=A1#")
        s = s & v & " "
    Next
    Debug.Print s
End Sub

Output:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Sub eval2()
    Dim v As Variant
    Dim s As String
    For Each v In Evaluate("=SEQUENCE(5,4,1,1)")
        s = s & v & " "
    Next
    Debug.Print s
End Sub

Output:

1 5 9 13 17 2 6 10 14 18 3 7 11 15 19 4 8 12 16 20

The output I expected was for both subs:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

or at least for both the same.

Answer 1

For Each over a Range always goes row-wise but when used over an array it depends on the order of the dimensions.

Sub Tester()
    Dim arr, a, b, c, n As Long
    
    Debug.Print "------Evaluate(""=A1#"")----------------------"
    PrintVals Evaluate("=A1#")
    
    Debug.Print "------[A1#]----------------------"
    PrintVals [A1#]
    
    Debug.Print "------Evaluate(""=Sequence(5, 4, 1, 1)"")------------"
    PrintVals Evaluate("=SEQUENCE(5,4,1,1)")
    
    'multi-dimensional arrays seem to be iterated in reverse order of dimensions...
    n = 3
    ReDim arr(1 To n, 1 To n, 1 To n)
    For a = 1 To n
        For b = 1 To n
            For c = 1 To n
                arr(a, b, c) = Join(Array("A", a, "-B", b, "-C", c), "")
            Next c
        Next b
    Next a
    Debug.Print "------3D array-----------"
    PrintVals arr, vbLf
    
End Sub

'Iterate `v` using `For Each`
Sub PrintVals(arr, Optional sep As String = " ")
    Dim el, s
    Debug.Print TypeName(arr)
    For Each el In arr
        s = s & el & sep
    Next el
    Debug.Print s
End Sub

------Evaluate("=A1#")----------------------
Range
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 

------[A1#]----------------------
Range
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 

------Evaluate("=Sequence(5, 4, 1, 1)")------------
Variant()
1 5 9 13 17 2 6 10 14 18 3 7 11 15 19 4 8 12 16 20 

------3D array-----------
Variant()
A1-B1-C1
A2-B1-C1
A3-B1-C1
A1-B2-C1
A2-B2-C1
A3-B2-C1
A1-B3-C1
A2-B3-C1
A3-B3-C1
A1-B1-C2
A2-B1-C2
A3-B1-C2
A1-B2-C2
A2-B2-C2
A3-B2-C2
A1-B3-C2
A2-B3-C2
A3-B3-C2
A1-B1-C3
A2-B1-C3
A3-B1-C3
A1-B2-C3
A2-B2-C3
A3-B2-C3
A1-B3-C3
A2-B3-C3
A3-B3-C3

Answer 2

The difference is row major order vs column major order traversal. Microsoft docs don't specify which For Each loop uses. All conclusions are based on code testing results.

For a range : Row Major Order

Sub ForEachRange()
    Dim c As Range, sMsg As String
    For Each c In Range("A1:B2")
        sMsg = sMsg & " " & c.Address(0, 0)
    Next
    Debug.Print sMsg
End Sub

Output:

A1 B1 A2 B2

For an array : Column Major Order

Sub ForEachArr()
    Dim c, sMsg As String
    Dim MyArray(1 To 2, 1 To 2) As Integer ' 2x2 array
    MyArray(1, 1) = 1
    MyArray(1, 2) = 2
    MyArray(2, 1) = 3
    MyArray(2, 2) = 4
    For Each c In MyArray
        sMsg = sMsg & " " & c
    Next
    Debug.Print sMsg
End Sub

Output:

1 3 2 4

I'm not sure why there is a difference. But it looks like:

For Each v In Evaluate("=A1#") is equivalent to For Each v In Range("A1:D5")

For Each v In Evaluate("=SEQUENCE(5,4,1,1)") is equivalent to

vArrary = Evaluate("=SEQUENCE(5,4,1,1)")
For Each v In vArrary

Using a nested For loop to iterate the result is more reliable.