VBA - Strings | Shout4Education - Get Jobs, Tutorials and Notes

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

Shout4Education Rebrand To myTechMint Click Here To Visit

$show=home

VBA - Strings

Syntax

Examples

String Functions

VBA - Instr

Syntax

Parameter Description

Example

VBA -InString Reverse

Syntax

Parameter Description

Example

VBA - Lcase

Syntax

Example

VBA - UCase

Syntax

Example

VBA - Left

Syntax

Parameter Description

Example

VBA - Right

Syntax

Parameter Description

Example

VBA - Mid

Syntax

Parameter Description

VBA - Ltrim

Syntax

Example

VBA - Rtrim

Syntax

Example

VBA - Trim

Syntax

Example

VBA - Len

Syntax

Example

VBA - Replace

Syntax

Parameter Description

Example

VBA - Space

Syntax

Parameter Description

Example

VBA - strComp

Syntax

Parameter Description

Example

VBA - String Function

Syntax

Parameter Description

Example

VBA - String Reverse Function

Syntax

Example

Comments

Labels

Social$type=social_icons