VBA Len Function – Get String Length

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 Sub

VBA 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 Sub

VBA 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 Sub

VBA 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!