How to use InStr function in VBA?

In VBA (Visual Basic for Applications), the InStr function is used to find the position of the first occurrence of a substring within another string. The function returns an integer specifying the start position of the first occurrence of one string within another. If the substring is not found, the InStr function returns 0. The syntax of the InStr function is:

InStr([start], string1, string2, [compare])
Where: start is optional. It is the starting position for the search. If omitted, the search starts at the beginning of string1. string1 is the string to be searched. string2 is the substring to search for within string1. compare is optional. It specifies the type of comparison to use (e.g., binary or textual). If omitted, a binary comparison is performed. Here’s an example of how to use the InStr function in VBA:

Sub FindSubstringPosition()
    Dim text As String
    Dim substring As String
    Dim position As Integer
    text = "Hello, World!"
    substring = "World"
    ' Search for the position of "World" in "Hello, World!"
    position = InStr(1, text, substring, vbTextCompare) ' Start at the first character, and use a case-insensitive comparison
    ' Check if the substring was found
    If position > 0 Then
        MsgBox "The substring '" & substring & "' was found at position " & position & "."
        MsgBox "The substring '" & substring & "' was not found."
    End If
End Sub
In this example, the InStr function searches for the substring “World” starting at the first character of the text “Hello, World!” and uses a textual comparison (ignoring case). If the substring is found, the MsgBox displays the position at which it was found; otherwise, it informs the user that the substring was not found. Remember that VBA string positions are 1-based, meaning the first character of the string has an index of 1.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *