populating cells with array bug?

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
 
D

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top