Since function arraySize 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
'**********************************************************************************************************
' Name: arraySize
' Author: mielk | 2014-07-15
'
' Comment: Function returns the size of the specified dimension of the given array.
'
' Parameters:
' arr Array which size is to be returned.
' dimension Dimension for which the size is to be returned.
' This parameter is optional. If it is skipped, the size of first dimension will
' be returned.
'
'
' Returns:
' Long Size of the given array in the specified dimension.
'
' Examples:
' -----------------------------------------------------------------------------------
'
'
'
' Exceptions:
' NotArrayException Thrown if the given parameter [arr] is not an array.
' NotDefinedArrayException Thrown if the given paremeter [arr] is an array, but it has not been
' initialized yet.
' IndexOutOfBoundException Thrown if the parameter [dimension] exceeds the number of dimensions
' in the given base array [arr].
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-07-15 mielk Method created.
'**********************************************************************************************************
Public Function arraySize(arr As Variant, Optional dimension As Integer = 1) As Long
Const METHOD_NAME As String = "arraySize"
'------------------------------------------------------------------------------------------------------
'Check if the given parameter [arr] is an array and if it has been already initialized. -------------|
'If it is not an array, code is moved to the label NotArrayException. '|
'If it is an array, but has not been initialized yet, code is moved to the label '|
'NotDefinedArrayException. '|
If Not VBA.IsArray(arr) Then GoTo NotArrayException '|
If Not isDefinedArray(arr) Then GoTo NotDefinedArrayException '|
'----------------------------------------------------------------------------------------------------|
'Compare parameter [dimension] with the number of dimensions in the given array [arr]. If it is -----|
'greater than the number of dimensions, IndexOutOfBoundException is thrown. '|
If dimension > countDimensions(arr) Then GoTo IndexOutOfBoundException '|
'----------------------------------------------------------------------------------------------------|
'Calculate the size of the given array in the given dimension. --------------------------------------|
arraySize = UBound(arr, dimension) - LBound(arr, dimension) + 1 '|
'----------------------------------------------------------------------------------------------------|
'==========================================================================================================
ExitPoint:
Exit Function
'----------------------------------------------------------------------------------------------------------
NotArrayException:
'Error handling for the case if the given parameter [arr] is not an array.
GoTo ExitPoint
NotDefinedArrayException:
'Error handling for the case if the given parameter [arr] is an array but it has not been
'initialized yet.
GoTo ExitPoint
IndexOutOfBoundException:
'Error handling for the case if the given dimension exceeds the number of dimensions in the given
'array [arr]
GoTo ExitPoint
End Function