VBA InStrRev Function

The VBA InStrRev function is used to find the position of a specified substring within a string. It is a powerful tool that can save time and effort when working with large amounts of data or when performing complex search operations in VBA.

VBA InStrRev Function – Purpose, Syntax and Arguments

Purpose

The main purpose of the InStrRev function is to search for a specific substring within a string and return its starting position from the end of the string. This function allows for flexibility in searching for substrings in strings as it starts the search from the last character of the string and moves towards the beginning.

Syntax

The syntax for the InStrRev function is as follows:

InStrRev(string, substring[, start[, compare]])

Arguments

• string: Required. The string to be searched.
• substring: Required. The substring to be searched for within the string.
• start: Optional. The position in the string to start the search. If not specified, the search starts at the end of the string.
• compare: Optional. Specifies the type of string comparison to be performed. It can take the values -1, 0, or 1 which represent different types of comparison. If not specified, a binary comparison is performed.

Example

Let’s say we have the following data in a sheet:

Column A
---------
Apple
Pear
Banana
Grapes

We want to search for the starting position of the substring ‘an’ in each of these strings. The following code will use the InStrRev function to perform this search and display the results in column B:

Sub InStrRev_Example()
Dim i As Integer
For i = 1 To 4
    Cells(i, 2).Value = InStrRev(Cells(i, 1).Value, "an")
Next i
End Sub

The result will be as follows:

Column A  |  Column B
---------------------
Apple     |  4
Pear      |  2
Banana    |  4
Grapes    |  3

As we can see, the function returns the starting position of the substring ‘an’ in each of the strings. It starts the search from the end of the string and moves towards the beginning, hence it returns the last ‘an’ in both ‘Apple’ and ‘Banana’.

Remarks

• The InStrRev function is case-sensitive, so the substring must match the string exactly in order for it to be found.
• If the ‘substring’ is not found in the ‘string’, the function returns a value of 0.
• The ‘start’ argument can be a negative number, in which case the search will start from that position in the string counting from the end of the string.
• If the ‘start’ argument is longer than the ‘string’ argument, the function will return a value of 0.
• If the ‘compare’ argument is not specified, the function will use a binary comparison which is case-sensitive. If a different type of comparison is desired, the ‘compare’ argument must be specified.

Important Notes

• The InStrRev function is available in both VBA and VBScript.
• In VBScript, the ‘compare’ argument is not available and a binary comparison is always performed.
• The order of the arguments in the function is different in VBScript and VBA. In VBScript, the ‘substring’ argument comes first, followed by the ‘string’ argument.

The VBA InStrRev function is a useful tool for locating substrings within strings. Its ability to start the search from the end of the string and move towards the beginning makes it a valuable function for efficient string searching and manipulation in VBA. With the knowledge of its syntax, arguments, and usage, the InStrRev function can be a valuable addition to any VBA programmer’s toolkit.

Understanding VBA InStrRev Function with Examples

Example 1: Basic Syntax of InStrRev Function

Description: The InStrRev function is used to find the position of a substring within a larger string, starting from the end of the string.
Code:

InStrRev(string, substring, [start], [compare])

Explanation: To use the InStrRev function, we need to provide the string and substring that we want to search for, along with two optional parameters – start and compare. The start parameter indicates the starting position from which the search should begin and the compare parameter specifies the type of comparison to be performed. If no start parameter is given, the search begins from the end of the string. If no compare parameter is specified, a binary comparison is performed.

Example 2: Using InStrRev with start parameter

Description: In this example, we will use the start parameter to specify the position from which the search should begin.
Code:

InStrRev("Hello World", "l", 4)

Explanation: In this example, we want to find the position of the letter “l” in the string “Hello World”, but we want to start the search from the 4th position. This means that the InStrRev function will ignore the first 3 letters of the string and start the search from the 4th position. The result of this example will be 3, as the first “l” is found at position 3 in “Hello World”.

1. The function searches for the first occurrence of the specified substring, starting from the 4th position in the string.
2. If the substring is found, the position of the first occurrence is returned (in this case, position 3).
3. If the substring is not found, the function returns a value of 0.

Example 3: Using InStrRev with compare parameter

Description: The compare parameter in the InStrRev function allows us to specify the type of comparison to be performed.
Code:

InStrRev("Hello World", "w", , vbTextCompare)

Explanation: In this example, we want to find the position of the letter “w” in the string “Hello World”, but we want to perform a text comparison instead of a binary comparison. This means that the InStrRev function will ignore the case of the letters and still find the position of the substring.

1. The function performs a text comparison and finds the first occurrence of the specified substring.
2. If the substring is found, the position of the first occurrence is returned (in this case, position 6).
3. If the substring is not found, the function returns a value of 0.

Example 4: Using InStrRev with multiple occurrences of the substring

Description: The InStrRev function can also be used to find the position of multiple occurrences of a substring within a string.
Code:

str = "We live in a beautiful world"
positions = ""
do
    i = InStrRev(str, "e")
    if i then
        positions = positions & i & ","
        str = mid(str, 1, i-1)
    end if
loop until i = 0
msgbox positions

Explanation: In this example, we want to find the positions of all the occurrences of the letter “e” in the string “We live in a beautiful world”. This is achieved by using a loop that iterates through the string, using the InStrRev function to find the position of “e” every time. The loop continues until the function returns a value of 0, indicating that no more occurrences of the substring are found. The positions are stored in a variable, which is then displayed in a message box.

1. The loop starts by finding the last occurrence of “e” in the string “We live in a beautiful world”.
2. The position of the last occurrence is added to the positions variable.
3. The string is then reduced by removing all the characters after the last occurrence of “e”.
4. The loop continues until there are no more occurrences of “e” in the string.
5. The final result will be a comma-separated list of positions of all the occurrences of “e” (in this case, 24,15,5).

Example 5: Using InStrRev to check for substrings in a sentence

Description: In this example, we will use the InStrRev function to check if a sentence contains certain substrings.
Code:

sentence = "I love to code in VBA"
if InStrRev(sentence, "love") then
    msgbox "I love coding!"
end if
if InStrRev(sentence, "VBA") then
    msgbox "I am a VBA developer!"
end if

Explanation: In this example, we have a variable called “sentence” which contains the string “I love to code in VBA”. We use the InStrRev function to check if the substring “love” or “VBA” are present in the sentence. If they are present, the respective message box is displayed.

1. The InStrRev function checks for the last occurrence of the substring “love” in the sentence.
2. If the substring is found, the if statement is executed and the first message box is displayed.
3. The InStrRev function then checks for the last occurrence of the substring “VBA” in the sentence.
4. If the substring is found, the if statement is executed and the second message box is displayed.
5. If no occurrences of the substrings are found, the code does not execute any message box.

In conclusion, the InStrRev function is a useful tool in VBA that allows us to find the position of a substring within a string, starting from the end of the string. It also gives us the flexibility to specify the starting position and the type of comparison to be performed. By understanding the basic syntax and some examples of how to use this function, we can improve our coding efficiency and functionality.