Passed strings longer than 255 characters are truncated in Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q213841
This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft Excel 97 and earlier and Microsoft Excel 98 and earlier version of this article, see 105416.
If you use a Microsoft Visual Basic for Applications procedure 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 Excel objects.
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 Excel, you can use more than 255 characters in a cell; however, when you pass strings that are greater than 255 characters in length from a Visual Basic procedure, Excel uses the same design that earlier versions use.

This limit applies to all strings that you pass from a Visual Basicprocedure to an 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,Excel truncates the text even though a text box can hold up to10,240 characters.
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. Examples 1 and 2 contain macros that use the Characters property to do this.

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 a user-defined function, see Example 3.

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 the previous string.
Sub NoLoop()   Dim var1 As String, var2 As String, var3 As String, var4 As String   Dim first As Integer, second As Integer, third As Integer   ' Create four text string, each 100 characters in length.        var1 = String(100,"a")   var2 = String(100,"b")   var3 = String(100,"c")   var4 = String(100,"d")   ' 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.   second = first + Len(var2)   third = second + Len(var3)   ' Select the text box on the worksheet.   ActiveSheet.Shapes("Text Box 1").Select   With Selection      ' Place the first string into the text box.      .Characters.Text = var1      ' Place the second, third, and fourth text strings into the      ' text box.       .Characters(Start:=first).Text = var2      .Characters(Start:=second).Text = var3      .Characters(Start:=third).Text = var4   End WithEnd Sub				

Example 2

This example utilizes a looping structure to insert a long string into a text box.
Sub Looper()   Dim i as Integer   Dim mytxt As String   ' Create a string 1000 characters in length.   mytxt = WorksheetFunction.Rept("test", 250)   ActiveSheet.Shapes("Text Box 1").Select         With Selection      ' Initialize text in text box.      .Text = ""      For i = 0 To Int(Len(mytxt) / 255)      .Characters(.Characters.Count + 1).Text = Mid(mytxt, (i * 255) + _         1, 255)      Next   End WithEnd Sub				

Example 3: Converting Text to an Array for PivotTableWizard

This example creates a PivotTable from a Microsoft Access database. It converts the value for the Connection argument to an array using theStringToArray function because the string is greater than 255 characters in length.
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)'NOTE: You can add your own code to add fields to the pivot table.End SubFunction 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 = TempEnd Function				
For more information about getting help with Visual Basic for Applications, click the following article number to view the article in the Microsoft Knowledge Base:
226118 List of resources that are available to help you learn Visual Basic for Applications programming
greater than 255 string vba XL2000 XL2002 XL2003

Article ID: 213841 - Last Review: 12/05/2015 12:21:44 - Revision: 4.5

Microsoft Office Excel 2003, Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition

  • kbnosurvey kbarchive kbdtacode kberrmsg kbprb kbprogramming KB213841