Check if a string is part of an array (containing wildcards...)

[jodecaesteker]

Hi all,



I'm new here and also very new to vba.

I want the user of an Excel worksheet to enter a string and I want to check whether that strings has a certain pattern.

The allowed patterns are:

##.
##.##.
##.##.L
##.##.##.
##.##.##.L
##.##.##.##.
##.##.##.##.L

# is any digit (0-9), L is any letter and the points are just that: points.
If the pattern of the string isn't one of the allowed patterns, a msgbox with an error message should appear and the macro should quit.

Is there a way to do this? (The patterns with the letters in them are not necessary, but it would be nice to include them.)

I have two choices for the string to be entered: the string might be entered by an inputbox, but I may also work with a userform. I know how to do that. I just need the check for the pattern...

Thanks in advance!
Jo D.

[jodecaesteker]

Found it myself!

 
Sub check_format()
Dim ok As Boolean
Dim str As String
 
'select the cell to check and run the macro - "ok" is the boolean that will...
'...determine whether the pattern is matching. It is set to true if the cell matches...
'...one of the allowed patterns.
 
str = Selection.Value
ok = False
 
Select Case True
    Case str Like "##[.]": ok = True
    Case str Like "##[.]##[.]": ok = True
    Case str Like "##[.]##[.]##[.]": ok = True
    Case str Like "##[.]##[.]##[.]##[.]": ok = True
End Select
 
If ok = False Then
    MsgBox ("Wrong pattern!")
Else
    MsgBox ("Pattern is ok!")
End If
 
End Sub

As said, I'm new to vba and my coding might not be as efficient as it could be.
I do like the "case" method as it is very readable and easy/convenient to add or delete new patterns.

Cheers!

[NoSparks]

The patterns with the letters in them are not necessary, but it would be nice to include them.

you could Case Select based on length of str and include the letter

Select Case Len(str)
    Case 3: If str Like "##[.]" Then ok = True
    Case 4: If str Like "##[.][A-Z, a-z]" Then ok = True
    ' etc
End Select

[jodecaesteker]

Originally Posted by NoSparks

you could Case Select based on length of str and include the letter

Select Case Len(str)
    Case 3: If str Like "##[.]" Then ok = True
    Case 4: If str Like "##[.][A-Z, a-z]" Then ok = True
    ' etc
End Select

Great, thanks for completing the solution!