populating cells with array bug?

[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

 

[Tim Williams]

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

Tim

 

[Dana DeLouis]

For i = 1 To 3276

maxLengthValue = maxLengthValue & "0123456789"
Next i

HI. Just a different idea for building the string...

Sub Demo()
Dim v
Const s As String = "1234567890"
v = WorksheetFunction.Rept(s, 3276)
Debug.Print Len(v)
End Sub

= = = = = =
HTH
Dana DeLouis

 

[Rick Rothstein]

For i = 1 To 3276

HI. Just a different idea for building the string...

Sub Demo()
Dim v
Const s As String = "1234567890"
v = WorksheetFunction.Rept(s, 3276)
Debug.Print Len(v)
End Sub

You can get the same end result that your code produces using built-in VB
functions...

Sub Demo()
Dim v
Const s As String = "1234567890"
v = Replace(String(3276, "X"), "X", s)
Debug.Print Len(v)
End Sub

 

[Dana DeLouis]

Const s As String = "1234567890"

v = Replace(String(3276, "X"), "X", s)

Thanks Rick! Didn't think of that. Nice!
Dana DeLouis