arrays - Excel VBA函数: How to pass range,转换为数组，反转，返回数组

Question

我正在尝试在 Excel 中进行一些数组数学运算，这需要我多次反转多个一维范围，所以我想为它编写一个函数，而不是在电子表格中创建反转。

我在 VBA 中编写了一个 reverse() 函数，但它返回 #VALUE!电子表格中的错误。无论数组大小如何，无论是输入相同大小的数组函数还是使用 SUM() 之类的汇总函数，都会发生这种情况。我验证了反转逻辑作为子工作。这使我相信问题在于传递/返回范围/数组，但我不明白出了什么问题。

Function reverse(x As Range) As Variant()
' Array formula that reverses a one-dimensional array (1 row, x columns)
    Dim oldArray() As Variant, newArray() As Variant
    Dim rows As Long: i = x.rows.Count
    Dim cols  As Long: i = x.Columns.Count
    ReDim oldArray(1 To rows, 1 To cols), newArray(1 To rows, 1 To cols)

    oldArray = x.Value
    newArray = oldArray

    For i = 1 To cols / 2 Step 1
        newArray(1, i) = oldArray(1, cols - i + 1)
        newArray(1, cols - i + 1) = oldArray(1, i)
    Next

    reverse = newArray
End Function

请记住，我可以将它扩展到反向二维数组，但这是微不足道的部分。我的问题只是试图确保函数在 (1, N) 范围内工作。

谢谢!

Answer 1

找到下面的代码......

Function reverse(x As Range) As Variant()
' Array formula that reverses a one-dimensional array (1 row, x columns)
    Dim oldArray() As Variant, newArray() As Variant
    Dim rows As Long
    rows = x.rows.Count
    Dim cols  As Long
    cols = x.Columns.Count
    ReDim oldArray(1 To rows, 1 To cols), newArray(1 To rows, 1 To cols)

    oldArray = x.Value
    newArray = oldArray

    For i = 1 To cols / 2 Step 1
        newArray(1, i) = oldArray(1, cols - i + 1)
        newArray(1, cols - i + 1) = oldArray(1, i)
    Next
reverse = newArray
End Function