Data validation matching with a list with VBA

Question

I have a certain range of cells in excel where I want to apply data validation on another data validation.

I want the user to only have 3 options for data input within range of cells:

1. either a number,
2. a range of numbers or by
3. choosing from a dropdown list that contains words and numbers.

I already implemented number 1 and 2 with the following function:

Function checkStr(ByVal str As String) As String

    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)
    checkStr = (allMatches.Count > 0)

End Function

Since the above function will only allow numbers or a range of numbers to be inputted, any ideas on how to add a validation to allow values from a pre-defined list containing both words and numbers?

Answer 1

I suggest to change the return As Boolean and then just filter the str against an array of valid list entries.

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

If the list of valid entries is in a range you can replace it with:

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

Answer 2

The list is taking values from some range. Thus, take the range of the list and use the Application.Match() to check whether the str is there:

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() would return either error or true. Thus, your function can be fixed with Not IsError().

---

And if you want to compare Strings with Strings and Numbers as Numbers, try to pass the variable as Variant and let VBA decide what it is actually:

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

If you explicitly define the variable as a numeric or string, the other option would be excluded in the Application.Match() comparison:

?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