Since function addEntry 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
'**********************************************************************************************************
' Name: addEntry
' Author: mielk | 2013-04-26
'
' Comment: Function to add the given entry to the specified 1D array.
' The given array will be expanded by one row, so this must be dynamic array.
'
' Parameters:
' arr Array where the value is to be added.
' It has to be 1D array, otherwise TooManyDimensionsException will be thrown.
' It has to be dynamic array, otherwise StaticArrayException will be thrown.
' value Value to be added. It can be value of any type (including objects).
'
'
' Exceptions:
' NotArrayException Thrown when [arr] parameter is not an array.
' TooManyDimensionsException Thrown if the given array has more than 1 dimension.
' StaticArrayException Thrown if the given array is not dynamic array and cannot be resized.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-04-26 mielk Method created.
'**********************************************************************************************************
Public Sub addEntry(ByRef arr As Variant, ByRef value As Variant)
Const METHOD_NAME As String = "addEntry"
'------------------------------------------------------------------------------------------------------
Const START_INDEX As Integer = 1 'Used to create new array if parameter [arr] is not declared
'------------------------------------------------------------------------------------------------------
Dim lowBound As Long
Dim upBound As Long
Dim lngCounter As Long
Dim varTemp As Variant
'------------------------------------------------------------------------------------------------------
'Check if the given parameter [arr] is an array and if it is dynamic. -------------------------------|
'If it is not an array, code is moved to the label NotArrayException. '|
'If it is an array, but is not dynamic, code jumps to the label StaticArrayException. '|
If Not VBA.IsArray(arr) Then GoTo NotArrayException '|
If Not isDynamicArray(arr) Then GoTo StaticArrayException '|
'----------------------------------------------------------------------------------------------------|
'Check if the given array has no more than one dimension. If it has more dimensions, code will ------|
'jump to the label NotArrayException. '|
If countDimensions(arr) > 1 Then GoTo TooManyDimensionsException '|
'----------------------------------------------------------------------------------------------------|
'Find the low and up bound of the result array. -----------------------------------------------------|
'For arrays already initialized low bound remain unchanged and up bound will be incremented by 1. '|
'For arrays not initialized yet, low bound and up bound will be equal to const START_INDEX. '|
If isDefinedArray(arr) Then '|
lowBound = LBound(arr) '|
upBound = UBound(arr) + 1 '|
Else '|
lowBound = START_INDEX '|
upBound = START_INDEX '|
End If '|
'----------------------------------------------------------------------------------------------------|
'Resize the given array using bounds calculated above and add the given parameter [value]. ----------|
ReDim Preserve arr(lowBound To upBound) '|
'|
'Before adding value to the array, it has to be checked if the value is an object ---------------| '|
'or a primitive value, because there is a difference in appending objects and non-objects. '| '|
If VBA.IsObject(value) Then '| '|
Set arr(upBound) = value '| '|
Else '| '|
arr(upBound) = value '| '|
End If '| '|
'-------------- [If VBA.IsObject(value) Then] ---------------------------------------------------| '|
'|
'----------------------------------------------------------------------------------------------------|
'==========================================================================================================
ExitPoint:
Exit Sub
'----------------------------------------------------------------------------------------------------------
NotArrayException:
'Error handler for the case if the given parameter [arr] is not an array.
GoTo ExitPoint
TooManyDimensionsException:
'Error handler for the case if the given array has more than one dimension.
GoTo ExitPoint
StaticArrayException:
'Error handler for the case if the given array is static and cannot be resized to add new item.
GoTo ExitPoint
End Sub