Since function joinArrays 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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
'**********************************************************************************************************
' Name: joinArrays
' Author: mielk | 2013-04-26
'
' Comment: Joins the given arrays into a single array.
' Function works only with 1D and 2D arrays and can join up to 30 arrays.
'
' Parameters:
' arrays Arrays to be joined. Function can join up to 30 arrays.
' All non-array parameters are ignored.
' All the arrays given to this function should have the same number of dimensions
' (you cannot mix up 1D and 2D arrays).
'
' Returns:
' Variant() The array that is the combination of all arrays given in arrays parameter.
' -----------------------------------------------------------------------------------
' [1D arrays]
' Function returns 1D arrays with so many items as the sum of items in all given
' source arrays.
'
' Example:
' ---------------
' Assuming the following arrays have been given to this function (with dimensions
' specified in brackets):
' - arr1(1 To 3) 1D array with 3 elements,
' - arr2(10 To 15) 1D array with 6 elements,
' - arr3(-2 To 2) 1D array with 5 elements.
' 1D array with 14 elements will be returned.
'
' -----------------------------------------------------------------------------------
' [2D arrays]
' 2D arrays can have different number of columns.
' Final array always have as many columns as the widest from the source arrays and as
' many rows as the sum of rows in all source arrays.
' NOTE! First dimension of the array is considered to be a 'column'.
'
' Example:
' ---------------
' Assuming the following arrays have been given to this function (with dimensions
' specified in brackets):
' - arr1(1 To 10, 1 To 2) array with 10 columns and 2 rows
' - arr2(3 To 5, 1 To 10) array with 3 columns and 10 rows
' - arr3(-2 To 6, 4 To 10) array with 8 columns and 7 rows
' The result array will have 10 columns (the number of columns equal to the number of
' columns in the widest source array - arr1 in this example) and 19 rows (sum of the
' rows in all source arrays).
'
' NOTE! To properly join the arrays created directly from Excel worksheet range:
' arr = Range(Cells(row, col), Cells(row2, col2))
' first you have to transpose them before passing then to this function.
' You can use transposeArray function, presented on our website to avoid the errors
' generated in some specific cases by Excel built-in function Transpose (i.e. if
' the array to be transposed has more than 32767 rows or the content of any array
' element is longer than 256 characters.
'
'
' By default, the result array is indexed from 1. You can change it, by modifying
' FIRST_INDEX constant defined in this function.
'
'
'
' Exceptions:
' DifferentDimensionsException Thrown if the arrays given to this function have different number of
' dimensions, i.e. some arrays are 1D and others are 2D.
'
' TooManyDimensionsException Thrown if any of the arrays given to the function has more than
' 2 dimensions.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-04-26 mielk Method created.
'**********************************************************************************************************
Public Function joinArrays(ParamArray arrays() As Variant) As Variant()
Const METHOD_NAME As String = "joinArrays"
Const FIRST_INDEX As Byte = 1
'------------------------------------------------------------------------------------------------------
Dim arr As Variant
Dim correctArrays() As Variant
Dim bytArraysCounter As Byte
Dim bytMaxDim As Byte
Dim bytArrayDimension As Byte
'------------------------------------------------------------------------------------------------------
'Checks every single source array given to this function. -------------------------------------------|
'Each item that is not an array or is an empty array is ignored. '|
'This loop checks also if all the arrays have the same number of dimensions. '|
For Each arr In arrays '|
'|
'Check if the current item in the loop is a defined array. If not, it is ignored. -----------| '|
If isDefinedArray(arr) Then '| '|
bytArrayDimension = countDimensions(arr) '| '|
'| '|
'If variable [bytMaxDim] is greater than 0, it means the array currently checked ----| '| '|
'is not the first array in the collection and the final number of dimensions has '| '| '|
'been already worked out. If the number of dimensions of this source array is '| '| '|
'different than the one worked out before, it means the given array have different '| '| '|
'number of dimensions and exception DifferentDimensionsException is thrown. '| '| '|
If bytMaxDim > 0 And bytArrayDimension <> bytMaxDim Then '| '| '|
'| '| '|
GoTo DifferentDimensionsException '| '| '|
'| '| '|
Else '| '| '|
'| '| '|
'If the array currently analyzed is the first array in the collection of '| '| '|
'arrays to be joined, its number of dimension is used to work out the final '| '| '|
'number of dimensions. The number of dimensions of other arrays in the '| '| '|
'collection will be compared to that number. '| '| '|
If bytMaxDim = 0 Then bytMaxDim = bytArrayDimension '| '| '|
'| '| '|
bytArraysCounter = bytArraysCounter + 1 '| '| '|
ReDim Preserve correctArrays(1 To bytArraysCounter) '| '| '|
correctArrays(bytArraysCounter) = arr '| '| '|
'| '| '|
End If '| '| '|
'------------------------------------------------------------------------------------| '| '|
'| '|
End If '| '|
'--------------------------------------------------------------------------------------------| '|
'|
Next arr '|
'----------------------------------------------------------------------------------------------------|
'For performance reasons, there are separate subfunctions to join 1D and 2D arrays. -----------------|
Select Case bytMaxDim '|
Case 1: joinArrays = joinArrays1D(FIRST_INDEX, correctArrays) '|
Case 2: joinArrays = joinArrays2D(FIRST_INDEX, correctArrays) '|
Case Else: GoTo TooManyDimensionsException '|
End Select '|
'---------------------------------------------------------------------------------------------------|
'==========================================================================================================
ExitPoint:
Exit Function
'--------------------------------------------------------------------------
DifferentDimensionsException:
'(...)
'Put your own error handling here for a case if the given parameter is not a dictionary.
GoTo ExitPoint
TooManyDimensionsException:
'(...)
'Put your own error handling here for a case if the given parameter is not a dictionary.
GoTo ExitPoint
End Function
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
'**********************************************************************************************************
' Name: joinArrays1D
' Author: mielk | 2013-04-26
'
' Comment: Subfunction used to join 1D arrays.
'
' Parameters:
' firstIndex Specified the first index of the result array.
' arrays() Arrays to be joined.
'
' Returns:
' Variant() The array that is the combination of all arrays given to this subfunction.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-04-26 mielk Method created.
'**********************************************************************************************************
Private Function joinArrays1D(firstIndex As Byte, arrays() As Variant) As Variant()
Const METHOD_NAME As String = "joinArrays1D"
'------------------------------------------------------------------------------------------------------
Dim arr As Variant
Dim combined() As Variant
Dim lngRows As Long
Dim lngResultRow As Long
Dim lngSourceRow As Long
'------------------------------------------------------------------------------------------------------
'Loop to count the total number of elements in all source arrays. -----------------------------------|
For Each arr In arrays '|
lngRows = lngRows + UBound(arr, 1) - LBound(arr, 1) + 1 '|
Next arr '|
'----------------------------------------------------------------------------------------------------|
'Create a temporary 1D array and resize it to have exactly as many
'elements as the sum of all elements in all source arrays.
ReDim combined(firstIndex To lngRows + firstIndex - 1)
'For each source array a loop iterates through all elements of this ---------------------------------|
'array and adds them to the final array. '|
lngResultRow = firstIndex '|
For Each arr In arrays '|
'|
'--------------------------------------------------------------------------------------------| '|
For lngSourceRow = LBound(arr, 1) To UBound(arr, 1) '| '|
combined(lngResultRow) = arr(lngSourceRow) '| '|
lngResultRow = lngResultRow + 1 '| '|
Next lngSourceRow '| '|
'--------------------------------------------------------------------------------------------| '|
'|
Next arr '|
'----------------------------------------------------------------------------------------------------|
joinArrays1D = combined
End Function
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
'**********************************************************************************************************
' Name: joinArrays2D
' Author: mielk | 2013-04-26
'
' Comment: Subfunction used to join 2D arrays.
'
' Parameters:
' firstIndex Specified the first index of the result array.
' arrays() 2D arrays to be joined.
'
' Returns:
' Variant() The array that is the combination of all arrays given to this subfunction.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-04-26 mielk Method created.
'**********************************************************************************************************
Private Function joinArrays2D(firstIndex As Byte, arrays() As Variant) As Variant()
Const METHOD_NAME As String = "joinArrays2D"
'------------------------------------------------------------------------------------------------------
Dim arr As Variant
Dim combined() As Variant
Dim lngColumnsCount As Long
Dim lngMaxCol As Long
Dim lngRows As Long
Dim lngResultRow As Long
Dim lngSourceRow As Long
Dim lngResultCol As Long
Dim lngSourceCol As Long
'------------------------------------------------------------------------------------------------------
'Loop to count the total number of rows in all source arrays and to ---------------------------------|
'work out the number of columns in the result array. '|
For Each arr In arrays '|
lngColumnsCount = UBound(arr, 1) - LBound(arr, 1) + 1 '|
If lngColumnsCount > lngMaxCol Then lngMaxCol = lngColumnsCount '|
lngRows = lngRows + UBound(arr, 2) - LBound(arr, 2) + 1 '|
Next arr '|
'----------------------------------------------------------------------------------------------------|
'Create a temporary 2D array and resize it to have the number of rows -------------------------------|
'and columns worked out before. '|
ReDim Preserve combined(firstIndex To lngMaxCol + firstIndex - 1, _
firstIndex To lngRows + firstIndex - 1) '|
'----------------------------------------------------------------------------------------------------|
'For each source array a loop iterates through all elements of this ---------------------------------|
'array and adds them to the final array. '|
lngResultRow = firstIndex '|
For Each arr In arrays '|
'|
'--------------------------------------------------------------------------------------------| '|
For lngSourceRow = LBound(arr, 2) To UBound(arr, 2) '| '|
lngResultCol = firstIndex '| '|
'| '|
'------------------------------------------------------------------------------------| '| '|
For lngSourceCol = LBound(arr, 1) To UBound(arr, 1) '| '| '|
combined(lngResultCol, lngResultRow) = arr(lngSourceCol, lngSourceRow) '| '| '|
lngResultCol = lngResultCol + 1 '| '| '|
Next lngSourceCol '| '| '|
'------------------------------------------------------------------------------------| '| '|
'| '|
lngResultRow = lngResultRow + 1 '| '|
'| '|
Next lngSourceRow '| '|
'--------------------------------------------------------------------------------------------| '|
'|
Next arr '|
'----------------------------------------------------------------------------------------------------|
joinArrays2D = combined
End Function