ExcelBanter - View Single Post

Tim Williams[_2_]

http://support.microsoft.com/kb/818808

Tim

"Casey" wrote in message
...
Microsoft documents that the largest number of characters that can be in a
cell value is 32,767. I verified this is the case using code similar to
the vba below. However, there is different behavior depending on how I
set the value programmatically. If I set the cell value from an array
(which I normally do when populating multiple values at once -- ex. a
two-dimensional array of values to populate a rectangular range), an
exception occurs if the value is longer than some arbitrary amount (around
910 in my case below). The error, "Application-defined or object-defined
error" is not helpful. Why does it fail when populating via array? Seems
like a bug to me unless there is another limitation I'm not aware of. See
vba below that reproduces the problem.

Thanks,
Casey

Sub MaxValueLengthTest()

On Error GoTo ErrHandler

Dim i As Integer
Dim vals(1) As Variant
Dim maxLengthValue As String

'build a string with 32767 characters -- the max supported per cell
For i = 1 To 3276
maxLengthValue = maxLengthValue & "0123456789"
Next i
maxLengthValue = maxLengthValue & "0123456"

'both simple cases work
vals(0) = "simple"
Range("A1").Value = vals(0)
Range("A2").Value = vals

vals(0) = maxLengthValue
Range("B1").Value = vals(0) 'setting from the string works
Range("B2").Value = vals 'setting via the array fails -- in my
testing it starts failing when a value in this array is more than approx.
910 characters

Exit Sub

ErrHandler:
MsgBox Err.Description
End Sub