The VBA LEN function helps to get the length of a given string. The variant variable is seen as a string by the VBA Len. When working with variables that are either strings or variations, the VBA Len Function helps to determine how many characters are there. Let’s say you declare the VBA Len function using variables other than an integer, long, single, or double. The function will calculate the number of bytes required to store the given variable and return that value.
People often confuse the Len function with the Excel worksheet function. Since it is a built-in function for both worksheet and VBA, it has the same syntax, arguments, and output (both return the length of a string). The LEN function is available in the Microsoft Visual Basic Editor as a VBA function that can be useful in a macro.
Syntax
Len( Expression )
Parameters
Expression (required)- This parameter is either a string or a variable whose length you want to figure out.
Return
The Len function in VBA returns the number of characters in the supplied String if the Expression is of String or variant data type or the number of bytes needed to keep the variable if the Expression is of integer, long, single, or double data type.
Example:
Obtain the length of the string “Hello World”.
The LEN string function is the most popular in VBA. It instantly gives back the number of characters in the String or the value specified. This way, it assists users across a range of logical frameworks.
Step 1: Open up your Excel file. You can open the Visual Basic window by clicking on Developer Window > Visual Basic Editor or pressing Alt + F11.
Step 2: You will see the VB Editor window. Next, you need to build a module. Click on Insert on the ribbon, then click on the Module.
Step 3: A module will be added. Our macro will be explicitly written in this module window. Initiate the programme by giving the programme’s name and then declaring the variable.
Sub VBA_LEN_Function() Dim str_LenCount As String End Sub
Step 4: Calculate the String length using the VBA LEN function. It will ask you for the Expression later in the function. Next, pass the String (“Hello World”).
Next, we will put the obtained value in the variable and use MsgBox to show the function’s output.
Sub VBA_LEN_Function()
Dim str_LenCount As String
str_LenCount = Len("Hello World")
MsgBox str_LenCount
End Sub
Output
Either click Run or press the F5 key to run your macro. You will get the following result.
