worksheetfunction.transpose - nx1 to n element?

[Chris Short]

worksheetfunction.transpose has the nice property that passing an n-element
row array returns an n x 1 array (ie it now has two dimensions).

However, I've just stumbled over passing an n x 1 array resulting in single
dimension n element array being returned, rather than a 1 x n array.

This of course is symmetric to the first case, but not what was expected.

I've looked but haven't found any mention of this or a known workaround.

With Excel 2003, I'm looking to confirm this behavior.

thanks,
Christopher

[Tom Ogilvy]

This behavior has been around at least since xl97 and I believe xl5 and
remains in xl2003.

Generally people speak of horizontal and vertical arrays.

A n-element 1 D array is Horizontal, so an n-element 1D array and a 1 x n
array are equivalent from a worksheet perspective. If you write a n element
1D array to the worksheet such as

Range(A1:J1).Value = Array(1,2,3,4,5,6,7,8,9,10)

it would be written correctly.




--
Regards,
Tom Ogilvy



"Chris Short" <cshort@abare.gov.au> wrote in message
news:efb$z9C2FHA.3560@TK2MSFTNGP15.phx.gbl...
> worksheetfunction.transpose has the nice property that passing an
n-element
> row array returns an n x 1 array (ie it now has two dimensions).
>
> However, I've just stumbled over passing an n x 1 array resulting in
single
> dimension n element array being returned, rather than a 1 x n array.
>
> This of course is symmetric to the first case, but not what was expected.
>
> I've looked but haven't found any mention of this or a known workaround.
>
> With Excel 2003, I'm looking to confirm this behavior.
>
> thanks,
> Christopher
>
>
>
>
>

[Chris Short]

Tom Ogilvy says:

>> This behavior has been around at least since ... xl5

yup - at least the horizontal to vertical part .

I suspect the converse has been around as long - they're symmetric - but not
what was expected!

>you write a n element 1D array to the worksheet ..

It was only the transpose behavior I was interested in. I'm dealing with
records from a database and just stumbled across the unusual case (for this
app) of a single record resulting in a 2D single column from the recordset
and transposing it resulted in the n 1D row (which then crashed my code -
which was expecting a 2 D row)

thanks,
Christopher

[Alan Beban]

Chris Short wrote:
> Tom Ogilvy says:
>
>
>>>This behavior has been around at least since ... xl5
>
>
> yup - at least the horizontal to vertical part .
>
> I suspect the converse has been around as long - they're symmetric - but not
> what was expected!
>
>
>>you write a n element 1D array to the worksheet ..
>
>
> It was only the transpose behavior I was interested in. I'm dealing with
> records from a database and just stumbled across the unusual case (for this
> app) of a single record resulting in a 2D single column from the recordset
> and transposing it resulted in the n 1D row (which then crashed my code -
> which was expecting a 2 D row)
>
> thanks,
> Christopher
>
>

The following function (which is included in the freely downloadable
file at http://home.pacbell.net/beban) will return a 1 x n 2-D array
from an n x 1 array; watch for word wrap:

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, z, Msg

'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