EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA String and Text Functions VBA InStrRev
 

VBA InStrRev

Madhuri Thakur
Article byMadhuri Thakur

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of InStrRev in Excel VBA

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

Syntax of VBA InStrRev

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.

You can download this VBA InStrRev Excel Template here – VBA InStrRev Excel Template

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.

VBA INSTRREV (Developer)

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.

VBA INSTRREV (Module)

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

Code:

Sub Sample()

End Sub

VBA InStrRev Example 1.1

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

VBA InStrRev Example 1.2

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

VBA InStrRev Example 1.3

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

Example 1.4

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

VBA InStrRav 1

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.

VBA InStrRev Example 2.1

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

Code:

Sub Sample1()

End Sub

VBA InStrRev Example 2.2

Step 3: Declare another integer variable for the example.

Code:

Sub Sample1()

Dim A As Integer

End Sub

Example 2.3

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

Example 2.4

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

Example 2.6

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

VBA InstrRev 2

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

VBA InStrRev Example 3.1

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

VBA InStrRev Example 3.2

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

VBA InStrRev Example 3.3

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

VBA InStrRev Example 3.4

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

VBA InStrRev Example 3.5

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

Example 3.7

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

Example 3.8

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA InStrRev Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW