Finding Multiple Text in a String

Question

I want to check if in a given text, there's one of several strings. e.g. ist "halt" OR "Stop" or "Wait" in my text then do something.

What does work if I use several elseifs. But I want a slimer more elegant Code

Both the subs down there work

Public Sub worksfine1(strText As String)
strText = LCase(strText)

If InStr(strText, "go") > 1 Then
        Call DoStuff1(strText)
    ElseIf InStr(strText, "wait") > 1 Then
        Call DoStuff2(strText)
    ElseIf InStr(strText, "stop") > 1 Then
        Call DoStuff2(strText)
    ElseIf InStr(strText, "halt") > 1 Then
        Call DoStuff2(strText)
End If
End Sub

Public Sub worksfine2(strText As String)
strText = LCase(strText)

If InStr(strText, "go") > 1 Then
        Call DoStuff1(strText)
    ElseIf InStr(strText, "wait") > 1 Or InStr(strText, "stop") > 1 Or InStr(strText, "halt") > 1 Then
        Call DoStuff2(strText)
End If
End Sub

What I want is something like

...
ElseIf InStr(strText, "wait",  "stop", "halt") > 1 Then
        Call DoStuff2(strText)
...

Is there an easy way, or do I have to live with the code above ?

FunThomas · Accepted Answer · 2019-05-17 11:00:48Z

1

There is no build-in function in VBA, but you could create your own. That would clean up your worksfine1-function - maybe not so usefull for this small example, but if you have several checks like this.

Try

Public Sub worksfine1(strText As String)
    strText = LCase(strText)

    If MyInStr(strText, "go") Then
        Call DoStuff2(strText)
    ElseIf MyInStr(strText, Array("wait", "stop", "halt")) Then
        Call DoStuff2(strText)
    End If
End Sub

Function MyInStr(text As String, words) As Boolean
    Dim i As Long
    If IsArray(words) Then
        For i = LBound(words) To UBound(words)
            If InStr(text, words(i)) > 0 Then
                MyInStr = True
                Exit Function
            End If
        Next i
    Else
        MyInStr = (InStr(text, words) > 0)
    End If
End Function

Note that you can call the function with either an array of words or a single word and that it returns a Boolean, not an Integer

edited May 17, 2019 at 11:00

answered May 17, 2019 at 8:18

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

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

3 Comments

webwohnraum Over a year ago

Yes an own Function would work didn't thik of that thank you.

webwohnraum Over a year ago

Just a question to it. The Lbound isn't really necessary, is it?

FunThomas Over a year ago

IMHO, you should always use lBound when looping over an array. A helper function shouldn't do any assumptions. In VBA, an array can start with 0, with 1 and even any other number

Collectives™ on Stack Overflow

Finding Multiple Text in a String

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related