XL: Passed Strings Greater Than 255 Characters Are Truncated

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Macintosh, version 5.0
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In Microsoft Excel, if you use a Microsoft Visual Basic for Applications procedure to pass a string that is greater than 255 characters in length to an object, such as a text box, Microsoft Excel may truncate the string to 255 characters or fail to enter the string in the text box.

CAUSE

In Microsoft Excel 7.0 or earlier, this behavior occurs because the character limit per cell is 255 characters. As a result, strings greater than 255 characters in length that are passed from a Visual Basic procedure to any Microsoft Excel specific function or object are truncated to 255 characters.

In Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition, you can use more than 255 characters in a cell; however, when you pass strings greater than 255 characters in length from a Visual Basic procedure, Microsoft Excel 97 retains the same design features that earlier versions of Microsoft Excel use.

This limit applies to all strings that you pass from a Visual Basic procedure to a Microsoft Excel sheet (it is not exclusive to information you pass to cells). For example, if you pass a text string longer than 255 characters to a text box on a worksheet or a dialog sheet, Microsoft Excel truncates the text even though a text box can hold up to 10,240 characters.

WORKAROUND

Microsoft provides programming examples 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 article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

Example 1

In the example below, the character length of each variable is 100. Each Insert method inserts another string at the position at the end of the previous string.

Sub NoLoop()
   Dim var1 as String, var2 as String, var3 as String
   Dim first As Integer, secnd As Integer, third As Integer

   ' Note that each of the strings in quotation marks should be entered
   ' on one line.
   var1 = _
      "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
      aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
   var2 = _
      "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
      bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"
   var3 = _
      "cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
      cccccccccccccccccccccccccccccccc"
   var4 = _
      "dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
      dddddddddddddddddddddddddddddddd"

   'Character length of each variable string is 100 characters.
   ' Define variable equal to length of first string.
   first = Len(var1) + 1
   ' Define variables equal to length of original string plus
   ' length of each additional string.
   secnd = first + Len(var2)
   third = secnd + Len(var3)

   ' Insert first string into text box
   DialogSheets(1).TextBoxes(1).Characters.Insert String:=var1
   ' Insert second string at the location of the end of the first string.
   DialogSheets(1).TextBoxes(1).Characters(first).Insert String:=var2
   ' Insert third string at the location of the end of the second string
   ' and so on.
   DialogSheets(1).TextBoxes(1).Characters(secnd).Insert String:=var3
   DialogSheets(1).TextBoxes(1).Characters(third).Insert String:=var4

End Sub

Example 2

Sub Loop ()

   Dim i as Integer
   Dim mytxt As String
   ' Assign mytxt variable to the desired string
   ' String should be entered as one line
   mytxt = "This is the desired string longer than 255 characters."

   With DialogSheets(1).TextBoxes(1)

      ' Initialize text in text box.
      .Text = ""
      For i = 0 To Int(Len(mytxt) / 255)
         .Characters(.Characters.Count + 1).Insert Mid(mytxt, (i * 255) + _
            1, 255)
      Next

   End With

End Sub

Example 3

In the example below, each statement adds 200 "X" characters to the text box. This is very similar to the first example but uses syntax that is specific to Microsoft Excel 97 for Windows.

Sub Excel97()

   ActiveSheet.Shapes("Text Box 2").Select
   Selection.Characters.Text = _
   "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
   XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
   XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"

   Selection.Characters(201).Insert String:= _
   "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
   XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
   XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"

End Sub

MORE INFORMATION

Using Visual Basic for Applications is an improvement over using the Microsoft Excel 4.0 macro language in that you can use Visual Basic procedures for parsing, reading, and writing from a file up to 64 KB in size. When you use strings in a Visual Basic procedure, you are not limited to 255 characters; if you are using Microsoft Windows version 3.1, you can use strings up to 64 KB.

REFERENCES

"User's Guide," version 5.0, page 113

For additional information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q163435
   TITLE     : VBA: Programming Resources for Visual Basic for
               Applications