This Excel tutorial explains how to use Excel INSTR Function for VBA, and explain difference among vbUseCompareOption, vbBinaryCompare and vbTextCompare.
Excel VBA INSTR Function
Excel INSTR function is used to search a substring (part of the string) within a string and return the position of the first occurrence.
For example, in the string “FinanceDepartment”, the substring “Department” can be found at 8th position within “FinanceDepartment”, the function will return number “8”.
If we try to search substring “Division” within “FinanceDepartment”, since it cannot be found, number “0” will return.
INSTR is always used to test whether a string contains a substring, as always we just want to know “True” or “False” but not the actual position, so we can simply test whether the returned value is “0”.
For Excel spreadsheet, since there is no INSTR function, consider using Search Function or Find Function.
Syntax of Excel VBA INSTR Function
InStr( [start], string, substring, [compare] )
start is optional. It is the starting position for the search, default i s 1. For example, if you want to search “De” from “Development Department”, beginning from the 13th position, the function will return 13, not 1.
string is the full string
substring is part of the string you want to search
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 | Default value, uses setting of Option Compare statement defined at Module level.(Two options: Option Compare Text / Option Compare Binary)If you don’t write Option Compare statement, Option Compare Binary is default |
| vbBinaryCompare | 0 | Binary comparison, turn text into a sequence to compare. Since the sequence is case sensitive, you can compare text using this option if you want the comparison to be case sensitive, which means you cannot search “aaa” within “AAA” |
| vbTextCompare | 1 | Compare text, case insensitive, which means you can search “aAa” within “AAA” |
Note that if you use [compare], you have to also define [start], otherwise Excel mistakenly thinks your string is start.
Excel worksheet does not have INSTR function, but FIND Function is very similar to SEARCH, but they are different in the way that FIND does not allow case sensitive comparison and return error if substring is not found.
Example of Excel VBA INSTR Function
| VBA Code | Result | Explanation |
| INSTR(3,”123123″,”2″) | 5 | The first occurrence of “2” in “123123” starting from position 3 is at position 5 |
| INSTR(“FINANCE”,”f”) | 0 | By default, comparison is case sensitive, therefore “f” cannot be found in “Finance” and return 0 |
| INSTR(1, “FINANCE”, “f”, vbTextCompare) | 1 | vbTextCompare turns the comparison into case insensitive. Defining[compare] requires you to also define [start] |
Alternative of Excel VBA INSTR Function – using Wildcard
If you just want to check if a text contains specific substring, you can use wildcard to return TRUE or FALSE.
VBA supports the below three Wildcard characters. Note that Wildcard is case sensitive.
| Wildcard | Meaning | Example |
| * | Represents one or more characters (any character) | J* any text that starts with J *J starts with any text but ends with J *J* any text that has J in the middle |
| ? | Represents one character (any character) | J? 2 characters that start with J ?J 2 characters that end with J ?J? 3 characters with J in the middle |
| ~ | Treat * or ? as the actual character but not wildcard. Used ~ followed by * or ? | J~** any text that starts with J* ~**J any text that starts with * and ends with J ~?*~* any text that starts with ? and ends with * |
The below code check if worksheet contains “Del”, if TRUE delete it.
Sub loopWS() Application.DisplayAlerts = False Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If ws.Name Like "*Del*" Then ws.Delete End If Next ws Application.DisplayAlerts = True End Sub
Outbound References
https://msdn.microsoft.com/en-us/library/office/gg264811%28v=office.15%29.aspx