Length of a String with VBA in Excel (With Macro and UDF)

In this article, I’ll show you how you can calculate the length of a string with VBA in Excel. You’ll learn to develop a Macro and a user-defined function to calculate the length of a single string or a range of strings.


Count the Length of a String with VBA in Excel (Quick View)

Sub String_Length()

Dim Length As String
Length = ""

For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Columns.Count
        Length = Length + Str(Len(Selection.Cells(i, j))) + vbNewLine
    Next j
Next i

MsgBox Length

End Sub

VBA Code to Count the Length of a String with VBA in Excel


Download Practice Workbook


2 Methods to Count the Length of a String with VBA in Excel

Here we’ve got a data set with the Names and Student IDs of some students of a school called Sunflower Kindergarten.

Data Set to Count the Length of a String with VBA in Excel

Our objective today is to find out the lengths of the Student IDs with the help of VBA.


1. Develop a Macro to Count the Length of a String with VBA in Excel

You can develop a Macro to find out the length of a single string or a range of strings using VBA code.

Use the following VBA code:

VBA Code:

Sub String_Length()

Dim Length As String
Length = ""

For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Columns.Count
        Length = Length + Str(Len(Selection.Cells(i, j))) + vbNewLine
    Next j
Next i

MsgBox Length

End Sub

Note: This code creates a Macro called String_Length.

Step by Step Procedure to Run the Macro:

Step 1: Opening the VBA Window

Press ALT+F11 on your keyboard. The VBA window will open.

Opening VBA Window to Count the Length of a String with VBA in Excel

Step 2: Inserting a New Module

Go to the Insert tab in the VBA window. 

From the options available, select Module.

Step 3: Entering the VBA Code

A new module window called “Module 1” will open. 

Insert the given VBA code in the opened Module.

VBA Code to Count the Length of a String with VBA in Excel

Explanation of the Code:

  • Sub String_Length() declares the starting of the Macro String_Length.
  • Dim Length As String declares a string called Length.
  • Length = “” sets the initial value of the string Length to a blank character.
  • For i = 1 To Selection.Rows.Count starts an iteration for each row of the selected range.
  • For j = 1 To Selection.Columns.Count starts an iteration for each column of the selected range.
  • Length = Length + Str(Len(Selection.Cells(i, j))) + vbNewLine accumulates the lengths of all the strings one by one. This is the most crucial line.
  • Next j declares the end of the 2nd for-loop.
  • Next i declares the end of the 1st for-loop.
  • MsgBox Length shows all the lengths in a message box.
  • End Sub declares the end of the Macro.

Step 4: Saving the Macro-Enabled Workbook

Save the workbook as Excel Macro-Enabled Workbook.

Saving the Workbook to Count the Length of a String with VBA in Excel

Step 5: Selecting the Range of Cells

➤ Then come back to your worksheet.

➤ Select the range of cells whose length you want to find out. I have selected the column Student ID.

Step 6: Running the Macro

  Press ALT+F8 on your keyboard.

A dialogue box called Macro will open up. Select String_Length (The name of the Macro) and click on Run.

Running the Macro to Count the Length of a String with VBA in Excel

Step 7: The Final Output!

Then click OK.

You will find a message box showing the lengths of all your selected strings one by one.

Output to to Count the Length of a String with VBA in Excel


2. Run a User-Defined Function to Calculate the Length of a String with VBA in Excel

You can also develop a user-defined function to calculate the length of a single string or a range of strings using VBA code in Excel.

VBA Code:

Function LENGTH(Str As Variant)

Dim Output() As Integer
ReDim Output(Str.Rows.Count - 1, Str.Columns.Count - 1)

For i = 0 To Str.Rows.Count - 1
    For j = 0 To Str.Columns.Count - 1
        Output(i, j) = Len(Str(i + 1, j + 1))
    Next j
Next i

LENGTH = Output

End Function

Note: This code creates a User-defined function called Length.

Step by Step Procedure to Run the Function:

Step 1: The Initial Steps

Accomplish steps 1-4 from the previous method to open a new module, insert the VBA code and save the Macro-Enabled workbook.

Explanation of the Code:

  • Function LENGTH(Str As Variant) starts a new user-defined function called LENGTH that takes a range of cells known as Str as input.
  • Dim Output() As Integer declares an array of integers known as Output.
  • ReDim Output(Str.Rows.Count – 1, Str.Columns.Count – 1) sets the new dimension of the range Output equal to the input range Str.
  • For i = 0 To Str.Rows.Count – 1 starts an iteration for each row of the input range Str.
  • For j = 0 To Str.Columns.Count – 1 starts an iteration for each column of the input range Str.
  • Output(i, j) = Len(Str(i + 1, j + 1)) sets the values of the array output to the lengths of the array Str.
  • Next j ends the 2nd for-loop.
  • Next i ends the 1st for-loop.
  • LENGTH = Output sets the array Output as the output of the function.
  • End Function declares the end of the function.

Step 2: Running the Function

Come back to your worksheet.

Select any cell and insert the formula:

=LENGTH(range)

Here I want to know the lengths of the cells of the columns Student ID.

So I have entered:

=LENGTH(C4:C13)

Inserting User Defined Function to Count the Length of a String with VBA in Excel

Note: This is an Array Formula. So press CTRL+SHIFT+ENTER unless you are in Office 365.

Step 3: The Final Output

You will get the lengths of all the cells arranged beautifully in a new range.

Length of String Using VBA in Excel


Conclusion

Using these methods, you can calculate the length of a single string or a range of strings with VBA in Excel. Do you have any questions? Feel free to ask us.

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo