What is VBA LEN Excel Function?
The VBA LEN function is a built-in string function that returns the length of a specified string or the number of characters in a given text or string variable. It is commonly used to determine the size of a string, which can be crucial for various programming tasks, such as validating input lengths, handling strings with specific length requirements, or extracting substrings based on their positions within a larger string.
Let us look at an example. In this example, the code calculates the string length “Hello, World!” using the VBA Len function. The length is 13 (number of characters) and is stored in the variable length.
The code then displays a message box, “The string length is: 13.”
Table of contents
- The VBA LEN function counts each character, including spaces, in the input string. It is commonly used to find the length of a string, which includes the count of characters present in the string, including spaces and special characters.
- If you want to exclude leading and trailing spaces from the count, consider using the Trim or WorksheetFunction.Trim function to remove them.
- The VBA LEN function is not limited to only strings; it can also be used to find the number of elements in an array by considering the VBA Len of array as a string. However, it may not give the desired result for multi-dimensional arrays.
VBA LEN Syntax
The syntax of the VBA LEN function is simple.
string: The input string or text expression for which you want to find the length.
Let us look at some examples of how to implement the LEN function.
Example #1: Basic Usage
Step 1: Open your Excel workbook and access the VBA editor by pressing “Alt + F11.”
Step 2: In the VBE, click “Insert” in the menu bar and select “Module.” It will insert a new module into the project.
Step 3: In the new module, create a subroutine named “ValidateInput.”
Step 4: In this step, we declare userInput and userInputLength, where userInput is VBA Len of String data type and userInputLength is of Integer data type. These variables will be used to store the user’s input and the length of the input string, respectively.
Step 5: We create an input box window using the InputBox function. The input box will display the message “Enter your name:” and wait for the user to enter their name. The user’s input will be stored in the userInput variable.
Step 6: Check if the user’s input is not empty using the If statement and the LEN function. If the length of the userInput string is greater than 0 (i.e., not empty), the code proceeds to the next steps. Otherwise, it jumps to the Else block.
Step 7: If the user’s input is not empty, the code calculates the length of the input string using the Len function and assigns it to the userInputLength variable.
Step 8: Now, we display the length of the user’s input string in a message box using the MsgBox function. The box shows the message “Hello, [user’s input]! Your name contains [length of the input] characters.” The [user’s input] and [length of the input] are replaced with the actual user’s input and length, respectively.
Step 9: If the user’s input is empty (i.e., the Else part of the If statement is triggered), the code displays a different message box with “Please enter your name.”
Step 10: Now save the module and exit the VBE. Now, press Alt + F8 to open the VBA Macro menu, select “ValidateInput” and click Run.
Step 11: It will prompt the user to enter their name using an input box in VBA, calculate the length of the input string, and display a message box showing the user’s name and the number of characters in their name.
Here is the complete code:
Dim userInput As String
Dim userInputLength As Integer
‘ Prompt the user for input
userInput = InputBox(“Enter your name:”)
‘ Check if the input is not empty
If Len(userInput) > 0 Then
‘ Calculate the length of the input string
userInputLength = Len(userInput)
‘ Display the length in a message box
MsgBox “Hello, ” & userInput & “! Your name contains ” & userInputLength & ” characters.”
‘ Display a message if the input is empty
MsgBox “Please enter your name.”
Example #2: Handling Empty Strings
Step 1: In the new module, create a subroutine “Example_Len_EmptyString.”
Step 2: In this step, we declare a variable named “emptyString” using the Dim statement and specify that it will be of the String data type. This variable will store the empty string.
Step 3: Here, we set the value of the “emptyString” variable to an empty string using the assignment operator “=.”
Step 4: In this step, we declare another variable named “length” using the Dim statement and specify that it will be of the Integer data type. This variable will store the length of the string.
Step 5: Here, we use the Len function to calculate the length of the “emptyString” variable (which has no characters) and assign the result to the “length” variable.
Step 6: Finally, we use the MsgBox function to display a message box with the text “The length of the empty string is: ” concatenated with the value stored in the “length” variable. The & symbol is used for concatenation in VBA.
Step 7: Now save the module and exit the VBE. Now press Alt + F8 to open the Macro menu, select “Example_Len_EmptyString,” and click Run.
Step 8: Once you execute the macro, the message box will display “The length of the empty string is: 0,” as there are no characters in an empty string.
Here is the complete code:
Dim emptyString As String
emptyString = “”
Dim length As Integer
length = Len(emptyString)
MsgBox “The length of the empty string is: ” & length
VBA LEN as Support Function
Example #1: Trimming Leading and Trailing Spaces
Step 1: In the new module, declare a custom function named “TrimSpaces” that takes an input string “inputString” as an argument and returns a string as a result.
In this step, we define a VBA function named “TrimSpaces” using the Function keyword. The function takes a single input parameter, “inputString,” of the String data type and will return a string as the result.
Step 2: Here, we utilize the WorksheetFunction.Trim function to remove any leading and trailing spaces from “inputString.” The Trim function removes only leading and trailing spaces, not spaces between words.
Step 3: Here, we use the Len function to calculate the length of the trimmed string obtained from WorksheetFunction.Trim(inputString). Then, we use the Mid function in VBA to extract a substring from “inputString” starting from the position after the trimmed part and extending to the end of the original string.
Step 4: Save and close the VBA editor.
Step 5: Go back to your Excel workbook, and you can now use the TrimSpaces function in your worksheet or other VBA code.
Step 6: To use the function in your worksheet, enter the formula =TrimSpaces(inputString) into a cell, where inputString is the string from which you want to trim leading and trailing spaces. For example, if you want to trim spaces from cell A1, you would enter =TrimSpaces(A1).
Step 7: Press Enter, and the function will return the modified string with leading and trailing spaces removed.
Note: The TrimSpaces function requires the leading and trailing spaces in the input string to equal the number of letters in the word.
For instance, if the word is “King” with four letters, the function expects both the leading and trailing spaces to have four spaces as well to work correctly. This condition ensures the function accurately trims the intended spaces and provides the correct modified string.
Here is the complete code:
Function TrimSpaces(inputString As String) As String
‘ Trims leading and trailing spaces from the input string
TrimSpaces = Mid(inputString, Len(WorksheetFunction.Trim(inputString)) + 1, Len(inputString) – 2 * Len(WorksheetFunction.Trim(inputString)))
Example #2: Counting Non-Empty Cells in a Column
Step 1: Declare a custom function named “CountNonEmptyCells” that takes a range “rng” as an argument and returns a Long data type.
Step 2: Here, we declare two variables named “cell” and “count.” “cell” is of the VBA Range data type and will be used to iterate through each cell in the input range “rng.” “count” is of the Long data type and will store the count of non-empty cells.
Step 3: We use a For Each loop to loop through each cell in the range “rng.” The loop iterates through all cells in the specified range.
Step 4: Here, we use the Len function and Trim function in VBA to check if the current cell’s value is non-empty. The Len function calculates the length of the cell value, while the Trim function removes leading and trailing spaces.
Step 5: (i.e., the non-empty cell value), the “count” variable is incremented by 1.
Step 6: The Next cell statement indicates the end of the loop, and the loop continues for the next cell in the “rng” range.
Step 7: Finally, we assign the value of the “count” variable (which represents the number of non-empty cells in the range) to the function “CountNonEmptyCells.” The function exits, and the result is returned.
Step 8: Save and close the VBA editor.
Step 9: Go back to your Excel workbook, and you can now use the CountNonEmptyCells function in your worksheet or other VBA code.
Step 10: To use the function in your worksheet, enter the formula =CountNonEmptyCells(range) into a cell, where the range is the range of cells in which you want to count the non-empty cells. For example, if you want to count non-empty cells in column A, you would enter =CountNonEmptyCells(A:A).
Step 11: Press Enter, and the function will calculate the number of non-empty cells in the specified rangeThe “CountNonEmptyCells” function is valuable when you need to count the number of non-empty cells in a given column.
Here is the complete code:
Function CountNonEmptyCells(rng As Range) As Long
‘ Returns the number of non-empty cells in a given column
Dim cell As Range
Dim count As Long
For Each cell In rng
If Len(Trim(cell.Value)) > 0 Then
count = count + 1
CountNonEmptyCells = count
Important Things To Note
- The VBA Len function is not directly applicable to arrays. To determine the number of elements in an array, use the LBound and UBound functions and the appropriate array dimension.
- When applying the VBA LEN function to a Variant data type, it considers the underlying data type. For example, if the VBA LEN of Variant contains a numeric value, it calculates the length of its string representation.
- VBA LEN can be used to check the length of cell values within a column. It is often combined with loops or conditions to perform specific actions based on the length of cell contents.
- There is a difference between VBA Len and LenB functions. The VBA Len function returns the number of characters based on the number of bytes used to represent each character, while LenB returns the number of bytes in the string.
- The VBA LEN of column function can be used to check the length of cell values within a column. It is often combined with loops or conditions to perform specific actions based on the length of cell contents.
Frequently Asked Questions (FAQs)
Yes, in VBA, the maximum length of a string is 2^31-1 characters (2,147,483,647 characters). However, in practice, memory limitations may prevent reaching this theoretical limit.
The VBA Len function returns the number of characters in a string based on the number of bytes used to represent each character, while LenB returns the number of bytes in the string.
If the VBA Len function is not working as expected, it could be due to the presence of leading or trailing spaces. Use Trim or WorksheetFunction.Trim to remove any leading and trailing spaces before calculating the length. Also, verify that the input variable is a valid string or text expression.
This article must help understand VBA LEN formulas and examples. We can download the template here to use it instantly.
Guide to VBA LEN Excel Function. Here we learn how to use LEN function to find the length of string or character along with examples & downloadable excel template. You can learn more from the following articles –