arrays - 查找字符串是否在二维 VBA Excel 数组中

Question

我有一个很棒的函数，我一直将其用于一维 Excel VBA 数组，用于检查字符串是否在数组中:

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean

    IsInArray = (UBound(Filter(arr(), stringToBeFound)) > -1)

End Function

不幸的是，当使用它来检查二维数组时它不起作用，就像我在这里一样:

Sub new_idea_filter()

home_sheet = ActiveSheet.Name

c = 1

Dim myfilters(1 To 4, 1 To 5000)


myfilters(1, 4) = "Test"

If IsInArray("Test", myfilters()) = True Then
    killer = True
End If



End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean

    IsInArray = (UBound(Filter(arr(), stringToBeFound)) > -1)

End Function

函数中不断出错，说下标超出范围，有人知道如何检查字符串是否在二维数组中吗？

Answer 1

我的代码集合中的一些内容

您可以使用Application.Match。这适用于 1D 和 2D 数组:)

看这个

Sub Sample()
    Dim myfilters(1 To 4, 1 To 5000)

    myfilters(1, 4) = "Test"

    If IsInArray("Test", myfilters()) = True Then MsgBox "Found"
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    Dim bDimen As Byte, i As Long

    On Error Resume Next
    If IsError(UBound(arr, 2)) Then bDimen = 1 Else bDimen = 2
    On Error GoTo 0

    Select Case bDimen
    Case 1
        On Error Resume Next
        IsInArray = Application.Match(stringToBeFound, arr, 0)
        On Error GoTo 0
    Case 2
        For i = 1 To UBound(arr, 2)
            On Error Resume Next
            IsInArray = Application.Match(stringToBeFound, Application.Index(arr, , i), 0)
            On Error GoTo 0
            If IsInArray = True Then Exit For
        Next
    End Select
End Function