VBA Len Function – Get String Length
In this Article
This tutorial will demonstrate how to use the Len VBA function to get the length of a string.
Len Function
The VBA Len function returns the length of a specified string.
VBA Len Count Characters
The VBA Len function counts the characters in a string.
Sub LenExample_1()
MsgBox Len("12345") 'Result is: 5
MsgBox Len("12") 'Result is: 2
MsgBox Len("1") 'Result is: 1
MsgBox Len(" ") 'Result is: 1
'There is a space character in there.
MsgBox Len("") 'Result is: 0
MsgBox Len("AB Cd") 'Result is: 5
End SubVBA Len Strings or Variants
VBA Len Function can count the number of characters in variables declared as strings or variants. Actually, VBA Len will treat a variant as a string. If VBA Len is used with an integer, long, single or double then VBA Len is going to count the number of bytes needed to store the variable.
Sub LenExample_2()
Dim VarEx1 As String
VarEx1 = 12345
MsgBox Len(VarEx1) 'Result is: 5
'Len is counting the number of characters in variable
Dim VarEx2 As Variant
VarEx2 = 12345
MsgBox Len(VarEx2) 'Result is: 5
'Len is counting the number of characters in variable
Dim VarEx3 As Integer
VarEx3 = 12345
MsgBox Len(VarEx3) 'Result is: 2
'Len is counting the number of bytes used to store the variable
Dim VarEx4 As Long
VarEx4 = 12345
MsgBox Len(VarEx4) 'Result is: 2
'Len is counting the number of bytes used to store the variable
Dim VarEx5 As Single
VarEx5 = 12345
MsgBox Len(VarEx5) 'Result is: 2
'Len is counting the number of bytes used to store the variable
Dim VarEx6 As Double
VarEx6 = 12345
MsgBox Len(VarEx6) 'Result is: 2
'Len is counting the number of bytes used to store the variable
End SubVBA Len Count Occurrences of a Character
VBA Len function can be used with VBA Replace function to count how many times a character is found in a string.
VBA Replace Function can replace a substring with another substring in a text:
MsgBox Replace("XBCX", "X", "7") 'Result is: "7BC7"We can use Replace to remove the characters we want to count with “” and then find the difference in length before and after the replacement.
Sub LenExample_3()
Dim StrEx As String 'Define a string variable
StrEx = "Jack,John,Jim,Jordan"
MsgBox Len(StrEx) - Len(Replace(StrEx, ",", "")) 'Result is: 3
'Breaking down the code above
MsgBox Len(StrEx) 'Result is: 20
MsgBox Replace(StrEx, ",", "") 'Result is: "JackJohnJimJordan"
MsgBox Len(Replace(StrEx, ",", "")) 'Result is: 17
MsgBox Len(StrEx) - Len(Replace(StrEx, ",", "")) 'Result is: 20-17=3
End SubVBA Len Count Occurrences of a Substring
VBA Len function can be used with VBA Replace function to count how many times a substring is found in a string.
VBA Replace Function can replace a substring with another substring in a text:
MsgBox Replace("XB cX", "X", "7") 'Result is: "7B c7"We can use Replace to remove the substrings we want to count with “” and then find the difference in length before and after the replacement. Finally, we need to divide the difference with the length of the substring we replaced.
Sub LenExample_4()
Dim StrEx As String 'Define a string variable
StrEx = "Jack, John, Jim, Jordan"
Dim SubStr As String 'Define a substring variable
SubStr = ", "
'We will find how many times SubStr is found inside StrEx
MsgBox (Len(StrEx) - Len(Replace(StrEx, SubStr, ""))) / Len(SubStr) 'Result is: 3
'Breaking down the code above
MsgBox Len(StrEx) 'Result is: 23
MsgBox Replace(StrEx, SubStr, "") 'Result is: "JackJohnJimJordan"
MsgBox Len(Replace(StrEx, SubStr, "")) 'Result is: 17
MsgBox Len(StrEx) - Len(Replace(StrEx, SubStr, "")) 'Result is: 23-17=6
MsgBox (Len(StrEx) - Len(Replace(StrEx, SubStr, ""))) / Len(SubStr)
'Result is: (23-17)/2=3
End Sub
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Learn More!
