5 Examples of How to get Array Length/Size in VBA

Get array length in VBA

For one dimensional array, you may get the array length (size) by using the UBound function.

Following is the syntax of UBound function:

UBound(arrayname, [ dimension ])

An example to get array size

In the following example, we declared an array and assign values to its elements by index numbers.

Then UBound function is used to get the size that is displayed in the message box:

VBA Code:

Output:

We need to add 1 to the UBound result as the array index starts at 0. So, we have one more item than the returned value of UBound function.

What if array index starts at 2 or more?

If we have an array like this:

Dim arr_days(2 To 7) As String

Then UBound still returns seven, as it’s the upper number in our array. However, the actual number of elements is less.

In that case, you may use UBound with the LBound function – which returns a lower number in the array.

See the simple example below of getting array length:

Output:

Getting array size of a two-dimensional array

There may be different scenarios to get the array size of 2-D array.

Scenario 1:

If your two-dimensional array elements start at 1, for example:

Dim arr_products(1 To 5, 1 To 4) As String

In that case, you may use this simple approach to get number of elements in the 2-D array:

Result:

What if array index starts beyond 1?

Let us say, your 2-D array is declared like this:

Dim arr_products(3 To 6, 2 To 3) As String

Then our above approach fails, as UBound still returns 6 and 3 and the output remains the same.

In that case, we can use LBound function as well to get the correct array length.

LBound returns the smallest available subscript for the indicated dimension of an array.

See the following solution for our 2D array:

Output:

Working with 2-D array length and Excel sheet

Now let’s look at an Excel sheet example to have a better idea of how the above approach worked.

We have the following sample sheet:

We will create a 2-D array from that sheet and get the array length (total cells utilized).

So following is our two-dimensional array:

Dim arr_products(3 To 6, 2 To 3) As String

Assign the cell values as array elements

Display all array elements in a message box to see what we get from the Excel sheet:

Get the 2-D array length:

Let us combine all of the above code and see the outputs:

Output:

Message box 1:

Message box 2:

You can compare the cell data and array length with the sample sheet’s highlighted area above.