VBA InStr Tutorial

VBA Searching strings using InStr and InStrRev

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)
Loading...

String manipulation is a key skill in VBA and other programming languages. Today let’s focus on using string searching functions InStr and InStrRev. Sometimes you need to search for substrings within other string variables for various reasons. It is important to know the tools you have in place to assist you with such tasks.

VBA InStr

The VBA InStr function finds the position of a substring matched within a certain string. Returns 0 if substring not found.

VBA InStr
VBA InStr

InStr is a very powerful and useful function when having to locate substrings withing another text string.

InStr Example

Let assume a simple text string “Hello There!”. Remember that VBA indexes strings starting at index 1:

'Hi There!
'123456789

Find substring “There”:

Debug.Print InStr("Hi There!", "There") 
'Result: 4

Find second occurance of character “e” (first “e” is on position 6):

Debug.Print InStr(7, "Hi There!", "There") 
'Result: 8

VBA InStrRev

The VBA InStrRev function finds the position of a substring matched within a certain string, starting from the end of the string. The position from the string is counted from the left like for the InStr function. Returns 0 if substring not found.

VBA IntStrRev
VBA IntStrRev

InStrRev Example

Let assume a simple text string with multiple occurances”Hi There! You There!”. Remember that VBA indexes strings starting at index 1:

Find the last occurance of the substring “There”:

Debug.Print InStrRev("Hi There! You There!", "There") 
'Result: 15

Find second last occurance of substring “There”:

Debug.Print InStr("Hi There! You There!", "There", 18) 
'Result: 4

Why in the second example this I provide 18 as the position to start from instead of say 14? That’s because looking from the back There starts at position 19. Look at the image below:

InStrRev explained
InStrRev explained

InStr Comparison types

There is some confusion with the Comparison Types when dealing with InStr and InStrRev functions. What’s the difference?

vbBinaryCompare

This option compares each character in a string match exactly as according to it’s binary value. What does this translate to? Letter case will be distinguished (small from capital letters). See example below:

Debug.Print InStr(1, "Hi There!", "There", vbBinaryCompare)
'Result: 4

Debug.Print InStr(1, "Hi There!", "there", vbBinaryCompare)
'Result: 0 (not found)

vbTextCompare

This option ignores letter case (small vs. capital letters):

Debug.Print InStr(1, "Hi There!", "There", vbTextCompare)
'Result: 4

Debug.Print InStr(1, "Hi There!", "there", vbTextCompare)
'Result: 4

Related Posts

Leave a Reply