C
Casey
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
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