VBA Len function
To get the length of a string, the value in the cell, etc., you may use the Len function in VBA.
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
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
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
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
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