VBA InStrRev

VBA InStrRev Function

Knowing the occurrence of a string in another string can be very handy while working with day to day data. Obviously, we can do it manually by calculating the occurrence of the string in another string but that would the task very hefty. So to make it easier we have a function in VBA which is known as INSTRREV which is used to find the occurrence.

As explained above, INSTRREV in Excel VBA is used to find an occurrence of a string in another string. This function finds the first occurrence of a string in the target string and returns the value. Now we have to remember that as it gives the occurrence of the string so the returned value is numeric. Also as it is a comparison function so like other functions in VBA there are three basic comparisons methods.

Syntax of InStrRev in Excel VBA

The syntax for VBA InStrRev function in excel is as follows:

Now let us break down the syntax and learn about it, String is the main string from where we want to find the occurrence of a substring, Start is the numeric occurrence we provide to the string. If no start parameter is provided the function starts looking a string from the end of it. And compare is the comparison method we provide to the function. There are three types of comparison for this function:

1. To use Option Compare which is (-1). It is also known as VbUseCompareOption.
2. To use Binary Compare which is (0). It is also known as VbBinaryCompare.
3. To use Text Compare which is (1). It is also known as VbTextCompare.

Again if none of the compare options is provided then the function automatically considers it as a binary compare.

Now let us use this function in a few examples and look at how to use this function.

How to Use Excel VBA InStrRev?

Now let us try with some examples on VBA InStrRev in Excel.

Example #1 – VBA InStrRev

Let us take for an example that our string is “ I am a Good Boy” and find the occurrence of character “ “ which is space.

Step 1: Now before we move into VBA, first enable VBA then go to the code section in the developer’s section to click on visual basic.

Step 2: Once we enter the VB editor we can see in the header section, there is an option of insert. Insert a new module from that option as shown below.

Step 3: Now let us start our subprocedure in the module as shown below.

Code:

Sub Sample()

End Sub

Step 4: Now declare a variable as an integer which will hold the output value of the function for us.

Code:

Sub Sample()

Dim A As Integer

End Sub

Step 5: Now in the variable use the INSTRREV function to find the occurrence of “ “ in the string “ I am a Good Boy” as follows.

Code:

Sub Sample()

Dim A As Integer
A = InStrRev(" I am a Good Boy", " ")

End Sub

Step 6: Now display the value stored in variable A using the msgbox function.

Code:

Sub Sample()

Dim A As Integer
A = InStrRev(" I am a Good Boy", " ")
MsgBox A

End Sub

Step 7: Let us execute the above code to get the following result.

We get the result as 13 because we did not provide the start position to the function so it automatically calculated the occurrence from the end and so the result. It is found that “ “ is on the 13th position of the string when we search it from the end.

Example #2 – VBA InStrRev

In the above example, we did not provide any start position to the string. Let us provide this time in this example. Let us find out from the second position where does the “ “ occurs in the string.

Step 1: Insert a new module from that option as shown below.

Step 2: Let us again define a subprocedure for our second example.

Code:

Sub Sample1()

End Sub

Step 3: Declare another integer variable for the example.

Code:

Sub Sample1()

Dim A As Integer

End Sub

Step 4: Now in Variable A let us find the occurrence of the “ “ from the second position using the INSTRREV function as follows.

Code:

Sub Sample1()

Dim A As Integer
A = InStrRev(" I am a Good Boy", " ", 2)

End Sub

Step 5: Now use msgbox function to display the value stored in A.

Code:

Sub Sample1()

Dim A As Integer
A = InStrRev(" I am a Good Boy", " ", 2)
MsgBox A

End Sub

Step 6: Now run the above code to find out the below result as shown below,

We get 1 as a result as we count 2 we get I and after one position we get the occurrence of “ “.

Example #3 – VBA InStrRev

In this example let us use the compare methods. We have a string “ India is the Best” and let us find the string “E” using both text and binary compare methods.

Step 1: In the same module 1, write another subprocedure for example 3.

Code:

Sub Sample2()

End Sub

Step 2: Let us define two variables as Integer which will hold the value for the occurrence of the string E in both Text and Binary comparison respectively.

Code:

Sub Sample2()

Dim A, B As Integer

End Sub

Step 3: In variable A let us use the INSTRREV function with the text comparison as follows.

Code:

Sub Sample2()

Dim A, B As Integer
A = InStrRev("India is the Best", "E", , vbTextCompare)

End Sub

Step 4: Now display the value stored in A using the msgbox function.

Code:

Sub Sample2()

Dim A, B As Integer
A = InStrRev("India is the Best", "E", , vbTextCompare)
MsgBox A

End Sub

Step 5: In variable B let’s use the binary comparison for the same string as follows.

Code:

Sub Sample2()

Dim A, B As Integer
A = InStrRev("India is the Best", "E", , vbTextCompare)
MsgBox A
B = InStrRev("India is the Best", "E", , vbBinaryCompare)
MsgBox B

End Sub

Step 6: Execute the above code to find the first result stored in variable A which is as follows.

Step 7: Press OK to see the result stored in variable B.

We get 0 as the result for binary compare because in our string “e” is present not “E”. In binary values both of these are different. So if a value is not found in the string we get a result as 0.

Things to Remember

• The value returned by this function is numeric.
• If the substring is not found the value returned is 0.
• Start position is optional. If it is not provided, by default function search the occurrence from the end of the string.
• The comparison methods are also optional.

Recommended Articles

This is a guide to VBA InStrRev. Here we discuss how to use Excel VBA InStrRev along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Active Cell
2. VBA Delete Row
3. VBA Transpose
4. VBA RGB