InStr vs InStrRev functions in VBA example

InStr vs InStrRev functions in VBA example

InStr vs InStrRev

Both functions works to find the first occurrence of a word given under search criteria in a string. If value found a valid index would be return which will represent > 0 if no match then it returns 0. The difference between InStr and InStrRev is search direction.

InStr Syntax

InStr(Start, String1, String2, CompareMethod)

  • Start: represents a valid positive integer from where you would like to start search in given string
  • String1: refers to the string on which search needs to be performed
  • String2: refers to word that you would like to search within String1
  • CompareMethod: there are three types of compare methods:
    • vbTextCompare : performs text to text compare, integer constant value is 1
    • vbDatabaseCompare: performs database value comparison, integer constant value is 2
    • vbBinaryCompare: performs binary comparison, integer constant value is 0

InStrRev Syntax

InStrRev(StringCheck, StringMatch, Start,CompareMethod)

  • StringCheck: refers to the string on which search needs to be performed
  • StringMatch: refers to word that you would like to search within StringCheck
  • Start: Optional. Numeric expression that sets the starting position for each search. If omitted, -1 is used, which means that the search begins at the last character position. If start contains Null, an error occurs.
  • CompareMethod: there are three types of compare methods:
    • vbTextCompare : performs text to text compare, integer constant value is 1
    • vbDatabaseCompare: performs database value comparison, integer constant value is 2
    • vbBinaryCompare: performs binary comparison, integer constant value is 0
    • vbUseCompareOption: performs a comparison by using the setting of the Option Compare statement.

Code example

Sub CheckInstr()
    'Declare constant having "testing" words twice
    Const VBAOVERALL_STRING As String = "this is my testing string where testing will happen based on instr function"
    
    'A variable to grab the index
    Dim oResult As Integer
    
    'InStr example
    oResult = InStr(1, VBAOVERALL_STRING, "TTT", vbTextCompare)
    Debug.Print "InStr found testing at " & oResult & " index"
    
    'InStrRev
    oResult = InStrRev(VBAOVERALL_STRING, "testing", -1, vbBinaryCompare)
    Debug.Print "InStrRev found testing at " & oResult & " index"
End Sub

Output

figure 1.0

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.