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 & "."
    Else
        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.

facebooklinkedin