将Excel字符串数组传递给用户定义的VBA函数

Question

我希望使用Excel数组函数来生成字符串数组，然后将其传递给用户定义的函数，以去除空格并连接由","分隔的字符串。

我有一个从VBA宏调用时执行此操作的函数。当我尝试将其用作用户定义的函数时，例如=ConcStr({"A","B","C"})，我得到一个#Value!错误。

函数如下：

Sub StrTest()
    Dim StaticArray(1 To 3) As String
    Dim Result As String
    StaticArray(1) = "A"
    StaticArray(2) = "B"
    StaticArray(3) = "C"
    Result = ConcStr(Arr:=StaticArray)
    MsgBox Result
End Sub

Function ConcStr(Arr() As String) As String
    MsgBox "started"
    Dim N As Long
    Dim Total As String
    For N = LBound(Arr) To UBound(Arr)
        MsgBox Arr(N)
        Total = Total & "," & Arr(N)
    Next N
    ConcStr = Total
End Function

Answer 1

如果你重写你的UDF来接受一个Variant，它应该可以工作。此外，您还可以只使用Join函数来完成所需的操作：

Function ConcStr(arr As Variant) As String
    ConcStr = Join(arr, ",")
End Function

Answer 2

我能得到你想要的：

Public Function ConcatString(ByVal arr As Variant) As String
    ConcatString = vbNullString
    Dim i As Long, n As Long, z as Long
    z = LBound(arr) : n = UBound(arr)
    For i = z To n
        ConcatString = ConcatString + arr(i)
    Next i
End Function

​

​

Answer 3

声明、调整、赋值并将数组作为变量传递。

Sub StrTest()
    Dim StaticArray As Variant, Result As String

    ReDim StaticArray(1 To 3)

    StaticArray(1) = "A"
    StaticArray(2) = "B"
    StaticArray(3) = "C"

    Result = ConcStr(Arr:=StaticArray)
    MsgBox Result
    Result = ConcStr2(Arr:=StaticArray)
    MsgBox Result

End Sub

Function ConcStr(Arr As Variant) As String
    MsgBox "started"

    Dim N As Long, Total As String

    For N = LBound(Arr) To UBound(Arr)
        MsgBox Arr(N)
        Total = Total & "," & Arr(N)
    Next N

    ConcStr = Mid(Total, 2) 'Mid to get rid of the first comma

End Function

Function ConcStr2(Arr As Variant) As String
    'could just be like this,
    ConcStr2 = Join(Arr, ",")
End Function

我添加了一个替代的Join Function版本来简化操作，并用Mid function修改了您的函数，删除了前导逗号。