EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home VBA VBA Resources VBA String and Text Functions VBA InStrRev

VBA InStrRev

By Madhuri ThakurMadhuri 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
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, 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
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*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 Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more