Home » Tutorials » VBA Tutorials » VBA – Strings

VBA – Strings

VBA - Strings Shout4Education
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 ” “.

Syntax

variablename = "string"

Examples

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

String Functions

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.
Sr.No. Function Name & Description
1 InStr

Returns the first occurrence of the specified substring. Search happens from the left to the right.
2 InstrRev

Returns the first occurrence of the specified substring. Search happens from the right to the left.
3 Lcase

Returns the lower case of the specified string.
4 Ucase

Returns the upper case of the specified string.
5 Left

Returns a specific number of characters from the left side of the string.
6 Right

Returns a specific number of characters from the right side of the string.
7 Mid

Returns a specific number of characters from a string based on the specified parameters.
8 Ltrim

Returns a string after removing the spaces on the left side of the specified string.
9 Rtrim

Returns a string after removing the spaces on the right side of the specified string.
10 Trim

Returns a string value after removing both the leading and the trailing blank spaces.
11 Len

Returns the length of the given string.
12 Replace

Returns a string after replacing a string with another string.
13 Space

Fills a string with the specified number of spaces.
14 StrComp

Returns an integer value after comparing the two specified strings.
15 String

Returns a string with a specified character for specified number of times.
16 StrReverse

Returns a string after reversing the sequence of the characters of the given string.

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 -InString Reverse

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

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

Add a button and place the following function.
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
Upon executing the above script, it produces the following result.
Line 1 : 6
Line 2 : 6
Line 3 : 8
Line 4 : 0
Line 5 : 2
Line 6 : 2
Line 7 : 0

 

VBA – Lcase

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

Syntax

Lcase(String)

Example

Add a button and place the following function inside the same.
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
Upon executing the above script, it produces the following output.
Line 1 : microsoft vbscript
Line 2 : ms vbscript
Line 3 : microsoft

 

VBA – UCase

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

Syntax

UCase(String)

Example

Add a button and place the following function inside the same.
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
Upon executing the above script, it produces the following output.
Line 1 : MICROSOFT VBSCRIPT
Line 2 : MS VBSCRIPT
Line 3 : MICROSOFT

 

VBA – Left

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

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

Add a button and add the following function.
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
When you execute the above function, it produces the following output.
Line 1 : Mi
Line 2 : MS VB
Line 3 : microsoft

VBA – Right

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

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

Add a button and add the following function.
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
When you execute the above function, it produces the following output.
Line 1 : pt
Line 2 : CRIPT
Line 3 : microsoft

VBA – Mid

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

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.
Add a button and add the following function.
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
When you execute the above function, it produces the following output.
Line 1 : icrosoft VBScript
Line 2 : icros
Line 3 : osoft V

 

VBA – Ltrim

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

Syntax

LTrim(String)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim var as Variant
   var =       "             Microsoft VBScript"
   msgbox "After Ltrim : " & LTrim(var)
End Sub
When you execute the function, it produces the following output.
After Ltrim : Microsoft VBScript

VBA – Rtrim

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

Syntax

RTrim(String)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim var as Variant
   var =       "Microsoft VBScript           "
   msgbox("After Rtrim : " & RTrim(var))
End Sub
When you execute the above function, it produces the following output.
After Rtrim : Microsoft VBScript

VBA – Trim

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

Syntax

Trim(String)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   var = "Microsoft VBScript"
   var =       "            Microsoft VBScript           "
   msgbox ("After Trim : " & Trim(var))
End Sub
When you execute the above function, it produces the following output.
After trim : Microsoft VBScript

 

VBA – Len

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

Syntax

Len(String)

Example

Add a button and add the following function.
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
When you execute the above function, it produces the following output.
Length of var1 is : 18
Length of var2 is : 36

 

VBA – Replace

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

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

When you execute the above function, it produces the following output.
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

 

VBA – Space

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

Syntax

space(number)

Parameter Description

Number − A required parameter. The number of spaces that we want to add to the given string.

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
When you execute the above function, it produces the following output.
Microsoft VBScript

 

VBA – strComp

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

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

Add a button and add the following function.
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
When you execute the above function, it produces the following output.
Line 1 :0
Line 2 :1
Line 3 :1
Line 4 :0
Line 5 :1

VBA – String Function

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

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

Add a button and add the following function.
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
When you execute the above function, it produces the following output.
Line 1 :$$$
Line 2 :****
Line 3 :ddddd
Line 4 :AAAAAA

VBA – String Reverse Function

The StrReverse function reverses the specified string.

Syntax

StrReverse(string)

Example

Add a button and add the following function.
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
When you execute the above function, it produces the following output.
Line 1 : TPIRCSBV
Line 2 : tpircSBV tsriF yM
Line 3 : 54.321

 

Leave a Comment