VBA LEN

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.

VBA Len Intro - Code

The code then displays a message box, “The string length is: 13.”

VBA Len Intro - Output
Key Takeaways
  1. 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.
  2. If you want to exclude leading and trailing spaces from the count, consider using the Trim or WorksheetFunction.Trim function to remove them.
  3. 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.

Len(string)

string: The input string or text expression for which you want to find the length.


Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

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.”

VBA Len - Example 1 - Step 1

Step 2: In the VBE, click “Insert” in the menu bar and select “Module.” It will insert a new module into the project.

VBA Len - Example 1 - Step 2.jpg

Step 3: In the new module, create a subroutine named “ValidateInput.”

VBA Len - Example 1 - Step 3.jpg

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.

VBA Len - Example 1 - Step 4.jpg

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.

VBA Len - Example 1 - Step 5.jpg

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.

VBA Len - Example 1 - Step 6.jpg

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.

VBA Len - Example 1 - Step 7.jpg

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.

VBA Len - Example 1 - Step 8.jpg

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.”

VBA Len - Example 1 - Step 9.jpg
VBA Len - Example 1 - Step 9 - message box

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.

Example 1 - Step 10.jpg

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.

VBA Len - Example 1 - Step 11 - box.jpg
VBA Len - Example 1 - Step 11 - Output

Here is the complete code:

Sub ValidateInput()
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.”
Else
‘ Display a message if the input is empty
MsgBox “Please enter your name.”
End If
End Sub

Example #2: Handling Empty Strings

Step 1: In the new module, create a subroutine “Example_Len_EmptyString.”

VBA Len - Example 2 - Step 1.jpg

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.

VBA Len - Example 2 - Step 2.jpg

Step 3: Here, we set the value of the “emptyString” variable to an empty string using the assignment operator “=.”

VBA Len - Example 2 - Step 3.jpg

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.

VBA Len - Example 2 - Step 4.jpg

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.

VBA Len - Example 2 - Step 5.jpg

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.

VBA Len - Example 2 - Step 6.jpg

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.

VBA Len - Example 2 - Step 7.jpg

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.

VBA Len - Example 2 - Step 8.jpg

Here is the complete code:

Sub Example_Len_EmptyString()
Dim emptyString As String
emptyString = “”
Dim length As Integer
length = Len(emptyString)
MsgBox “The length of the empty string is: ” & length
End Sub

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.

VBA len - Trim - Example 1 - Step 1.jpg

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.

VBA len - Trim - Example 1 - Step 2.jpg

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.

VBA len - Trim - Example 1 - Step 3.jpg
VBA len - Trim - Example 1 - Step 3 - String.jpg

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.

VBA len - Trim - Example 1 - Step 5.jpg

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).

VBA len - Trim - Example 1 - Step 6.jpg

Step 7: Press Enter, and the function will return the modified string with leading and trailing spaces removed.

VBA len - Trim - Example 1 - Step 7.jpg

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)))
End Function

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.

Example 2 - Step 1.jpg

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.

Example 2 - Step 2.jpg

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.

Example 2 - Step 3.jpg

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.

Example 2 - Step 4.jpg

Step 5: (i.e., the non-empty cell value), the “count” variable is incremented by 1.

Example 2 - Step 5.jpg

Step 6: The Next cell statement indicates the end of the loop, and the loop continues for the next cell in the “rng” range.

Example 2 - Step 6.jpg

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.

Example 2 - Step 7.jpg

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.

Example 2 - Step 9.jpg

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).

Example 2 - Step 10.jpg

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.

Example 2 - Step 11.jpg

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
End If
Next cell
CountNonEmptyCells = count
End Function

Important Things To Note

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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)

1. Is there a limit to string length in VBA?

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.

2. What is the difference between Len and LenB in VBA?

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.

3. Why is VBA Len function not working?

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.

Download Template

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 –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *