VBA – Strings

Strings are a sequence of characters, which can consist of either alphabets, numbers, special characters, or all of them. A variable is said to be a string if it is enclosed within double quotes ” “.

variablename = "string"

str1 = "string"   ' Only Alphabets
str2 = "132.45"   ' Only Numbers
str3 = "!@#$;*"  ' Only Special Characters
Str4 = "Asc23@#"  ' Has all the above

There are predefined VBA String functions, which help the developers to work with the strings very effectively. Following are String methods that are supported in VBA. Please click on each one of the methods to know in detail.

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

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", )) 
   MsgBox ("Line 5 : " & InStr(1, Var, "i")) 
   MsgBox ("Line 6 : " & InStr(7, Var, "i")) 
   MsgBox ("Line 7 : " & InStr(Var, "VB")) 
End Sub

Line 1 : 6
Line 2 : 0
Line 3 : 8
Line 4 : 9
Line 5 : 2
Line 6 : 16
Line 7 : 11

Syntax

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

Parameter Description

• String1 − A required parameter. String to be searched.
• String2 − A required parameter. String against which String1 is searched.
• Start − An optional parameter. Specifies the starting position for the search. The search begins at the first position from the right to the left.
• 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

Private Sub Constant_demo_Click()
   var = "Microsoft VBScript"
   msgbox("Line 1 : " & InStrRev(var,"s",10))
   msgbox("Line 2 : " & InStrRev(var,"s",7))
   msgbox("Line 3 : " & InStrRev(var,"f",-1,1))
   msgbox("Line 4 : " & InStrRev(var,"t",5))
   msgbox("Line 5 : " & InStrRev(var,"i",7))
   msgbox("Line 6 : " & InStrRev(var,"i",7))
   msgbox("Line 7 : " & InStrRev(var,"VB",1))
End Sub

Line 1 : 6
Line 2 : 6
Line 3 : 8
Line 4 : 0
Line 5 : 2
Line 6 : 2
Line 7 : 0

Syntax

Lcase(String)

Example

Private Sub Constant_demo_Click()
   var = "Microsoft VBScript"
   msgbox("Line 1 : " & LCase(var))

 

var = "MS VBSCRIPT"
   msgbox("Line 2 : " & LCase(var))

 

var = "microsoft"
   msgbox("Line 3 : " & LCase(var))
End Sub

Line 1 : microsoft vbscript
Line 2 : ms vbscript
Line 3 : microsoft

Syntax

UCase(String)

Example

Private Sub Constant_demo_Click()
   var = "Microsoft VBScript"
   msgbox("Line 1 : " & UCase(var))

 

var = "MS VBSCRIPT"
   msgbox("Line 2 : " & UCase(var))

 

var = "microsoft"
   msgbox("Line 3 : " & UCase(var))
End Sub

Line 1 : MICROSOFT VBSCRIPT
Line 2 : MS VBSCRIPT
Line 3 : MICROSOFT

Syntax

Left(String, Length)

Parameter Description

• String − A required parameter. Input String from which the specified number of characters to be returned from the left side.
• Length − A required parameter. An Integer, which specifies the number of characters to be returned.

Example

Private Sub Constant_demo_Click()
   Dim var as Variant

 

var = "Microsoft VBScript"
   msgbox("Line 1 : " & Left(var,2))

 

var = "MS VBSCRIPT"
   msgbox("Line 2 : " & Left(var,5))

 

var = "microsoft"
   msgbox("Line 3 : " & Left(var,9))
End Sub

Line 1 : Mi
Line 2 : MS VB
Line 3 : microsoft

Syntax

Right(String, Length)

Parameter Description

• String − A required parameter. Input String from which the specified number of characters to be returned from the right side.
• Length − A required parameter. An Integer, which Specifies the number of characters to be returned.

Example

Private Sub Constant_demo_Click()
   var = "Microsoft VBScript"
   msgbox("Line 1 : " & Right(var,2))

 

var = "MS VBSCRIPT"
   msgbox("Line 2 : " & Right(var,5))

 

var = "microsoft"
   msgbox("Line 3 : " & Right(var,9))
End Sub

Line 1 : pt
Line 2 : CRIPT
Line 3 : microsoft

Syntax

Mid(String,start[,Length])

Parameter Description

• String − A required parameter. Input String from which the specified number of characters to be returned.
• Start − A required parameter. An Integer, which specifies the starting position of the string.
• Length − An optional parameter. An Integer, which specifies the number of characters to be returned.

Private Sub Constant_demo_Click()
   Dim var as Variant
   var = "Microsoft VBScript"
   msgbox("Line 1 : " & Mid(var,2))
   msgbox("Line 2 : " & Mid(var,2,5))
   msgbox("Line 3 : " & Mid(var,5,7))
End Sub

Line 1 : icrosoft VBScript
Line 2 : icros
Line 3 : osoft V

Syntax

LTrim(String)

Example

Private Sub Constant_demo_Click()
   Dim var as Variant
   var =       "             Microsoft VBScript"
   msgbox "After Ltrim : " & LTrim(var)
End Sub

After Ltrim : Microsoft VBScript

Syntax

RTrim(String)

Example

Private Sub Constant_demo_Click()
   Dim var as Variant
   var =       "Microsoft VBScript           "
   msgbox("After Rtrim : " & RTrim(var))
End Sub

After Rtrim : Microsoft VBScript

Syntax

Trim(String)

Example

Private Sub Constant_demo_Click()
   var = "Microsoft VBScript"
   var =       "            Microsoft VBScript           "
   msgbox ("After Trim : " & Trim(var))
End Sub

After trim : Microsoft VBScript

Syntax

Len(String)

Example

Private Sub Constant_demo_Click()
   Dim var1 as Variant
   Dim var2 as Variant

var1

="Microsoft VBScript"
   msgbox("Length of var1 is : " & Len(var1))

var2

=       "       Microsoft VBScript           "
   msgbox ("Length of var2 is : " & Len(var2))
End Sub

Length of var1 is : 18
Length of var2 is : 36

Syntax

Replace(string,find,replacewith[,start[,count[,compare]]])

Parameter Description

• String − A required parameter. The Input String which is to be searched for replacing.
• Find − A required parameter. The part of the string that will be replaced.
• Replacewith − A required parameter. The replacement string, which would be replaced against the find parameter.
• Start − An optional parameter. Specifies the start position from where the string has to be searched and replaced. Default value is 1.
• Count − An optional parameter. Specifies the number of times the replacement has to be performed.
• Compare − An optional parameter. Specifies the comparison method to be used. Default value is 0.
  • 0 = vbBinaryCompare – Performs a binary comparison
  • 1 = vbTextCompare – Performs a Textual comparison

Example

Private Sub Constant_demo_Click()
   Dim var as Variant
   var = "This is VBScript Programming"

 

'VBScript to be replaced by MS VBScript
   msgbox("Line 1: " & Replace(var,"VBScript","MS VBScript"))

‘

VB to be replaced by vb
   msgbox("Line 2: " & Replace(var,"VB","vb"))

 

''is' replaced by ##
   msgbox("Line 3: " & Replace(var,"is","##"))

”is’

 replaced by ## ignores the characters before the first occurence
   msgbox("Line 4: " & Replace(var,"is","##",5))

 

''s' is replaced by ## for the next 2 occurences.
   msgbox("Line 5: " & Replace(var,"s","##",1,2))

”r’

 is replaced by ## for all occurences textual comparison.
   msgbox("Line 6: " & Replace(var,"r","##",1,-1,1))

 

''t' is replaced by ## for all occurences Binary comparison
   msgbox("Line 7: " & Replace(var,"t","##",1,-1,0))

End Sub

Line 1: This is MS VBScript Programming
Line 2: This is vbScript Programming
Line 3: Th## ## VBScript Programming
Line 4: ## VBScript Programming
Line 5: Thi## i## VBScript Programming
Line 6: This is VBSc##ipt P##og##amming
Line 7: This is VBScrip## Programming

Syntax

space(number)

Parameter Description

Example

Private Sub Constant_demo_Click()
   Dim var1 as Variant

var1

= "Microsoft"
   Dim var2 as Variant

var2

= "VBScript"
   msgbox(var1 & Space(2)& var2)
End Sub

Microsoft VBScript

• If String 1 < String 2, then StrComp returns -1
• If String 1 = String 2, then StrComp returns 0
• If String 1 > String 2, then StrComp returns 1

Syntax

StrComp(string1,string2[,compare])

Parameter Description

• String1 − A required parameter. The first string expression.
• String2 − A required parameter. The second string expression.
• Compare − An optional parameter. Specifies the string comparison to be used. It can take the following values.
  • 0 = vbBinaryCompare – Performs Binary Comparison(Default)
  • 1 = vbTextCompare – Performs Text Comparison

Example

Private Sub Constant_demo_Click()
   Dim var1 as Variant
   msgbox("Line 1 :" & StrComp("Microsoft","Microsoft"))
   msgbox("Line 2 :" &StrComp("Microsoft","MICROSOFT"))
   msgbox("Line 3 :" &StrComp("Microsoft","MiCrOsOfT"))
   msgbox("Line 4 :" &StrComp("Microsoft","MiCrOsOfT",1))
   msgbox("Line 5 :" &StrComp("Microsoft","MiCrOsOfT",))
End Sub

Line 1 :0
Line 2 :1
Line 3 :1
Line 4 :0
Line 5 :1

Syntax

String(number,character)

Parameter Description

• Number − A required parameter. An integer value, which would be repeated for a specified number of times against the character parameter.
• Character − A required parameter. Character value, which has to be repeated for a specified number of times.

Example

Private Sub Constant_demo_Click()
   msgbox("Line 1 :" & String(3,"$"))
   msgbox("Line 2 :" & String(4,"*"))
   msgbox("Line 3 :" & String(5,100))
   msgbox("Line 4 :" & String(6,"ABCDE"))
End Sub

Line 1 :$$$
Line 2 :****
Line 3 :ddddd
Line 4 :AAAAAA

Syntax

StrReverse(string)

Example

Private Sub Constant_demo_Click()
   msgbox("Line 1 : " & StrReverse("VBSCRIPT"))
   msgbox("Line 2 : " & StrReverse("My First VBScript"))
   msgbox("Line 3 : " & StrReverse("123.45"))
End Sub

Line 1 : TPIRCSBV
Line 2 : tpircSBV tsriF yM
Line 3 : 54.321