VBA Instr Function
We often require finding the word from a sentence or entire Excel sheet to apply some formatting or to change it. For instance, you want to apply bold font format to only the word ‘VBA’ from the String ‘VBA Programming Language’. The best way to locate the occurrence of a word is by using the built-in VBA InStr function.
This tutorial will run you through the definition, syntax, parameters, return type, and different examples explaining how to write a macro code using the VBA InStr function.
What is Instr Function?
“The VBA Instr String function is helpful in finding the position of any specific character in the supplied string. For example, if you want to find the position of ‘x’ in ‘Excel’, using the Excel VBA Instr function would return 2.”
The VBA INSTR function is an inbuilt function categorized under String/Text Functions. This function is coded in VBA macro in the Microsoft Visual Basic Editor. However, you can also use INSTR as an Excel function in Excel spreadsheets.
Syntax
Parameter
Start (optional): This parameter represents the starting position for the search. If this parameter is not skipped, the default start value is 1.
String (required): This parameter represents the string to search within.
Substring (required): This parameter represents the substring that you want to find.
Compare (optional): This parameter represents the type of comparison to perform. It can be one of the following values:
- vbBinaryCompare (compare as 0): This compare field represents the binary comparison that can also be entered as zero (0). It is a case-sensitive search (‘XYZ’ will be different from small case ‘xyx’) of the substring in the actual string.
- vbTextCompare (compare as 1): This is a textual comparison and can be entered as one (1). It is a case-insensitive search of the “string 2” in the “string 1.”
- vbDatabaseCompare (compare as 2): This can be entered as two (2). It compares based on the data of the MS Access database.
Points to Remember:
- Many user mistakes INSTR function as worksheet function. But that not true as you cannot use it within the worksheet because it is a VBA function and not a worksheet function.
- If the argument String2 is omitted or empty, this function would return the value of the [Start] parameter.
- If the InStr function cannot locate the substring within the primary string, it would return 0.
Examples
INSTR Example 1: INSTR function to find the position of ‘o’ in the string ‘VBA World’.
Using the INSTR function we will find the position of ‘o’ in the string ‘VBA World’ from the beginning.
Below given are the steps to write a VBA macro to find the position of the character ‘o’ in the supplied string:
Step 1: Go to the VBA developer tab either by clicking the shortcut keywords Alt +F11 or click on developer window -> visual basic editor.
Step 2: The VB Editor will appear. The next step is to create a module. Right-clicking on the VBA Project-> Go to Insert-> Go to Module.
Step 3: The VBA module will be inserted. The Next step is to declare and initialize the variable MyVal with string “VBA World”.
The code will as follows:
Step 4: Next, we will call the InStr function and pass the string in the first parameter and in the second parameter we will specify the substring ‘o’. Later using the MsgBox we will display the output.
Output
Run the code by pressing the F5 key and fetch the result. You will have the following Messagebox in your Excel sheet displaying the output.
As a result you will notice, the message box will display an output of 5.
Program 2: Using INSTR to find the position of the Second Word
Let’s say, you want to find the position of ‘butter’ in the sentence – ‘It’s better to have butter on the butter table’. However, there is a twist as you want the search to begin with the second word instead of the first one.
To solve this program, all you need to do is to change the [Start] parameter to make sure it specifies the position from where the second word starts.
Below given are the steps to write a VBA macro to find the position of the character ‘o’ in the supplied string:
Step 1: Go to the VBA developer tab either by clicking the shortcut keywords Alt +F11 or click on developer window -> visual basic editor.
Step 2: The VB Editor will appear. The next step is to create a module. Right-clicking on the VBA Project-> Go to Insert-> Go to Module.
Step 3: The VBA module will be inserted. The Next step is to declare and initialize the variable position. In this variable, we will store the position of the substring butter.
The code will as follows:
Step 4: Next, we will call the InStr function and pass the [start] argument as 4, in the second parameter we will specify the string and in the third parameter we will specify the substring ‘butter’. Later using the MsgBox we will display the output.
Output
Run the code by pressing the F5 key and fetch the result. You will have the following Messagebox in your Excel sheet displaying the output.
The macro will return the message box displaying with the value 22 as we have specified the starting position as 4. Hence it will ignore the first occurrence ‘butter’ and locate the second occurrence of word ‘butter’ in the string.
InStr Example 3: Find the occurrence of @ in specified email id in Excel worksheet.
In this macro code, instead of writing the entire macro, we will code a macro function that will be used later in our Excel worksheet.
Following are the below steps to implement a VBA function to find the occurrence of the character ‘@’ in the supplied string:
Step 1: Go to the VBA developer tab either by clicking the shortcut keywords Alt +F11 or click on developer window -> visual basic editor.
Step 2: The VB Editor will appear. The next step is to create a module. Right-clicking on the VBA Project-> Go to Insert-> Go to Module.
Step 3: Next, we will create a custom function as any other Excel worksheet function. Define the Function as an integer value and declare your variable.
Code:
Step 4: Inside the InStr function, pass your string as Ref, the substring as ‘@’. Pass the integer ValPos to the function LocatePos. This function will take a cell reference as input and give you the position of @ in it.
Code:
Output
Open your Excel worksheet, and you will notice the LocatePos is created as a custom function. Use the function, pass the string and it will return the position of the substring ‘@’.
Refer to the below output:
