VBA Length of String

What is VBA Length of String?

VBA Len is a significant tool for data validation. VBA LEN function Returns the number of characters in a supplied string i.e. to measure the length of text or number of characters in a text. VBA LEN is categorized under the String function. It can be used as either procedure or function in the VBA editor window. It is frequently used as a support function along with other string functions like MID, RIGHT i.e. To pullout name parts from a full name.

Syntax of VBA Length of String in Excel

The syntax for VBA Length of String function in excel is as follows:

After typing LEN click on spacebar, the above-mentioned syntax appears where it contains the below-mentioned argument.

• String: It is an expression or a text string or a variable name from which you want to return the length.

i.e. Len(“Length”) = return the value 6, because the length of the text “Length” is 6. If you take variable as Variant instead of long then it is considered the same as a string. i.e. the Len functions treat the variant variable as a String.

How to Use Length of String Function in Excel VBA?

Below are the different examples to use Length of string in Excel using VBA code.

VBA Length of String – Example #1

Follow the below steps to use a length of string function in excel VBA.

Step 1: Go to the Developers tab and click on Visual Basic.

Step 2: Open a Module from the Insert menu option as shown below.

Step 3: Under the Microsoft Excel objects, right-click on sheet 1 (VB_LEN) Insert and under the menu section select Module, so that a new blank module gets created.

VBA Length of String – Example #2

Suppose, I have the word “NULL” and I want to find out the number of characters in this text string i.e. for this, with the help of VB LEN function macro code, I can find out.

Step 1: In the VBA editor, I have a given a name as LENGTH() after typing Sub

Sub LENGTH()

End Sub

Step 2: As VBA LEN function is categorized under string type, DIM (Dimension) is used in a VBA code to declare a variable name, it’s type. In VBA, you need to always declare initially that you are setting up a variable. This is done (mostly) with the word Dim.

DIM is used to declare variable & storage allocation for variable.

Syntax: Dim [Insert Variable Name] as [Insert Variable Type]

Code:

Sub LENGTH()

Dim L As Variant

End Sub

Step 3: After declaring a variable, Assign a value to this variable with the help of LEN function.

Code:

Sub LENGTH()

Dim L As Variant
L = Len (

End Sub

Step 4: After typing LEN and click on the spacebar and once you enter open bracket below mentioned syntax appears for the right function.

String: It is an expression or a text string or a variable name from which you want to return length i.e. “LENGTH”

Code:

Sub LENGTH()

Dim L As Variant
L = Len("LENGTH")

MsgBox L

End Sub

Once you finished typing LEN function arguments. Now, I want to display this result of the len function in the message box. then in the next line of code, let’s click on Ctrl + Space, type MsgBox after this you can mention a variable name.

Step 5: Now, the code is ready, you can run it by click on the F5 key. Once you do that, Pop up message appears, with a result in it as “6” which indicates the number of characters in a supplied string or text (Length)

VBA Length of String – Example #3

Now, instead of output appearing in the message box, I want the result or output data to appear in the worksheet. In the worksheet, I have a data, i.e. USA state in the cell “A4”, now I want to find out a number of characters in the text string of state, Here I can use Len function with slight modification in the code.

Step 1: After declaring a variable, I have to input the variable name again and cell address of the full-text string with the help of Range or cell function.

Code:

Sub TEXT_LENGTH()

Dim L As Variant
L = Range("A4")

End Sub

Step 2: Now, again I need to enter a variable name, and apply LEN function & input its arguments.

Code:

Sub TEXT_LENGTH()

Dim L As Variant
L = Range("A4")
L = Len("Massachusetts")

End Sub

In the previous example,  we entered msg box for the result to be displayed, now, I want the result to appear in a specific cell in a worksheet. for this, I  need to enter range or cell function for the result to be displayed.

Step 3: Now, let’s apply range function, initially I need to input the range or cell function and later enter the variable name, so that in that specific cell (“B4”), the result appears.

Code:

Sub TEXT_LENGTH()

Dim L As Variant
L = Range("A4")
L = Len("Massachusetts")
Range("B4").Value = L

End Sub

Step 4: Now, the code is ready, I can run it by click on the F5 key. once I do that, you can observe an output value of LEN function appearing in the cell “B4” i.e. 13 which indicates the number of characters in a supplied string or text.

Things to Remember

• Apart from LEN function, LenB is used to calculate or to find out the actual number of required bytes for a provided variable in memory.
• Object variable should not be used in Len function.

Recommended Articles

This is a guide to VBA Length of String Function. Here we discuss how to use VBA Length of String Function in Excel along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Workbook
2. VBA String Array
3. VBA String Comparison
4. VBA RGB