bbb bag

Excel InStr VBA Function

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:


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
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.

Need Support or Help?

Send us an Email, we'll get back to you asap.

Frequently Asked Questions

Our FAQ may answer some questions that you about our company & services.

Anything else we can do?

We'd love to hear from you. Contact us to see how we can help.

Hundreds of Satisfied Clients

Excel Help