EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA String and Text Functions VBA Length of String
Secondary Sidebar
VBA String and Text Functions
  • VBA String and Text
    • VBA Constants
    • VBA TRIM
    • VBA Find
    • VBA Case
    • VBA Replace
    • VBA String
    • VBA Split
    • VBA InStr
    • VBA RegEx
    • VBA Left
    • VBA UCASE
    • VBA LCase
    • VBA Concatenate
    • VBA StrComp
    • VBA StrConv
    • VBA Find and Replace
    • VBA Right
    • VBA ASC
    • VBA InStrRev
    • VBA Length of String
    • VBA Format
    • VBA MID
    • VBA Concatenate Strings
    • VBA SubString
    • VBA String Comparison
    • VBA String Array
    • VBA Dynamic Array
    • VBA Replace String

VBA Length of String

By Pradeep SPradeep S

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:

Syntax of VBA Length of string

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

You can download this VBA Length of String Excel Template here – VBA Length of String Excel Template

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.

VBA length of string Example 1.1

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

VBA length of string - module

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 1.2

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 given a name as LENGTH() after typing Sub

Sub LENGTH()

End Sub

VBA length of String Example 2.1

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

VBA length of String Example 2.2

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

VBA length of String Example 2.3

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

Example 2.4

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 String of Length 1

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

 Example 3.1

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

Example 3.2

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

Example 3.3

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.

VBA String of length

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
Popular Course in this category
VBA Training (4 Courses, 12+ Projects)
  4 Online Courses |  13 Hands-on Projects |  50+ Hours |  Verifiable Certificate of Completion
4.7
Price

View Course
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more