如何将数组传递给VBA中的函数? [英] How to pass an array to a function in VBA?

问题描述

我正在尝试编写一个接受数组作为参数的函数.数组可以包含任意数量的元素.

I am trying to write a function that accepts an array as an argument. The array can have any number of elements.

Function processArr(Arr() As Variant) As String
    Dim N As Variant  
    dim finalStr as string      
    For N = LBound(Arr) To UBound(Arr)
        finalStr = finalStr & Arr(N)
    Next N
    processArr = finalStr
End Function

这是我尝试调用该函数的方式:

Here is how I try to call the function:

Sub test()
    Dim fString as string
    fString = processArr(Array("foo", "bar"))
End Sub

我收到一条错误消息:

编译错误:类型不匹配:预期为数组或用户定义的类型.

Compile Error: Type mismatch: array or user defined type expected.

我在做什么错了?

推荐答案

这似乎没有必要，但是VBA是一个奇怪的地方.如果您声明一个数组变量，然后使用Array()进行设置，然后将该变量传递给您的函数，VBA将很高兴.

This seems unnecessary, but VBA is a strange place. If you declare an array variable, then set it using Array() then pass the variable into your function, VBA will be happy.

Sub test()
    Dim fString As String
    Dim arr() As Variant
    arr = Array("foo", "bar")
    fString = processArr(arr)
End Sub

此外，您的函数processArr()可以写为:

Also your function processArr() could be written as:

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

如果您热衷于简洁.

这篇关于如何将数组传递给VBA中的函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！