Excel VBA函数：如何传递范围，转换为数组，反向和返回数组 [英] Excel VBA function: How to pass range, convert to array, reverse, and return array

问题描述

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

我在VBA中编写了一个reverse（）函数，但它返回了#VALUE！电子表格中的错误。无论数组大小如何，或者输入相同大小的数组函数还是使用诸如SUM（）之类的摘要函数括起来，都会发生这种情况。我验证了反向逻辑可以作为Sub。这使我相信问题出在传递/返回范围/数组，但我不明白出了什么问题。

 函数reverse（x作为范围）作为Variant（）
'反转一维数组（1行，x列）的数组公式
 Dim oldArray（）作为Variant，newArray（）作为Variant 
 Dim行只要长：i = x.rows.Count 
 Dim cols长：i = x.Columns.Count 
 ReDim oldArray（1行，1 To cols），newArray（1 To行，1到cols）
 
 oldArray = x.Value 
 newArray = oldArray 
 
对于i = 1到cols / 2步骤1 
 newArray（ 1，i）= oldArray（1，cols-i + 1）
 newArray（1，cols-i + 1）= oldArray（1，i）
下一个
 
反向= newArray 
结束函数
  

请记住，我可以将其扩展为反转二维数组，但这是微不足道的部分。我的问题只是想确保函数在（1，N）范围内起作用。

谢谢！

解决方案

查找以下代码...。

 功能反转（x为范围）为Variant（）
'反转一维数组（1行，x列）的数组公式
 Dim oldArray（）作为Variant，newArray（）作为Variant 
 Dim行与Long 
行= x.rows.Count 
 Dim cols只要
 cols = x.Columns.Count 
 ReDim oldArray（1行，1 To cols），newArray（1行， 1至cols）
 
 oldArray = x.Value 
 newArray = oldArray 
 
对于i = 1至cols / 2步骤1 
 newArray（1， i）= oldArray（1，cols-i + 1）
 newArray（1，cols-i + 1）= oldArray（1，i）
下一个
反向= newArray 
结束功能
  

I am trying to do some array math in Excel which requires me to reverse a number of 1-dimensional ranges a good amount of times, so I want to write a function for it, rather than create reverses in the spreadsheet.

I have written a reverse() function in VBA but it returns #VALUE! errors in the spreadsheet. This happens no matter array size, nor whether inputting a same size array function or enclosing with a summary function like SUM(). I verified that the reversing logic works as a Sub. This leads me to believe the issue is with passing/returning the range/array but I don't understand what is wrong.

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

Keep in mind, I may extend it to reverse 2 dimensional arrays, but that's the trivial part. My question is just trying to ensure the function works on a (1, N) range.

Thanks!

解决方案

Find below code....

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

这篇关于Excel VBA函数：如何传递范围，转换为数组，反向和返回数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！