数据验证与带有VBA的列表匹配

2 回答

• 1

  我建议更改返回 As Boolean ，然后只针对有效列表条目数组过滤 str .

  Function checkStr(ByVal str As String) As Boolean
  
      Dim objRegEx As Object, allMatches As Object
      Set objRegEx = CreateObject("VBScript.RegExp")
  
      With objRegEx
          .MultiLine = False
          .IgnoreCase = False
          .Global = True
          .Pattern = "^\d+(-\d+)?$"
      End With
  
      Set allMatches = objRegEx.Execute(str)
  
  
      Dim ValidList As Variant
      ValidList = Array("123", "456") 'your list of valid entries
  
      'check if either str is in the ValidList OR matches the regex
      If (UBound(Filter(ValidList, str)) > -1) Or (allMatches.Count > 0) Then
          checkStr = True
      End If
  
  End Function
  

  如果有效条目列表在一个范围内，您可以将其替换为：

  ValidList = WorksheetFunction.Transpose(Worksheets("SheetName").Range("A1:A10").Value)
  

  回复于 2024-05-17T17:28:23+08:00
• 1

  该列表从某个范围获取值 . 因此，取列表范围并使用 Application.Match() 检查 str 是否存在：

  Public Function checkStr(str As String) As Boolean
  
      Dim isItError As Variant
      isItError = Application.Match(str, Worksheets(1).Range("A1:A5"), 0)
  
      checkStr = Not IsError(isItError)
  
  End Function
  

  Application.Match() 将返回 error 或 true . 因此，您的功能可以使用 Not IsError() 修复 .

  ---

  如果你想将字符串与字符串和数字作为数字进行比较，请尝试将变量作为Variant传递，让VBA决定它实际上是什么：

  Public Function checkMe(someVar As Variant) As Boolean
  
      Dim isItError As Variant
      Dim formulaAddress As String
  
      With Range("C1").Validation
          formulaAddress = Right(.Formula1, Len(.Formula1) - 1)
      End With
  
      isItError = Application.Match(someVar, Range(formulaAddress))
      checkMe = Not IsError(isItError)
  
  End Function
  

  如果将变量显式定义为数字或字符串，则 Application.Match() 比较中将排除另一个选项：

  ?Application.Match("1",Array(1,2,3))
  Error 2042
  ?Application.Match(1,Array(1,2,3))
  1 
  ?Application.Match("1",Array("1","2","3"))
  1 
  ?Application.Match(1,Array("1","2","3"))
  Error 2042
  

  回复于 2024-05-17T17:28:23+08:00