Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.transpose - nx1 to n element?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.transpose - nx1 to n element?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.transpose - nx1 to n element?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.transpose - nx1 to n element?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hidden element | Excel Worksheet Functions | |||
hidden element | Excel Worksheet Functions | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions | |||
VBA- Contains any element | Excel Programming | |||
WorkSheetFunction.CountIf & WorkSheetFunction.SumIf with 2 conditions? | Excel Programming |