Learn about VBA Instr Function

Learn about VBA Instr Function

In This Article, we will learn about following topic of InStr function in Excel VBA with Practical Example.

1.Introduction of InStr Functino

2. VBA code for Basic understanding

3. VBA Code for Case-Sensitive Search

4. VBA Code for Conditional Logic

5. VBA Code for Count How Many Times a Substring Appears

Introduction

The InStr function in VBA is used to search for the occurrence of one string (substring) within another string. It returns the position (as an integer) where the substring is first found. If the substring is not found, the function returns 0

The InStr function in VBA is a search tool that helps find the location of a specific text (substring) within a larger text (main string). Think of it as a way to ask, “Where does this word or character appear in this sentence?”

Syntax

InStr([start], string1, string2, [compare])

Parameters

1. start (Optional):
   1. The position in string1 where the search begins. The default is 1 (start of the string).
   2. If omitted, the search starts from the beginning of string1.
2. string1:
   1. The string to be searched.
3. string2:
   1. The substring to find within string1.
4. compare (Optional):
   1. Specifies the type of comparison.
   2. Options:
      • vbBinaryCompare (Default): Performs a case-sensitive search.
      • vbTextCompare: Performs a case-insensitive search.
      • vbDatabaseCompare: Available only in Microsoft Access (compares based on the database locale).

Return Value

• Integer:
  1. Position of the first occurrence of string2 in string1.
  2. Returns 0 if string2 is not found.

Example

1. Basic use

VBA Code:

Sub InStrExample()

Dim position As Integer

position = InStr("Hello World", "World")

MsgBox position ' Output: 7 (Position where "World" starts)

End Sub

1. Case SenSetive Search (default)

VBA Code

Sub InStrExample()

Dim position As Integer

position = InStr("Hello World", "world") ' in this searching word is mentioned in small letter so due to erro

MsgBox position ' Output: 0 (due to small letter of "world")

End Sub

2. Case-Sensitive Search

VBA Code

Sub InStrExample_3()

Dim position As Integer

position = InStr("Hello World", "world", vbTextCompare)

MsgBox position

'Output: 10 (due to use of bianary word : vbTextCompare, if search is in small letter, result will be accurate)

End Sub

3. Specify Start Position

VBA Code:

Sub instrExample_4()

Dim position As Integer

position = InStr(8, "hello miss hello", "hello")

MsgBox position

'hello start at the position 12

End Sub

4. Using Instr with Conditional Logic

VBA Code

Sub usinginstrconditional()

Dim text As String

text = "VBA is Powerful"

If InStr(text, "Powerful") > 0 Then

MsgBox "Ther word powerful find"

Else

MsgBox "The Word not find"

End If

End Sub

5. Extract Text After a Specific Character

Suppose you have “Name: John” and want to extract “John”:

VBA Code

Sub Extract1()

Dim fulltext As String

Dim result As String

fulltext = "Name:John"

Dim position As Integer

position = InStr(fulltext, ":")

If position > 0 Then

result = Mid(fulltext, position)

MsgBox result

Else

MsgBox "Colon not found"

End If

End Sub

6. Count How Many Times a Substring Appears

Find how many times “apple” appears in a sentence:

VBA Code:

Sub CountOccurrences()

Dim text As String

Dim position As Integer

Dim count As Integer




text = "apple orange apple banana apple"

position = 0

count = 0

Do

position = InStr(position + 1, text, "apple") ' Find "apple" starting from the next position

If position > 0 Then count = count + 1

Loop Until position = 0




MsgBox "The word 'apple' appears " & count & " times."

End Sub

Best Practices

• Always specify the compare parameter if the case-sensitivity of the search is critical.
• Use the start parameter to limit the search range for better performance on large strings.
• Combine with functions like Mid or Left for more complex string manipulations.

Key Points to Remember

1. Case-Sensitivity:
   • By default, InStr is case-sensitive unless you specify vbTextCompare.
2. Zero-Based Indexing:
   • Unlike some languages, VBA uses 1-based indexing. Position 1 is the first character.
3. Error Handling:
   • Ensure your code handles cases where InStr returns 0 (substring not found).

By mastering the InStr function, you can efficiently search and manipulate text data in Excel VBA.

Summary

• The InStr function is a powerful tool for finding and manipulating text in VBA.
• Use it to locate text, verify existence, and work with substrings.
• By combining it with other functions like Mid, Left, and Right, you can handle advanced string operations effectively.

Other topic to Read

1. VBA String Functions : Excel VBA provides a variety of string functions that help manipulate and analyze text.
2. VBA Functions : VBA (Visual Basic for Applications) functions are used to perform operations in Microsoft Office applications like Excel, Access, and Word. These functions can be built-in or user-defined
3. VBA If Then Else : The If…Then…Else statement in VBA is used to execute different blocks of code based on whether a condition is True or False. It is one of the fundamental control structures used to introduce conditional logic into your code, allowing different actions to be taken depending on the values of variables or expressions
4. VBA Do While Loop : A Do…While loop is used when we want to repeat a set of statements as long as the condition is true. The condition may be checked at the beginning of the loop or at the end of the loop.

5. Instr Function: Returns a Variant (Long) specifying the position of the first occurrence of one string within another

Excel File for Download