excel - 扩展数组的函数

Question

我如何编写一个函数，我可以将数组传递给它并返回它，但扩展为 1？

例如

myArray = expandArray(myArray)

Function expandArray(myArray As Long)
    Dim x As Integer
    x = UBound(myArray) + 1
    ReDim Preserve myArray(x)
    expandArray = myArray

End Function

我在上面遇到了 ByRef 错误

Answer 1

强制myArray 为数组并使其成为Variant 类型。如果您确定您的数组仅包含 Long 类型的值，则将 Variant 替换为 Long。

Sub Test()
    Dim myOriginalArray()
    myOriginalArray = Array(1, 2, 3)

    Dim MyReturnedArray()
    MyReturnedArray = expandArray(myOriginalArray)
   
    'because of ByRef both myOriginalArray and MyReturnedArray got expanded
End Sub

Function expandArray(ByRef myArray() As Variant) As Variant
    Dim x As Long
    x = UBound(myArray) + 1
    ReDim Preserve myArray(x)
    expandArray = myArray
End Function

但请注意，您只能给数组ByRef，这意味着 myOriginalArray 也会被扩展!

所以让它成为过程而不是函数会更清楚

Sub Test()
    Dim myOriginalArray()
    myOriginalArray = Array(1, 2, 3)

    expandArray myOriginalArray 
    'the myOriginalArray got expanded because of ByRef
End Sub

Sub expandArray(ByRef myArray() As Variant)
    Dim x As Long
    x = UBound(myArray) + 1
    ReDim Preserve myArray(x)
End Function

或者如果您需要 myOriginalArray 不改变，

Sub Test()
    Dim myOriginalArray()
    myOriginalArray = Array(1, 2, 3)

    Dim MyReturnedArray()
    MyReturnedArray = expandArray(myOriginalArray)

    'here only MyReturnedArray is the expanded version
End Sub

Public Function expandArray(ByRef myArray() As Variant) As Variant
    Dim x As Long
    x = UBound(myArray) + 1
    
    Dim ReturnArray() As Variant
    ReturnArray = myArray 'make sure only the return array gets expanded even with ByRef
    
    ReDim Preserve ReturnArray(x)
    expandArray = ReturnArray
End Function

---

最后的想法

请注意，如果您经常使用 ReDim Preserve，这会带来很高的成本并且会大大降低您的代码速度。有时，定义一个具有空槽的更大数组比多次调整数组大小更有效。