You may receive a "Run-time error 1004" error message when you programmatically set a large array string to a range in Excel 2003 (818808)

SYMPTOMS

In Office Excel 2003, when you programmatically set a range value with an array containing a large string, you may receive an error message similar to the following:

Run-time error '1004'.
Application-defined or operation-defined error.

CAUSE

This issue may occur if one or more of the cells in an array (range of cells) contain a character string that is set to contain more than 911 characters.

WORKAROUND

To work around this issue, edit the script so that no cells in the array contain a character string that holds more than 911 characters.

For example, the following line of code from the example code block below defines a character string that contains 912 characters:

Sub XLTest()
Dim aValues(4)
 
  aValues(0) = "Test1"
  aValues(1) = "Test2"
  aValues(2) = "Test3"
 
  MsgBox "First the Good range set."
  aValues(3) = String(911, 65)
  
  Range("A1:D1").Value = aValues
 
  MsgBox "Now the bad range set."
  aValues(3) = String(912, 66)
  Range("A2:D2").Value = aValues
 
End Sub

MORE INFORMATION

A character string is a series of characters that are manipulated as a group. The length of a character string is typically the number of characters in it, or the length is pre-defined, as it is in this case.