![](https://codky.com/wp-content/uploads/2024/01/How-to-use-InStr-function-in-VBA-1024x576.png)
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.
Unlock Your Potential
Help us grow the project