Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array size limit when using WorksheetFunction.Transpose?
I'm getting Run-time error '13': Type mismatch when using...
Range("B1").Resize(UBound(vaArray, 2), 2).Value = Application.WorksheetFunction.Transpose(vaArray) to get vaArray onto the worksheet and the number of columns in the array is greater than something like 2730. I can't find any mention of such a limit in my researches. Does anybody know of such a limit and its cause? Ken Johnson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array size limit when using WorksheetFunction.Transpose?
xl2002 removed the 5461 element limit in application.transpose.
And if you have 2730 * 2 elements, you're one away from that limit. (I think that the limit was 5461 in xl2k and below--I don't have xl2k to test.) Ken Johnson wrote: I'm getting Run-time error '13': Type mismatch when using... Range("B1").Resize(UBound(vaArray, 2), 2).Value = Application.WorksheetFunction.Transpose(vaArray) to get vaArray onto the worksheet and the number of columns in the array is greater than something like 2730. I can't find any mention of such a limit in my researches. Does anybody know of such a limit and its cause? Ken Johnson -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array size limit when using WorksheetFunction.Transpose?
xl2000 SP3
I have the limit at 5461 which is double the 2730 figure you meantion plus 1. I assume this is a version specific difference. See Footnote F in the following link. Although the aritcle concerns "Limitations of Passing Arrays to Excel Using Automation" I believe the footnote is a general comment re Transpose. http://support.microsoft.com/default...b;en-us;177991 The best workaround I can think of would be to create your own VBA Transpose function using a loop that allows you to dump to the worksheet in batches of 2730 (or 5461) using Excel's Transpose repopulating and reusing the array. So execution should only be slowed modestly. Regards, Greg "Ken Johnson" wrote: I'm getting Run-time error '13': Type mismatch when using... Range("B1").Resize(UBound(vaArray, 2), 2).Value = Application.WorksheetFunction.Transpose(vaArray) to get vaArray onto the worksheet and the number of columns in the array is greater than something like 2730. I can't find any mention of such a limit in my researches. Does anybody know of such a limit and its cause? Ken Johnson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array size limit when using WorksheetFunction.Transpose?
Hi Dave and Greg, Thanks heaps for that information. Thanks for the workaround idea Greg. Ken Johnson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array size limit when using WorksheetFunction.Transpose?
Ken Johnson wrote:
I'm getting Run-time error '13': Type mismatch when using... Range("B1").Resize(UBound(vaArray, 2), 2).Value = Application.WorksheetFunction.Transpose(vaArray) to get vaArray onto the worksheet and the number of columns in the array is greater than something like 2730. I can't find any mention of such a limit in my researches. Does anybody know of such a limit and its cause? Ken Johnson The following is a transpose function that avoids the 5461 element limit. It also preserves the type of the array when transposing a non-Variant() array; the failure to do that is another limitation of the Worksheet Transpose function, and one that persists beyond Version 2000. I didn't tidy it up to avoid wordwrap: Function ArrayTranspose(InputArray) 'This function returns the transpose of 'the input array or range; it is designed 'to avoid the limitation on the number of 'array elements and type of array that the 'worksheet TRANSPOSE Function has. 'Declare the variables Dim outputArrayTranspose As Variant, arr As Variant, p As Integer Dim i As Long, j As Long 'Check to confirm that the input array 'is an array or multicell range If IsArray(InputArray) Then 'If so, convert an input range to a 'true array arr = InputArray 'Load the number of dimensions of 'the input array to a variable On Error Resume Next 'Loop until an error occurs i = 1 Do z = UBound(arr, i) i = i + 1 Loop While Err = 0 'Reset the error value for use with other procedures Err = 0 'Return the number of dimensions p = i - 2 End If If Not IsArray(InputArray) Or p 2 Then Msg = "#ERROR! The function accepts only multi-cell ranges and 1D or 2D arrays." If TypeOf Application.Caller Is Range Then ArrayTranspose = Msg Else MsgBox Msg, 16 End If Exit Function End If 'Load the output array from a one- 'dimensional input array If p = 1 Then Select Case TypeName(arr) Case "Object()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Object For i = LBound(outputArrayTranspose) To UBound(outputArrayTranspose) Set outputArrayTranspose(i, LBound(outputArrayTranspose)) = arr(i) Next Case "Boolean()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Boolean Case "Byte()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Byte Case "Currency()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Currency Case "Date()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Date Case "Double()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Double Case "Integer()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Integer Case "Long()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Long Case "Single()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Single Case "String()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), LBound(arr, 1) To UBound(arr, 1)) As String Case "Variant()" ReDim outputArrayTranspose(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr)) As Variant Case Else Msg = "#ERROR! Only built-in types of arrays are supported." If TypeOf Application.Caller Is Range Then ArrayTranspose = Msg Else MsgBox Msg, 16 End If Exit Function End Select If TypeName(arr) < "Object()" Then For i = LBound(outputArrayTranspose) To UBound(outputArrayTranspose) outputArrayTranspose(i, LBound(outputArrayTranspose)) = arr(i) Next End If 'Or load the output array from a two- 'dimensional input array or range ElseIf p = 2 Then Select Case TypeName(arr) Case "Object()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Object For i = LBound(outputArrayTranspose) To _ UBound(outputArrayTranspose) For j = LBound(outputArrayTranspose, 2) To _ UBound(outputArrayTranspose, 2) Set outputArrayTranspose(i, j) = arr(j, i) Next Next Case "Boolean()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Boolean Case "Byte()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Byte Case "Currency()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Currency Case "Date()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Date Case "Double()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Double Case "Integer()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Integer Case "Long()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Long Case "Single()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Single Case "String()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As String Case "Variant()" ReDim outputArrayTranspose(LBound(arr, 2) To UBound(arr, 2), _ LBound(arr) To UBound(arr)) As Variant Case Else Msg = "#ERROR! Only built-in types of arrays are supported." If TypeOf Application.Caller Is Range Then ArrayTranspose = Msg Else MsgBox Msg, 16 End If Exit Function End Select If TypeName(arr) < "Object()" Then For i = LBound(outputArrayTranspose) To _ UBound(outputArrayTranspose) For j = LBound(outputArrayTranspose, 2) To _ UBound(outputArrayTranspose, 2) outputArrayTranspose(i, j) = arr(j, i) Next Next End If End If 'Return the transposed array ArrayTranspose = outputArrayTranspose End Function Alan Beban |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array size limit when using WorksheetFunction.Transpose?
Hi Alan,
Thanks for that. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there an array size limit for MMULT or MINVERSE in excel 2007? | Excel Worksheet Functions | |||
Cell size? Or size limit for Text data type? | Excel Discussion (Misc queries) | |||
what is array size limit in VBA? | Excel Programming | |||
worksheetfunction.transpose - nx1 to n element? | Excel Programming | |||
limit to possible array size? | Excel Programming |