Article ID: 105416 - View products that this article applies to.
This article was previously published under Q105416
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 may fail to enter the string in the text box.
This behavior also commonly occurs when you attempt to pass more than 255 characters to the Connection or SourceData argument of the PivotTableWizard. When you attempt to do this, you receive the following error message:
NOTE: This behavior affects other Microsoft Excel objects.
Run-time error '1004': PivotTableWizard method of worksheet class failed
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 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 that are greater than 255 characters in length from a Visual Basic procedure, Microsoft Excel 97 uses the same design 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 that is 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.
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 Characters property to insert multiple string variables. The following sample macros use the Characters property to do this. Note that the third example is for use 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 a user-defined function, see Example 4.
Example 1In 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.
Example 3In the following example, 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.
Example 4: Converting Text to an Array for PivotTableWizardThis example creates a PivotTable from a Microsoft Access database. The value for the Connection argument is converted to an array using the StringToArray function because the string is greater than 255 characters.
Using Visual Basic for Applications is an improvement over using the Microsoft Excel 4.0 macro language because you can use Visual Basic procedures for parsing, reading, and writing from a file up to 64 kilobytes (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.
"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:
(https://support.microsoft.com/kb/163435/EN-US/ )VBA: Programming Resources for Visual Basic for Applications
Article ID: 105416 - Last Review: October 11, 2006 - Revision: 2.2
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.