如何检查我的变量值是否在 VBA 中预定义的静态值列表中？

我认为Application.Match是错误的方法，因为第二个参数是一个单元格数组，没有列表。 以下是三种解决方案（myRowNumCheck0、myRowNumCheck1、myRowNumCheck2）：

Option Explicit

Public Const relevantRowNumsAsString As String = "#15#22#35#"  ' your predefined list seperated by #
                                                               ' neccessary for myRowNumCheck1 and myRowNumCheck2
Sub Test()

Debug.Print "myRowNumCheck0: " & myRowNumCheck1(4) & " " & myRowNumCheck1(22) & " " & myRowNumCheck1(35) & " " & myRowNumCheck1(55)
Debug.Print "myRowNumCheck1: " & myRowNumCheck1(4) & " " & myRowNumCheck1(22) & " " & myRowNumCheck1(35) & " " & myRowNumCheck1(55)
Debug.Print "myRowNumCheck2: " & myRowNumCheck1(4) & " " & myRowNumCheck1(22) & " " & myRowNumCheck1(35) & " " & myRowNumCheck1(55)
End Sub

Function myRowNumCheck0(numberToCheck As Integer) As Boolean
    Dim myArray(3) As Integer
    Dim k As Integer
    myArray(0) = 15
    myArray(1) = 22
    myArray(2) = 35
    myRowNumCheck0 = False
    
    For k = 0 To UBound(myArray)
        If numberToCheck = myArray(k) Then
            myRowNumCheck0 = True
            Exit Function
        End If
    Next k

End Function
Function myRowNumCheck1(numberToCheck As Integer) As Boolean
    Dim myArray() As String
    Dim numberToCheckString As String
    Dim k As Integer
    
    myArray = Split(relevantRowNumsAsString, "#", -1, vbTextCompare)
    myRowNumCheck1 = False
    
    For k = 0 To UBound(myArray)
        If CStr(numberToCheck) = myArray(k) Then
            myRowNumCheck1 = True
            Exit Function
        End If
    Next k

End Function
Function myRowNumCheck2(numberToCheck As Integer) As Boolean
    
    If InStr(0, "#" & numberToCheck & "#", relevantRowNumsAsString, vbTextCompare) > 0 Then
        myRowNumCheck2 = True
    Else
        myRowNumCheck2 = False
    End If
End Function

调用测试生成以下输出：

myRowNumCheck0: False True True False
myRowNumCheck1: False True True False
myRowNumCheck2: False True True False

如果你的列表只有几个元素，我更喜欢 myRowNumCheck2，因为你避免使用数组。对于 myRowNumCheck2，使用分隔符 # 启动和关闭 relevantRowNumsAsString 非常重要。 您必须确保分隔符之间的字符仅由数字（0 到 9）组成。