Multiple string search with InStr in VBA

Question

I am checking whether a Name textbox starts with Mr. Mrs. Ms. etc.

I created a function but I am not able to compare more than one string.

Here is my code.

'Checking whether name is starts with Mr./Mrs./Ms./Dr. or not
If Not FindString(LCase(Me.gname.Value), LCase("Mr")) Then
    MsgBox "Consumer Name Starts with Mr./Mrs./Ms./Dr. Check Consumer Name"
    Cancel = True
    Exit Sub
End If

'Here is the Find String function i created
Function FindString(strCheck As String, strFind As String) As Boolean
    Dim intPos As Integer

    intPos = 0
    intPos = InStr(strCheck, strFind)
    FindString = intPos > 0
End Function

Jagadish Dabbiru · Accepted Answer · 2014-07-26 15:27:47Z

4

Pass strFind as group of strings seperated by a delimiter ex:-

FindString(LCase(Me.gname.Value), LCase("Mr;Mrs;Ms;Dr"))

Now split them and compare using a loop.

Arr = Split(strFind,";")
Flag = 0

For Each str in Arr    
  If InStr(strCheck, str) > 0 Then
  Flag = 1    
  End If
Next
If Flag = 1 Then
  FindString = True
Else
  FindString = False
End If

answered Jul 26, 2014 at 15:27

Jagadish Dabbiru

9519 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex K. · Accepted Answer · 2014-07-26 15:41:15Z

4

Pass a list of tokens to search for using a ParamArray and loop each looking for a match.

You can use vbTextCompare to enforce case sensitivity.

Remember that starts with is different from contains.

'// you can pass as many prefixes as you like
If StringStarts(Me.gname.Value, "Mr", "Mrs", "Dr", "Supreme Commander", "Capt.") Then
    MsgBox "Consumer Name Starts with Mr./Mrs./Ms./Dr. Check Consumer Name"

... 

Function StringStarts(strCheck As String, ParamArray anyOf()) As Boolean
    Dim item As Long
    For item = 0 To UBound(anyOf)
        If InStr(1, strCheck, anyOf(item), vbTextCompare) = 1 Then
            StringStarts = True
            Exit Function
        End If
    Next
End Function

Or better with a RegEx to allow optional . and not match Mruku

StringStarts(Me.gname.Value, "Mr|Mrs|Ms|Dr")

...

Function StringStarts(strCheck As String, options As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Pattern = "^(" & options & ")\.*\b"

        StringStarts = .Test(strCheck)
    End With
End Function

edited Jul 26, 2014 at 15:41

answered Jul 26, 2014 at 15:28

Alex K.

177k32 gold badges276 silver badges299 bronze badges

1 Comment

D. O. Over a year ago

@Alex K. Good! I like the solution with the regular expression. But what if we have more than 20 strings to search? I think it is not sweetable to write them all.

FreeSoftwareServers · Accepted Answer · 2019-10-25 01:49:18Z

1

This is my version of @AlexK great answer. While it solved the OP's original problem I wanted to share a more generalized answer for others to benefit from.

Here is how I used the Function:

Public Sub InString_Test()

Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("Sheet1")

Dim rcell As Range, rng As Range
Set rng = WS.Range("A1:A" & WS.UsedRange.Rows.Count)
For Each rcell In rng.Cells

If InStrFunc(Range(rcell.Address), "TEST", "CAT") Then
   MsgBox "String Found in " & rcell.Address
End If

Next rcell

End Sub

Function InStrFunc(strCheck As String, ParamArray anyOf()) As Boolean
    Dim item As Long
    For item = 0 To UBound(anyOf)
        If InStr(1, strCheck, anyOf(item), vbTextCompare) <> 0 Then
            InStrFunc = True
            Exit Function
        End If
    Next
End Function

edited Oct 25, 2019 at 1:49

answered Oct 25, 2019 at 1:43

FreeSoftwareServers

2,8906 gold badges43 silver badges79 bronze badges

1 Comment

Sagar Over a year ago

Super. Works straight outta the box.

Collectives™ on Stack Overflow

Multiple string search with InStr in VBA

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related