Passed Strings Longer Than 255 Characters Are Truncated

This article was previously published under Q105416
This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
In Microsoft Excel, if you use a Microsoft Visual Basic for Applicationsprocedure to pass a string that is greater than 255 characters in length toan object, such as a text box, Microsoft Excel may truncate the string to255 characters or may fail to enter the string in the text box.

This behavior also commonly occurs when you attempt to pass more than 255characters to the Connection or SourceData argument of thePivotTableWizard. When you attempt to do this, you receive the followingerror message:
Run-time error '1004': PivotTableWizard method of worksheet class failed
NOTE: This behavior affects other Microsoft Excel objects.
CAUSE
In Microsoft Excel 7.0 or earlier, this behavior occurs because thecharacter limit per cell is 255 characters. As a result, strings greaterthan 255 characters in length that are passed from a Visual Basic procedureto any Microsoft Excel 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 passstrings that are greater than 255 characters in length from a Visual Basicprocedure, Microsoft Excel 97 uses the same design that earlier versions ofMicrosoft Excel use.

This limit applies to all strings that you pass from a Visual Basicprocedure to a Microsoft Excel sheet; it is not exclusive to informationyou pass to cells. For example, if you pass a text string that is longerthan 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 to10,240 characters.
WORKAROUND
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements. To insert more than 255 characters in a text box, use the Charactersproperty to insert multiple string variables. The following sample macrosuse the Characters property to do this. Note that the third example is foruse with Microsoft Excel 97 for Windows.

To pass more than 255 characters to the Connection or SourceData arguments,convert the long string to an array. To see a sample macro that uses auser-defined function, see Example 4.

Example 1

In the following example, the character length of each variable is 100.Each Insert method inserts another string at the position at the end of theprevious 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 = _         "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa         aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"      var2 = _         "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb         bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"      var3 = _         "ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc         ccccccccccccccccccccccccccccccccccc"      var4 = _         "ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd         ddddddddddddddddddddddddddddddddddd"      ' The character length of each variable string is 100 characters.      ' Define the variable equal to length of the first string.      first = Len(var1) + 1      ' Define variables equal to length of the original string plus      ' the 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 Looper()         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 following example, each statement adds 200 "X" characters to thetext box. This is very similar to the first example but uses syntax that isspecific to Microsoft Excel 97 for Windows.
   Sub Excel97()      ActiveSheet.Shapes("Text Box 2").Select      Selection.Characters.Text = _      "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"      Selection.Characters(201).Insert String:= _      "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"   End Sub				

Example 4: Converting Text to an Array for PivotTableWizard

This example creates a PivotTable from a Microsoft Access database. Thevalue for the Connection argument is converted to an array using theStringToArray function because the string is greater than 255 characters.
   Sub ExecuteLongConnection()          'Declare variables.       Dim Chan As Variant       Dim LongConnection As Variant       Dim NumRows, NumCols As Variant          ' Set LongConnection to a long connection string       ' (> 127 characters).       LongConnection = "ODBC;DBQ=\\mustang2\databases\nwind.mdb;" _          & "DefaultDir=\\mustang2\databases;Driver={Microsoft " _          & "Access Driver (*.mdb)};DriverId=25;FIL=MS Access;" _          & "ImplicitCommitSync=Yes;MaxBufferSize=512;MaxScanRows=8;" _          & "PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;" _          & "UserCommitSync=Yes"          ' Execute the PivotTableWizard method and use the StringToArray       ' function to convert the long string to elements in an array.       ActiveSheet.PivotTableWizard SourceType:=xlExternal, SourceData:= _              Array("SELECT Employees.EmployeeID, Employees.Region," _              & "Employees.Country FROM `\\mustang2\databases\NWIND`" _              & ".Employees Employees"), _              TableDestination:="", TableName:="PivotTable1", _              BackgroundQuery:=False, _              Connection:=StringToArray(LongConnection)      End Sub      'NOTE: You can add your own code to add fields to the pivot table.                 Function StringToArray(Query As Variant) As Variant      Const StrLen = 127 ' Set the maximum string length for                         ' each element in the array to return                         ' to 127 characters.      Dim NumElems As Integer      Dim Temp() As String      ' Divide the length of the string Query by StrLen and      ' add 1 to determine how many elements the String array      ' Temp should contain, and redimension the Temp array to      ' contain this number of elements.      NumElems = (Len(Query) / StrLen) + 1      ReDim Temp(1 To NumElems) As String      ' Build the Temp array by sequentially extracting 127      ' segments of the Query string into each element of the      ' Temp array.      For i = 1 To NumElems         Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)      Next i      ' Set the function StringToArray to the Temp array so it      ' can be returned to the calling procedure.      StringToArray = Temp   End Function				
MORE INFORMATION
Using Visual Basic for Applications is an improvement over using theMicrosoft Excel 4.0 macro language because you can use Visual Basicprocedures for parsing, reading, and writing from a file up to 64kilobytes (KB) in size. When you use strings in a Visual Basic procedure,you are not limited to 255 characters; if you are using Microsoft Windowsversion 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 forApplications, please see the following article in the Microsoft KnowledgeBase:
163435VBA: Programming Resources for Visual Basic for Applications
xl97 greater than 255 string vba XL
Properties

Article ID: 105416 - Last Review: 12/04/2015 09:46:30 - Revision: 2.2

Microsoft Excel 97 Standard Edition, Microsoft Excel 98 for Macintosh

  • kbnosurvey kbarchive kbdtacode kbhowto kbprogramming KB105416
Feedback