Since function countArrayItems 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
'**********************************************************************************************************
' Name: countArrayItems
' Author: mielk | 2012-06-21
'
' Comment: Function to count the number of items within the given array.
'
' Parameters:
' arr Array which items are to be counted.
'
' Returns:
' Long The number of items within the given VBA array.
'
'
' Exceptions:
' NotArrayException
' Thrown if the given parameter is not an array.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-11-17 Artur Krawczyński Function created.
'**********************************************************************************************************
Public Function countArrayItems(arr As Variant) As Long
Const METHOD_NAME As String = "countArrayItems"
'------------------------------------------------------------------------------------------------------
Dim bDimensions As Byte
Dim iDimension As Byte
'------------------------------------------------------------------------------------------------------
'Check if the given parameter [arr] is an array. If not, the code will be moved to
'NotArrayException label.
If Not VBA.IsArray(arr) Then GoTo NotArrayException
'Function checks if the given array is declared (so if its number of dimensions is greater than 0).
bDimensions = countDimensions(arr)
'If the given array is not declared, [bDimensions] variable is assigned with 1 as a base for
'multiplying process.
If bDimensions Then countArrayItems = 1
'Multiply sizes of all dimensions of the given array in order to get the total number of items.
For iDimension = 1 To bDimensions
countArrayItems = countArrayItems * (UBound(arr, iDimension) - LBound(arr, iDimension) + 1)
Next iDimension
'==========================================================================================================
ExitPoint:
Exit Function
'----------------------------------------------------------------------------------------------------------
NotArrayException:
'(...)
'Put your own error handling here for a case if the given parameter is not an array.
GoTo ExitPoint
End Function