Searching and Comparing Strings in Excel VBA

The basic point of InStr() in Excel is to find out if one string is contained within another. The return value is a Variant (Long) that returns the position of the substring within the main string. Under some conditions, this return value can be used as a Boolean value.

The general format is:

InStr(start_position, main_string, substring , comparison)

Start_position and comparison are optional, a more general form is:

InStr(main_string, substring)

The required items are main_string and substring.

Example 1:

Private Sub CommandButton1_Click()
     Dim myString As String
     myString = "Hello World"
    
     Dim subString As String
     subString = "Hello"
    
     If (InStr(myString, subString)) Then
         MsgBox "True"
     Else
         MsgBox "False"
     End If
 End Sub

The above code should return back:

If we alter that code:

Private Sub CommandButton1_Click()
     Dim myString As String
     myString = "Hello World"
    
     Dim subString As String
     subString = "Hello Human"
    
     If (InStr(myString, subString)) Then
         MsgBox "True"
     Else
         MsgBox "False"
     End If
 End Sub

The substring is no longer contained within the myString variable and we receive a false message box.

Example 2:

Private Sub CommandButton1_Click()
     Dim myString As String
     myString = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    
     Dim subString As String
     subString = "Z"
    
     Dim SubStringPos As Integer
    
     SubStringPos = InStr(myString, subString)
     MsgBox SubStringPos
 End Sub

This sets the subStringPos variable equal to the position where subString is found within the myString variable. Since “Z” is the 26th letter, we get back 26 as our value.

Example 3:

Private Sub CommandButton1_Click()
     Dim myString As String
     myString = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    
     Dim subString As String
     subString = "C"
    
     Dim SubStringPos As Integer
    
     SubStringPos = InStr(12, myString, subString)
     MsgBox SubStringPos
 End Sub

I have added the optional starting position parameter. I have set it to 12. The substring we are looking for is “C” and that occurs in the 3rd position. This will result in the InStr() function not finding “C” and it should return a value of 0.

Example 4:

Private Sub CommandButton1_Click()
     Dim myString As String
     myString = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    
     Dim subString As String
     subString = "H"
    
     Dim SubStringPos As Integer
     Dim SubStringPos2 As Integer
    
     SubStringPos = InStr(2, myString, subString, vbTextCompare)
     SubStringPos2 = InStr(2, myString, subString, 1)
    
     MsgBox SubStringPos
     MsgBox SubStringPos2
 End Sub

I have added the optional comparison parameter using both the constant variable and the constant variable equivalent value.

This will result in two message boxes, both showing the position of “H” in the myString variable.

Excel VBA Resources