In VBA, use Len(stringVariable) to get the number of characters in a string; the result is a Long. For example, if customerName contains Ada Lovelace, Len(customerName) returns 12, including the space.
What Len means in VBA
Len is a built-in VBA function that measures the length of a value. When its argument is a string, it counts the characters in that string. The result is useful when you need to check whether text is blank, enforce a character limit, or process text one position at a time.
The function returns a number, not a shortened or changed version of the string. Calling Len does not edit the variable, remove spaces, or change its data type. The original string remains available for later statements.
Length is a count of positions in the string, not a count of words or visible groups of text. For ordinary English text, each letter, digit, punctuation mark, and space normally contributes one position. Special Unicode text can be more complicated: a displayed symbol or accented character may be represented by multiple underlying code units, so Len is not a universal count of what a person sees as one visual symbol.
Syntax and the return value
The basic syntax is Len(string). Put the variable or expression inside the parentheses. You can assign the result to a Long, compare it in an If statement, or use it in a loop. Although VBA can often convert an expression for you, a String variable makes the intention clear when the value is text.
Len(text)returns the length of the string currently stored intext.Len("hello")returns5.Len("")returns0because the string has no characters.Len(" ")returns2; those two spaces are characters.
The value is typically stored in a Long, VBA’s standard integer type for a length count. Use the result directly in comparisons rather than converting it to text. For example, If Len(text) > 50 Then checks whether the value has more than 50 characters.
A basic string-variable example
This short procedure assigns a value to a String variable, obtains its length, and prints both values to the Immediate window. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows.
Sub ShowStringLength()
Dim customerName As String
Dim characterCount As Long
' Count every character, including the space.
customerName = "Ada Lovelace"
characterCount = Len(customerName)
Debug.Print characterCount ' Prints 12.
End Sub
There are three characters in “Ada,” one space, and eight in “Lovelace,” for a total of 12. The assignment to characterCount stores that result; it does not alter customerName. If you change the text before calling Len, VBA measures the new value instead.
Before running a macro, save a copy of the workbook. Macro actions cannot be undone with Ctrl+Z, even when a sample macro seems harmless.
Spaces and other characters count
Len counts spaces wherever they occur: at the beginning, between words, and at the end. It also counts punctuation, digits, and characters such as tabs or line breaks when those are present in the string. That behavior is often exactly right for a maximum-length check, because the stored text includes those characters.
It can also explain why a cell or input looks empty but produces a nonzero length. A string containing three spaces has length three, not zero. If the requirement is to treat ordinary leading and trailing spaces as insignificant, trim the text before measuring it. VBA’s Trim$ removes leading and trailing space characters; it does not remove spaces between words.
Sub CompareRawAndTrimmedLength()
Dim enteredText As String
Dim rawLength As Long
Dim trimmedLength As Long
' The surrounding spaces count in the raw value.
enteredText = " VBA "
rawLength = Len(enteredText) ' 7
trimmedLength = Len(Trim$(enteredText)) ' 3
Debug.Print rawLength, trimmedLength
End Sub
Choose deliberately between the two measurements. Use Len(enteredText) when the spaces are part of the actual input and should count. Use Len(Trim$(enteredText)) when surrounding spaces should not affect a check. Trimming before validation changes what you measure, so do not do it automatically if exact spacing matters.
Len versus LenB
Len and LenB answer different questions. Len reports string length in characters as VBA counts them. LenB reports the number of bytes used to represent the value. In typical VBA strings, which use Unicode storage, an ordinary character commonly accounts for two bytes; therefore LenB can be twice Len for simple text.
For example, the word Cat has a Len of 3 and typically a LenB of 6 in VBA. Do not use LenB to enforce a limit expressed in characters. Nor should you treat its result as the complete size of a file, a database record, or text sent over a network: those formats may encode or store text differently and may add their own overhead.
LenB is relevant when code specifically needs a byte count, such as working with certain data structures or binary representations. For everyday text checks and character limits, Len is the appropriate function. If the data will be written to a particular external format, use that format’s encoding rules to determine the actual byte size.
Using Len for validation
A common use is rejecting text that exceeds a permitted length. Compare the result with the rule you need, and make the limit easy to identify in the code. For example, a form might allow up to 30 characters in a label. “Up to 30” normally means 30 is accepted and only values greater than 30 are rejected.
Sub CheckLabelLength()
Dim labelText As String
Dim maximumCharacters As Long
' Allow values of exactly 30 characters.
labelText = "Quarterly sales summary"
maximumCharacters = 30
If Len(labelText) > maximumCharacters Then
Debug.Print "Too long"
Else
Debug.Print "Length accepted: " & Len(labelText)
End If
End Sub
The example text has 24 characters, so the accepted branch runs. If you want to reject a value that is exactly at the limit, use a greater-than-or-equal comparison instead; that is a different rule. State whether the limit is inclusive when translating a form, import specification, or business requirement into VBA.
For a blank-input check, compare the length with zero. If spaces alone should count as blank, test the trimmed value instead. For a Variant that may contain Null, handle IsNull before calling or assigning the result of Len; a Null value is not the same thing as an empty string, and it cannot safely be treated as an ordinary numeric length.
Fixed-length strings and common surprises
Most VBA String variables are variable-length: they hold the text assigned to them without padding it to a declared size. VBA also supports fixed-length strings, declared with a length after an asterisk, such as Dim shortCode As String * 8. A fixed-length string is padded with spaces to its declared length when the assigned text is shorter. Consequently, Len(shortCode) reports the fixed length, including padding, rather than just the number of non-space characters originally typed.
This distinction matters when code is adapted from examples or older data-processing routines. If you see an unexpectedly high result, inspect the declaration as well as the assigned text. Also check for spaces appended by a worksheet cell, an import, or another procedure. The debugger’s Locals and Watch windows can help you inspect the variable, while printing Len before and after trimming helps distinguish padding from meaningful content.
- Use a normal
As Stringdeclaration for text whose length can vary. - Use a fixed-length declaration only when a specific data layout requires it.
- Do not infer that a value is blank just because it looks blank; check whether it contains spaces.
- When exact Unicode display length matters, test representative text rather than assuming one displayed symbol always equals one VBA position.
Related functions and choosing the right check
Use Len when the question is “How many positions are in this string?” Use Trim$, LTrim$, or RTrim$ when the rule concerns leading or trailing spaces, and call Len on the resulting text if the trimmed length is what matters. Use InStr when you need to find a substring, not count the whole string. Use Left$, Mid$, or Right$ when you need to extract part of a string.
These functions solve separate problems. For instance, a string can have a length of 20 without containing a required word, and finding a word with InStr does not tell you whether the entire input fits a character limit. A robust validation routine may use several checks, each tied to a specific requirement.
Frequently Asked Questions
How do I get the length of a string variable in VBA?
Call Len with the variable in parentheses, as in Len(myText). It returns a number representing the string’s length.
Does Len count spaces in VBA?
Yes. Leading, trailing, and internal spaces count. If surrounding spaces should be ignored, measure Len(Trim$(myText)) instead.
What does Len return for an empty string?Len("") returns 0. A string containing one or more spaces is not empty and returns the number of spaces it contains.
Should I use Len or LenB for a character limit?
Use Len for a limit stated in characters. LenB counts bytes in the VBA representation and is intended for byte-oriented work, not ordinary character-limit validation.
Why does Len return a larger value than the visible text seems to have?
Check for leading or trailing spaces, fixed-length String padding, and characters that are not visually obvious, such as tabs or line breaks. For unusual Unicode text, the number of underlying string positions may also differ from the number of displayed symbols.
Related
- Power Query Union Tables: How to Append Queries
- Data Validation: Definition, Rules, and Examples
- Excel Data Validation Date Picker: Setup and Options
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Copy a Column and Paste Values Only SafelyTo copy a column in Excel VBA and paste only its values, copy the source column and use…
- VBA Find a Plumber in an Excel Contact ListTo find plumber listings in an Excel contact list with VBA, search the column that contains service categories…
- VBA Copy and Paste Values Only in ExcelTo copy a range in Excel VBA and paste only its values, use Copy followed by PasteSpecial Paste:=xlPasteValues.…
- Excel Worksheet Change Event: VBA Setup and ExamplesExcel’s Worksheet_Change event is a VBA procedure that runs when cells on a particular worksheet are changed, such…
- VBA Delete Row If Cell Contains TextTo delete rows when a cell contains particular text, use VBA’s InStr function in a loop that checks…
- Dim Worksheet VBA: Declare and Set a Sheet VariableIn Excel VBA, use Dim ws As Worksheet to declare a variable that can refer to a worksheet;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.