使用某些功能，您可以执行以下操作 =SEARCH({"some","thing"},A1)
我该如何编写一个接受类似参数的函数{"some","thing"}？
我尝试过，Function whatever(my_list as Variant)并Function whatever(my_list() as Variant)根据我见过的其他一些答案/站点进行了尝试，但是它不起作用，我不确定为什么。
我认为如果我的函数是类似的东西，上面的方法会起作用，=WHATEVER("some","thing")但是我想要其他参数，例如=WHATEVER(A1,{"some","thing"},0,B1)
我想在接受列表作为参数后遍历列表。谢谢！
您可以使用以下功能实现此目的：
Public Function Foo(values As Variant) As String
Dim i As Long
For i = LBound(values) To UBound(values)
Foo = Foo & values(i)
Next i
End Function
然后在Excel公式中使用它，如下所示：
=Foo({"a","b","c"})
会传回的储存格值abc。
编辑....
但是该函数将无法处理二维数组，例如=Foo({"a","b","c";"d","e","f"})或将Range传递给该函数=Foo(A1:B6)。
这更加健壮：
Public Function Foo(ByVal values As Variant) As String
Dim temp As Variant
temp = values
Dim i As Long
Dim j As Long
Dim is2D As Boolean
If IsArray(temp) Then
On Error Resume Next
is2D = IsNumeric(LBound(temp, 2))
On Error GoTo 0
If is2D Then
For i = LBound(temp, 1) To UBound(temp, 1)
For j = LBound(temp, 2) To UBound(temp, 2)
Foo = Foo & temp(i, j)
Next j
Next i
Else
For i = LBound(temp) To UBound(temp)
Foo = Foo & temp(i)
Next i
End If
Else
Foo = temp
End If
End Function
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句