arrays - 如何在 VBA 中调用带有列表和另一个参数的函数？

Question

我的目标是在 VBA Excel 中制作一个小程序/宏，自动修复列名称。我想要执行此操作的方法是为每个单独的列名称单元格设置Subs。每个 Subs 都有一个字符串 Array，其中包含将要搜索的关键字。如果单元格值恰好是字符串之一，则该值将是固定的。然而，如何修复它们取决于列名称，这就是为什么每个子项都需要单独创建的原因。

现在，我创建了 Sub，它调用所有单个 cell 修复 Subs。我还创建了一个 Function 来测试相应的 cell 是否包含其中一个关键字。但是，我不知道如何正确地将有关可搜索字符串的 Array 和相应的 cell 的信息传递给 Sub。当我尝试调用 FindMatch() 函数时，收到“运行时错误 '13':类型不匹配”。

Function FindMatch(textArr() As String, cell As Range) As Boolean
    FindMatch = False
    Dim testCell As Range
    Dim i As Integer

' Range.Find() each string in the Array. If one of the Strings is found, FindMatch is true
    For i = 0 To UBound(textArr)
        Set testCell = cell.Find(What:=textArr(i), LookIn:=xlValues, LookAt:=xlWhole)

        If Not testCell Is Nothing Then
            FindMatch = True
        End If

    Next i

End Function

从这里调用:

Sub FindCr(cell As Range)

    Dim match As Boolean
    match = False

    Dim textArr() As String
    ' I get the type mismatch error here.
    textArr = Array("Cr", "Bag ", " Dog")

    match = FindMatch(textArr, cell)

    If match Then
' Do stuff depending on the column, for example:
        HighlightRange cell
    End If
End Sub

这是调用各个查找和修复 Sub 的主 Sub。它也是从一个遍历整个列名称单元格范围的大主循环中调用的。

Public Sub fixColumnNames(cell As Range)
    'Do find-and-fix functions for all possible column names
    FindCr cell
End Sub

注意，我不想使用 ParamArray，因为我还需要在 FindMatch() 函数中传递 cell。我相信 ParamArray 仅在调用中存在未定义数量的参数时使用。我不希望 cell 进入 ParamArray。

我尝试将 FindMatch 和 Dim textArr() 的声明更改为 As Variant，但这确实弄乱了 Range.Find() 函数。当给它一个 Variant 时，它开始匹配各种错误的关键字。像这样:

Function FindMatch(textArr() As Variant, cell As Range) As Boolean

还有

Dim textArr() As Variant
textArr = Array("Cr", "Bag ", " Dog")
match = FindMatch(textArr, cell)

因此，如果您有其他方法将字符串列表传递给具有其他附加参数的函数，请教我。或者，如果您知道导致问题的原因，我们将不胜感激。

Answer 1

我不太确定您遇到问题的原因，但这对我来说效果很好。由于您遇到了 ParamArray 问题，因此我继续将其合并到代码中以进行演示。

Sub FindCr(cell As Range)

    If FindMatch(cell, "Cr", "Bag ", " Dog") Then
        MsgBox "There was a match!"
    End If

End Sub

Function FindMatch(ByVal cell As Range, ParamArray textArr() As Variant) As Boolean

    Dim i As Long
    For i = 0 To UBound(textArr)

        If Not cell.Find(What:=textArr(i), LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
            FindMatch = True
            Exit Function
        End If

    Next

End Function

为了便于阅读，我已将其缩短了一些。您从未演示过为 Cell 参数传递的内容，但请确保您完全限定了范围。

Dim Cell As Range
Set Cell = Thisworkbook.Worksheets(1).Range("A1")

将是完全限定范围的示例。