Excel InStr VBA Function: What It Is and How to Use It?

The Excel InStr function is a great function to know in Excel VBA.  This function provides a way of querying a string to find the first occurence of one string within another.  You can set a static variable and compare it to a dynamic variable (in a loop or such) that when found, can perform some sort of logic the user specifies.

The Function:

InStr([
 Start],
 FirstString,
 SecondString[,
 Compare])

Start: Integer. This is optional, you can choose to start looking at a string a certain character in the string (i.e. 3 would start 3 charcters in)

FirstString:1st string you are comparing

SecondString:String you are comparing the first string to

Compare:This is optional. Below are the listed options for this value:

vbUseCompareOption: – 1 … A comparison using the setting of the Option Compare statement

vbBinaryCompare: 0 … This a binary comparison

vbTextCompare: 1 This will compare textually

 

Example of InStr function:

Function ShowThemInStr()
 
    Dim getstring1 As String
 
    Dim getstring2 As String
 
    Dim CompareTheTwo As Boolean
 
    Dim currentrow As Integer
 
   
 
    CompareTheTwo = False
 
   
 
   
 
    For currentrow = 1 To Sheets("Sheet1").UsedRange.Rows.Count
 
       
 
        ChangingText = Sheets("Sheet1").Cells(currentrow, 1).Value
 
        CompareTheTwo = InStr(ChangingText, "Goose")
 
       
 
        If CompareTheTwo = True Then
 
            MsgBox "You Found the word Goose in cell " & currentrow & "!"
 
            Exit For
 
        End If
 
   
 
    Next

End Function

What this function does is cycle through all of the cells in Column A (1) until it finds the word “Goose”. If “Goose” is found, which would mean the variable was true since we dimmed it as a Boolean operator, the IF statement would take over and the logic would be performed.  In this case, a message box would pop up and tell you what row the word “Goose” was found in.