As a programmer, I often manipulate texts and strings to perform various operations. In Visual Basic for Applications (VBA), one function I find particularly handy is the InStrRev function. This nifty tool does something quite simple yet powerful: it helps you locate the starting position of a specific substring within another string, starting from the end and moving to the beginning. It’s especially useful when you need to find elements or perform operations from the end of a string, such as file paths or last names when they follow a comma in a list.

The beauty of InStrRev lies in its precision and flexibility. It performs a task that’s somewhat opposite to what the commonly known InStr function does, which searches from the start of the string. With InStrRev, if you know the end portion of the string you’re dealing with, you can quickly isolate the piece you need without having to step through the string from the beginning. This becomes incredibly time-saving when working with longer strings or when the position of the substring from the end is what actually matters in your code logic.

Syntax

Here’s how I use InStrRev:

  • StringCheck: The string I’m searching within.
  • StringMatch: The substring I’m looking for.
  • Start: (Optional) Where I start the search from.
  • Compare: (Optional) The type of comparison.

Arguments

When I’m using the InstrRev function in VBA, there are specific pieces of data, or arguments, that I need to provide. Here’s what each argument means:

  • StringCheck: This is the text where I’ll hunt for a certain pattern or keyword.
  • StringMatch: The exact phrase I’m trying to find within StringCheck.

And there are a couple of additional details I might include to refine my search:

  • Start (Optional): Dictates the starting point for the search, counted from the right. If I don’t specify anything here, it kicks off at the last character.
  • Compare (Optional): Sets the ground rules for how the search behaves. The default is vbBinaryCompare, but I often use vbTextCompare for case-insensitive searches. Rarely, I might need vbDatabaseCompare for specialized database collations.

With these, InstrRev finds where, from right to left, StringMatch last shows up within StringCheck. If there’s no match, I get a zero. It’s pretty handy for parsing strings in reverse, especially when dealing with filenames in a path or when I just want the last occurrence of a substring.

Example

I once had to use VBA code to figure out the position of a space in a string. Here’s the code snippet I used:

Sub example_INSTRREV()
    Range("B1").Value = InStrRev(Range("A1"), " ")
End Sub

In this example, InStrRev function checked the cell A1, which had the phrase “Excel Champs”. It returned the number 6 because that’s where the first space appeared between “Excel” and “Champs”.

Notes

  • When I use “stringcheck” or “stringmatch”, if either is NULL, the outcome’s NULL too.
  • Can’t find “stringmatch” in “stringcheck”? That’s a 0 for me.
  • Remember, VBA isn’t case-sensitive.
  • By default, I start looking from the very end, right before the last character.
  • No special value needed for Excel 2007; it works the same as newer versions.
  • Hunting for the second to last space can be tricky—got to count carefully!

Similar Posts