MS Excel: INSTR Function (VBA)
In Microsoft Excel, the INSTR function returns the position of the first occurrence of a substring in a string.
Syntax
The syntax for the INSTR function is:
InStr( [start], string, substring, [compare] )
start is optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position 1.
string is the string to search within.
substring is the substring that you want to find.
compare is optional. It is the type of comparison to perform. It can be one of the following values:
| VBA Constant | Value | Explanation |
|---|---|---|
| vbUseCompareOption | -1 | Uses option compare |
| vbBinaryCompare | 0 | Binary comparison |
| vbTextCompare | 1 | Textual comparison |
| vbDatabaseCompare | 2 | Comparison based on your database |
Applies To
- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Type of Function
- VBA function (VBA)
VBA Function Example
The INSTR function can only be used in VBA code. Here are some examples of what the INSTR function would return:
| InStr(1, "Tech on the Net", "the") | would return 9 |
| InStr("Tech on the Net", "the") | would return 9 |
| InStr(10, "Tech on the Net", "t") | would return 15 |
For example:
Dim LPosition As Integer LPosition = InStr(10, "Tech on the Net", "t")
In this example, the variable called LPosition would now contain the value 15.