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

jodecaesteker · #1 01-31-2018, 08:20 AM

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 · #2 02-01-2018, 02:34 AM

Found it myself!

Code:

 
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 · #3 02-01-2018, 07:03 AM

Quote:

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

Code:

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 · #4 02-02-2018, 10:56 AM

Quote:

Originally Posted by NoSparks

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

Code:

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!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How can I compare a string in a cell to another string?	Amitti	Word VBA	2	04-10-2017 07:35 PM
Is there a way to delete the beginning part of an array so it doesn't overflow?	omahadivision	Excel Programming	4	01-15-2014 11:22 PM
Way to search for a string in text file, pull out everything until another string?	omahadivision	Excel Programming	12	11-23-2013 12:10 PM
Spell check checking only part of document	Adeyo	Word	1	02-24-2013 10:49 PM
Convert String Array to Integer Array from a User Input?	tinfanide	Excel Programming	4	12-26-2012 08:56 PM