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.

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

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

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

The InStrRev function returns the first occurrence of one string within another string. The Search happens from the right to the left.

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

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

The LCase function returns the string after converting the entered string into lower case letters.

Lcase(String)

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

The UCase function returns the string after converting the entered string into UPPER case letters.

UCase(String)

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

The Left function returns a specified number of characters from the left side of the given input string.

Left(String, Length)

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

The Right function returns a specified number of characters from the right side of the given input string.

Right(String, Length)

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

The Mid Function returns a specified number of characters from a given input string.

Mid(String,start[,Length])

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

The Ltrim function removes the blank spaces from the left side of the string.

LTrim(String)

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

The Rtrim function removes the blank spaces from the right side of the string.

RTrim(String)

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

The Trim function removes both the leading and the trailing blank spaces of the given input string.

Trim(String)

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

The Len function returns the length of the given input string including the blank spaces.

Len(String)

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

The Replace function replaces a specified part of a string with a specific string, a specified number of times..

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

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

The Space function fills a string with a specific number of spaces.

space(number)

Private Sub Constant_demo_Click()
Dim var1 as Variant
var1 = "Microsoft"
Dim var2 as Variant
var2 = "VBScript"
msgbox(var1 & Space(2)& var2)
End Sub

The StrComp function returns an integer value after comparing the two given strings. It can return any of the three values -1, 0, or 1 based on the input strings to be compared.

• 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

StrComp(string1,string2[,compare])

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",0))
End Sub

The String function fills a string with the specified character for specified number of times.

String(number,character)

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

The StrReverse function reverses the specified string.

StrReverse(string)

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