时间戳

首页 > 解决方案 > 扩展数组的函数

excel - 扩展数组的函数

问题描述

我将如何编写一个函数，我可以将一个数组传递给并让它返回，但扩展为 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 错误

标签： excelvba

解决方案

强制myArray成为一个数组并使其成为 type 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很多，这会带来高昂的成本并且会大大降低您的代码速度。有时，定义具有空槽的较大数组比多次调整数组大小更有效。

推荐阅读