Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating a ListBox with an Array | Excel Programming | |||
Populating an array | Excel Worksheet Functions | |||
populating a listbox from an array | Excel Programming | |||
populating listview with array | Excel Programming | |||
Populating a 2-D array | Excel Programming |