Excel VBA InStr
The VBA InStr function is used to check if a string is found in another line of text. And it returns the first position of it’s occur operations. It returns 0 if the text is not found; otherwise, it returns the character where the text is found.
The VBA InStr function performs exact matches or inexact matching by using the Operators. And by using the Wildcards, it performs the pattern matching.
The InStr is a VBA function but not a worksheet function. That means we cannot use the Instr function within the worksheet.
Syntax
Explanation
- [Start]: It is an optional integer argument that is representing the position that we want to start searching from.
- String1: This is the main string of the parent string which we want to search.
- String2: This is the substring that we want to search for.
- [Compare]: It is an optional argument, it specifying the type of comparison which we to make.
The compare argument settings are given in the below table:
| VBA Constant | Value | Description |
|---|---|---|
| vbUseCompareOption | -1 | It Performs a comparison |
| vbBinaryCompare | 0 | (Default) case-sensitive |
| vbTextCompare | 1 | It is Not case-sensitive |
| vbDatabaseCompare | 2 | Uses the information in the database to perform the comparison. |
- vbUseCompareOption: It performs the comparison with the help of setting of the Option Compare statement.
- vbBinaryCompare: It performs the character by character comparison. For example, if we are search ‘x’ in ‘Excel’, it will return 2, but if we are search ‘X’ in ‘Excel’, it will return 0 as X is in upper case. We can also use 0 rather than vbBinaryCompare. If the [Compare] argument is omitted, then vbBinaryCompare is taken as default.
- vbTextCompare: It perform a textual comparison. For example, if we look for ‘x’ or ‘X’ in Excel, it would return 2 in both cases. This argument ignores the letter case. We can also use 1 rather than vbTextCompare.
- vbDatabaseCompare: It performs a database comparison, and this is used for Microsoft Access only.
Some important points are:
- If substring or String2 is empty, the function will return the value of the [Start] argument.
- If the VBA InStr function cannot find the substring within the main string, it would return 0.
Examples
Example 1: Let’s find the position of the character “r” in the word “Paris” follow the following code, such as:
Now run the above code using the F5 key, or you can also run the code manually, and you will get the following output, such as:
Example 2: Let’s find the position of the character “A” in the word “Paris”. We will use the compare argument as vbBinaryCompare, as shown in the below screenshot:
Now execute the above program, the supplied compare argument as vbBinaryCompare InStr function returns the zero because there is no uppercase “A” letter exists in the word “Paris”.
