[SOLVED] 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

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

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
>
>

Hi Dave and Greg,

Thanks heaps for that information.
Thanks for the workaround idea Greg.

Ken Johnson

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

Hi Alan,

Thanks for that.

Ken Johnson