populating cells with array bug?
 

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

populating cells with array bug?
 

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

populating cells with array bug?
 

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


Casey wrote:
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

populating cells with array bug?
 

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

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

--
Rick (MVP - Excel)

populating cells with array bug?
 

Const s As String = "1234567890"
v = Replace(String(3276, "X"), "X", s)

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



On 10/14/09 1:22 PM, Rick Rothstein wrote:
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

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