Microsoft KB Archive/118979

= Microsoft Knowledge Base =

XL: LEN Function Truncates Count at 255 Characters
Last reviewed: March 27, 1997

Article ID: Q118979

5.00 5.00c 7.00 | 5.00

WINDOWS        | MACINTOSH kbprg kbcode

The information in this article applies to:


 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for Windows 95, version 7.0
 * Microsoft Excel for the Macintosh, version 5.0

SUMMARY
When you use the Visual Basic, Applications Edition, Len function to determine the number of characters in a text box, you may receive a count of 255 even if the number of characters in the string is greater than 255.

WORKAROUND
A better method for counting characters in an object than using the Len function is to use the Characters method and to use the Count property. To return the correct count for the number of characters in a text box, use the following Visual Basic statement:

Sub Character_Count MsgBox Worksheets("Sheet1").TextBoxes("Text 1").Characters.Count End Sub Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

MORE INFORMATION
The Len function returns the number of characters in a string. The function can be used to return the count of the number of characters in an object such as a text box. However, the Len function will count only a maximum of 255 characters (including carriage returns).

Example
The following Visual Basic statement will return a message box displaying the number of characters for the text box named with the object ID text 1 on Sheet1 but, if the text exceeds 255, the Len function truncates the text count at 255 characters.

Sub Len_Count MsgBox Len(Worksheets("Sheet1").TextBoxes("Text 1").Text) End Sub