VBA Len function
You may use the Len function in VBA to get the length of a string, the value in the cell, etc.
Syntax of Len function
Len function returns a long that represents the number of characters in a string.
An example of providing a string to Len function
We just provided a text string to the Len function in this example and displayed the result in a VBA message box:
Sub inputbox_ex()
Dim str_len As Long
str_len = Len("VBA Tutorial")
MsgBox "Length of String = " & str_len
End Sub
Result:
Using a String variable example
In this example, using a String variable and then getting its length by Len function:
Sub inputbox_ex() Dim Str As String Dim str_len As Long Str = "VBA Tutorial" str_len = Len(Str) MsgBox "Length of String = " & str_len End Sub
Result:
Are leading and trailing spaces also count in the Len function?
In the string variable, we added four leading and four trailing spaces. See the same example output as above except for we added spaces:
Sub inputbox_ex() Dim Str As String Dim str_len As Long Str = " VBA Tutorial " str_len = Len(Str) MsgBox "Length of String = " & str_len End Sub
Output:
So, yes Len also counts leading and trailing spaces.
Getting the count of an Excel cell containing text
In this example, we will get the length of the cell that contains text information.
The result below shows both the sheet and message box showing cell length:
Sub inputbox_ex()
Dim str_len As Long
'Getting Length of Excel Sheet Cell
str_len = Len(Range("B8").Value)
MsgBox "Length of Cell Text = " & str_len
End Sub
Result:
What if you provide Integer, Long type variables in the Len function?
Be careful when using the Len function and you provide a variable.
In the case of Integer, Long, or other types of variables than String, it reruns the number of bytes required to store a variable.
See the example below for a clear idea:
Sub inputbox_ex() Dim i As Integer Dim j As Long i = 123 j = 1223456 MsgBox "Length of Integer type variable = " & Len(i) MsgBox "Length of Long type variable = " & Len(j) End Sub
Results:
and
