VBA - Instr



The InStr Function returns the first occurrence of one string within another string. The search happens from the left to the right.

Syntax

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

Parameter Description

  • Start − An optional parameter. Specifies the starting position for the search. The search begins at the first position from the left to the right.

  • String1 − A required parameter. String to be searched.

  • String2 − A required parameter. String against which String1 is searched.

  • Compare − An optional parameter. Specifies the string comparison to be used. It can take the following mentioned values.

    • 0 = vbBinaryCompare - Performs Binary Comparison (Default)

    • 1 = vbTextCompare - Performs Text Comparison

Example

Add a button and add the following function.

Private Sub Constant_demo_Click() 
   Dim Var As Variant 
   Var = "Microsoft VBScript" 
   MsgBox ("Line 1 : " & InStr(1, Var, "s")) 
   MsgBox ("Line 2 : " & InStr(7, Var, "s")) 
   MsgBox ("Line 3 : " & InStr(1, Var, "f", 1)) 
   MsgBox ("Line 4 : " & InStr(1, Var, "t", 0)) 
   MsgBox ("Line 5 : " & InStr(1, Var, "i")) 
   MsgBox ("Line 6 : " & InStr(7, Var, "i")) 
   MsgBox ("Line 7 : " & InStr(Var, "VB")) 
End Sub 

When you execute the above function, it produces the following output.

Line 1 : 6
Line 2 : 0
Line 3 : 8
Line 4 : 9
Line 5 : 2
Line 6 : 16
Line 7 : 11
vba_strings.htm
Advertisements