… just a small point along the way ( or rather mistake in my thinking ) which I just noticed myself..so thought I should post it…
Originally Posted by
Doc.AElstein
Transpose Alternative For VBA Arrays
.......
. 2) The “simple routine” I mentioned, is simple enough even for me to write ( so I did! ) I give it below.
. A simple routine in a normal
Sub. is given and a
Pubic Function based on that simple routine. -
I was wondering if anyone can comment on this, suggest improvements, give alternative codes or Functions. Etc.…. (….
!!!!!! - if I am really trying to mimic the .Transpose then I should have remembered what I detailed myself about the behavior of the .Transpose applied to the case of a 2 Dimensional 1 Column Array. As shown in the codes around Post # 1 and Post # 7, the .Transpose in that case had the peculiarity of returning my “Pseudo Horizontal” 1 Dimensional Array. Hence my simple Fuction will not truly reproduce the .Transpose in that case.. – it would simply return a 2 Dimensional 1 row Array
So How about 2 codes .. An actual Transpose Fuction and a “Dot”Transpose Fuction
Code: Fuction Transpose
Function FuctionTranspose(inArr() As Variant) As Variant 'Only variant can return a collection ( Array ). A data field is expected to be returned, which always is as Variant definiert
Dim outArr() As Variant: ReDim outArr(1 To UBound(inArr(), 2), 1 To UBound(inArr(), 1)) 'Output Array , in non dynamic , we know the size and will enter each string in so can Dimension it (ReDim must be used simply as Dim only takes numbers, not variables
Dim j As Long, i As Long 'Loop Bound Count variable in Map Co-ordinates'( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.
For j = 1 To UBound(inArr(), 1) 'For each "row" of Input array..
For i = 1 To UBound(inArr, 2) 'go down each column of input Array.
outArr(i, j) = inArr(j, i) 'Put input array values in transposed co-ordinates of Output Array
Next i
Next j
Let FuctionTranspose = outArr() '=FuctionTranspose in code will return transposed Input Array. A data Field is required, and types of dynamic and un dynamic Arrays must match. The inly combination that works is variant
End Function 'FuctionTranspose
Code: Fuction “Dot”Transpose
'
Function FuctionDotTranspose(inArr() As Variant) As Variant 'Only variant can return a collection ( Array ). A data field is expected to be returned, which always is as Variant definiert
Dim outArr() As Variant: ReDim outArr(1 To UBound(inArr(), 2), 1 To UBound(inArr(), 1)) 'Output Array , in non dynamic , we know the size and will enter each string in so can Dimension it (ReDim must be used simply as Dim only takes numbers, not variables
Dim j As Long 'Used in both Loops below. Loop Bound Count variable in Map Co-ordinates'( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.
'###Case 1 ) Input Array is 1 column 2 Dimensiona l Array...
If UBound(inArr(), 2) = 1 Then '...This is required to truly mimmic the behaviour of the .Transpose Function which, in the case of a working with a 2 Dimensional 1 Column Array, returns a 1 Dimensional "Pseudo Horizontal" Array, not a 2 Dimensional 1 Row Array as is the case with a somple Transpose routine.
Dim OneDArr() As Variant: ReDim OneDArr(1 To UBound(inArr(), 1))
For j = 1 To UBound(inArr(), 1) 'For each column "down" in 2 Dimensional 1 column Array...
Let OneDArr(j) = inArr(j, 1) '...put value from 2 Dimensional 1 column Array in next space "to the right " in 1 Dimensional "psuedo Horizontal" Array
Next j 'Next "Row" in 2 Dimension Array or next "space to right" in 1 Dimensional Array
Let FuctionDotTranspose = OneDArr() ''=FuctionDotTranspose in code will return transposed Input Array. A data Field is required, and types of dynamic and un dynamic Arrays must match. The only combination that works is variant
Else '###Case 2) Input Array has more than 1 Column... The followin does simple Transposes by swaping indicies, J and i
Dim i As Long 'Loop Bound Count variable in Map Co-ordinates'( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.
For j = 1 To UBound(inArr(), 1) 'For each "row" of Input array..
For i = 1 To UBound(inArr, 2) 'go down each column of input Array.
Let outArr(i, j) = inArr(j, i) 'Put input array values in transposed co-ordinates of Output Array
Next i 'Go to next "column" of input Array ( which is also next "row" of output Array )
Next j 'Go to next "row" of input Array ( which is also next "column" of input Array
Let FuctionDotTranspose = outArr() '=FuctionDotTranspose in code will return transposed Input Array. A data Field is required, and types of dynamic and un dynamic Arrays must match. The only combination that works is variant
End If
End Function 'FuctionDotTranspose
Bookmarks