Excel VBA Macro to Find The Size of an Array

How to Find The Size of an Array
The largest available subscript for the indicated dimension of an array can be obtained by using theUbound function. In our one-dimensional array example, Ubound(arr) is 5.

In our two-dimensional array example above, there are two upper bound figures - both are 2.
UBound returns the following values for an array with these dimensions*:

Dim A(1 To 100, 0 To 3, -3 To 4)

Statement            Return Value
UBound(A, 1)          100
UBound(A, 2)            3
UBound(A, 3)            4

* Example taken from Excel VBA Help section.

The UBound function is used with the LBound function to determine the size of an array. Use theLBound function to find the lower limit of an array dimension.

Statement            Return Value
LBound(A, 1)            1
LBound(A, 2)            0
LBound(A, 3)           -3

To get the size of an array, use the following formula:

UBound(Arr) - LBound(Arr) + 1

For example:

Ubound(A,1) - LBound(A,1) + 1
= 100 - 1 + 1
= 100

Ubound(A,2) - LBound(A,2) + 1
= 3 - 0 + 1
= 4

Ubound(A,3) - LBound(A,3) + 1
= 4 - (-3) + 1
= 8