Excel VBA - 将列表作为函数参数传递

Question

使用某些功能，您可以执行 =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)
我想在接受它作为参数后遍历列表。谢谢!

Answer 1

您可以使用以下功能实现此目的:

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