Since function transposeArray uses some non-built VBA functions, they also must be included in your code for the function to work properly.
Otherwise the following error will occur: Compile error: Sub or Function not defined.
Required functions are listed below. You can get to each function's source code by clicking its name:
When adding the functions above to your VBA project, make sure you haven't done it before. If there are two different public functions with the same name in a single VBA project, the following compilation error is thrown: Compile error: Ambiguous name detected: function_name.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
'**********************************************************************************************************
' Name: transposeArray
' Author: mielk | 2012-06-21
'
' Comment: Function to transpose the given two dimensional array.
'
' There is an Excel built-in function to transpose arrays:
' Application.WorksheetFunction.Transpose. However, there are many cases when this function
' doesn't work properly and throws errors, i.e. if the array to be transposed has more
' than 65536 rows or the content of any array cell is longer than 255 characters
' ([Run-time error '13': Type mismatch] is generated by the compiler in both those cases).
'
' This function allows to get around those limitations.
'
' Parameters:
' arr The array to be transposed.
' It needs to have exactly two dimensions.
' If this parameter is not an array or have less or more than two dimensions, the exceptions
' are thrown.
'
' Returns:
' Array() The source array after being transposed.
'
'
' Exceptions:
' NotArrayException
' Thrown if the given parameter is not an array.
'
' DimensionsException
' Thrown if the array passed to this function has less or more than 2 dimensions (only
' 2-dimensional arrays can be transponed).
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-06-21 mielk Method created.
' 2015-02-15 mielk Function doesn't rely on Excel built-in transpose function anymore.
' This function returned unexpected results - all items of Data type were
' converted to String representation of those dates.
'**********************************************************************************************************
Public Function transposeArray(ByRef arr As Variant) As Variant()
Const METHOD_NAME As String = "transposeArray"
'------------------------------------------------------------------------------------------------------
Dim lngRow As Long 'Rows iterator
Dim lngCol As Long 'Columns iterator
Dim lWidth As Long
Dim lHeight As Long
Dim stWidth As Long
Dim stHeight As Long
Dim tempArray() As Variant
Dim dimensions As Integer
'------------------------------------------------------------------------------------------------------
'Checks if the given parameter [arr] is an array. If not, code execution moves
'to the NotArrayException label.
If Not isDefinedArray(arr) Then GoTo NotArrayException
'Only 2-dimensional arrays can be transponed. Function checks if the array passed as a parameter has
'exactly two dimensions. If it has less or more dimensions, code execution moves to the
'DimensionsException label.
dimensions = countDimensions(arr)
If dimensions <> 2 Then GoTo DimensionsException
stWidth = LBound(arr, 1)
stHeight = LBound(arr, 2)
lWidth = UBound(arr, 1)
lHeight = UBound(arr, 2)
'Temporary array [tempArray] is given the target size.
ReDim tempArray(stHeight To lHeight, stWidth To lWidth)
For lngRow = stWidth To lWidth
For lngCol = stHeight To lHeight
If VBA.IsObject(arr(lngRow, lngCol)) Then
Set tempArray(lngCol, lngRow) = arr(lngRow, lngCol)
Else
tempArray(lngCol, lngRow) = arr(lngRow, lngCol)
End If
Next lngCol
Next lngRow
'Newly created array [tempArray] is being assigned to the result variable.
transposeArray = tempArray
'==========================================================================================================
ExitPoint:
Exit Function
'----------------------------------------------------------------------------------------------------------
NotArrayException:
'Error handling for the case when the parameter passed to the function is not an array ...
GoTo ExitPoint
DimensionsException:
'Error handling for the case when the number of dimensions of the array passed to this function
'is different than 2 ...
GoTo ExitPoint
End Function