How to Identify Substring in a string without spaces

Question

I am looking to check whether the substring PROD is present in the stringToday's proDuction and return values as True or False.

Here the expected answer is true.

How can I do this in VBA

Answer 1

Use Like.

Sub testString()

    Dim myStr As String

    myStr = "Today's production"

    MsgBox myStr Like "*prod*"

End Sub

This would require the usage of Option Compare Text at the top of your module if you are wanting case-insensitive results, as the default compare method is to compare Binary (case-sensitive).

An easier method as suggested by Jeeped would be best if you do not want to check for case-sensitivity.

Sub testString()

    Dim myStr As String

    myStr = "Today's proDuction"

    MsgBox InStr(1, myStr, "prod", vbTextCompare) > 0

End Sub

Answer 2

My original answer:

Instr(Ucase("Today's proDuction"), "PROD") > 0

or

Instr(Lcase("Today's proDuction"), "prod") > 0

EDIT: In light of @GSerg's comment below and the answer from @KDavis, I thought it would be worth investigating and comparing different approaches. Interestingly, the results are not what I had expected. Here is my test code:

Option Explicit
Option Compare Text

Sub A()

    Dim i As Long
    Dim res As Boolean

    '// Timer class courtesy of @Mike Woodhouse here:
    '// https://stackoverflow.com/questions/198409/how-do-you-test-running-time-of-vba-code
    Dim tt As cTimer

    Set tt = New cTimer

    '// First test using UCASE to convert text with default comparison mode
    Debug.Print "A " & Now();

    tt.StartCounter

    For i = 1 To 10000000
        '// Need to state binary compare explicitly to override Option Compare Text above
        res = InStr(1, UCase("Today's proDuction"), "PROD", vbBinaryCompare) > 0
    Next i

    Debug.Print " [" & tt.TimeElapsed & "]"


    '// Second test using vbTextCompare comparison mode and implicit type conversion
    Debug.Print "B " & Now();

    tt.StartCounter

    For i = 1 To 10000000
        res = InStr(1, "Today's proDuction", "PROD", vbTextCompare)
    Next i


    Debug.Print " [" & tt.TimeElapsed & "]"


    '// Third test using vbTextCompare comparison mode and explicit test to convert to boolean
    Debug.Print "C " & Now();

    tt.StartCounter

    For i = 1 To 10000000
        res = InStr(1, "Today's proDuction", "PROD", vbTextCompare) > 0
    Next i

    Debug.Print " [" & tt.TimeElapsed & "]"



    '// Fourth test using vbTextCompare comparison mode and explicit type conversion
    Debug.Print "D " & Now();

    tt.StartCounter

    For i = 1 To 10000000
        res = CBool(InStr(1, "Today's proDuction", "PROD", vbTextCompare))
    Next i

    Debug.Print " [" & tt.TimeElapsed & "]"

    '// Fourth test using like
    Debug.Print "E " & Now();

    tt.StartCounter

    For i = 1 To 10000000
        res = "Today's proDuction" Like "*PROD*"
    Next i

    Debug.Print " [" & tt.TimeElapsed & "]"
End Sub

Here is a typical result, pretty consistent across a number of test runs:

A 28/02/2018 14:36:29 [3479.85848592479]
B 28/02/2018 14:36:33 [5145.24250798252]
C 28/02/2018 14:36:38 [5159.0118225338]
D 28/02/2018 14:36:43 [6627.32650697809]
E 28/02/2018 14:36:50 [6042.61252265476]

So, it appears that converting to upper case and then using INSTR with the default comparison mode operates detectably faster than both using the vbTextCompare comparison mode of InStr, and using Like syntax with wildcards.

In most cases, of course, the difference in performance is not likely to be noticeable.