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.