However, I’ll show you how you can calculate the length of a string with VBA in Excel. Fortunately, you’ll learn to develop a Macro and a user-defined function to complete the task. In this article, I will show you 2 easy ways to find string length with VBA in Excel. Hence, read through the article to learn more and save time.
Here, I have used a VBA code to generate the user-defined LENGTH function to check the string length in Excel.
Read More: VBA to Check If String Contains Another String in Excel (6 Methods)
Download Practice Workbook
2 Easy Methods to Count Length of 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.
Our objective today is to find out the lengths of the Student IDs with the help of VBA.
1. Develop Macro to Count Length of String with VBA in Excel
However, you can develop a Macro to find out the length of a single string or a range of strings using VBA code. Hence, follow the steps below.
⧭ Steps:
- Firstly, press ALT+F11 on your keyboard and the VBA window will open.
- Secondly, go to the Insert tab in the VBA window.
- Thirdly, from the options available, select Module.
- Fourthly, a new module window called “Module 1” will open.
- Then, insert the given VBA code in the opened Module.
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
⧪ Explanation of the Code:
- First, Sub String_Length() declares the starting of the Macro String_Length.
- Then, Dim Length As String declares a string called Length.
- Next, 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.
- Thereafter, 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.
- Now, MsgBox Length shows all the lengths in a message box.
- Lastly, End Sub declares the end of the Macro.
- Next, come back to your worksheet.
- After that, select the range of cells whose length you want to find out. Here, I have selected the column Student ID.
➤ Finally, hit the F5 key and you will find a message box showing the lengths of all your selected strings one by one.
2. Run User-Defined Function to Calculate Length of String with VBA in Excel
Last but not least, 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. Hence, follow the steps below.
⧭ Steps:
- Initially, accomplish steps 1-4 from the previous method to open a new module, and insert the 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
⧪ Explanation of the Code:
- Firstly, Function LENGTH(Str As Variant) starts a new user-defined function called LENGTH that takes a range of cells known as Str as input.
- Secondly, Dim Output() As Integer declares an array of integers known as Output.
- Thirdly, 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.
- After that, 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.
- Finally, End Functiondeclares the end of the function.
- Then, come back to your worksheet.
- Next, select any cell and insert the formula mentioned below. Here I want to know the lengths of the cells of the columns Student ID.
=LENGTH(C4:C13)
⧪ Note: This is an Array Formula. So press CTRL+SHIFT+ENTER unless you are in Office 365.
- Afterwards, you will get the lengths of all the cells arranged beautifully in a new range.
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.
For more information like this, visit Exceldemy.com.
