Size of an Array in Excel VBA
To get the size of an array in Excel VBA, use the UBound function and the LBound function.
Place a command button on your worksheet and add the following code lines:
1. First, we need to declare the array. Our array has two dimensions. It consists of 5 rows and 2 columns. Also declare two variables of type Integer.
The array may look like this.
2. Next, we get the size of the array. Add the following code lines:
y = UBound(Films, 2) - LBound(Films, 2) + 1
UBound(Films, 1) gives the upper limit of the first dimension, which is 5.
LBound(Films, 1) gives the lower limit of the first dimension, which is 1.
UBound(Films, 2) gives the upper limit of the second dimension, which is 2.
LBound(Films, 2) gives the lower limit of the second dimension, which is 1.
As a result, x equals 5 and y equals 2.
3. We use a MsgBox to display the number of elements of the array.
Result:
