VBA Array Length (Size)

Last Updated: July 03, 2023
puneet-gogia-excel-champs

- Written by Puneet

In VBA, to get the length of an array means to count the number of elements you have in that array. For this, you need to know the lowest element and the highest element. So, to get this you can use the UBOUND and LBOUND functions that return the upper bound and lower bound, respectively.

Apart from that, you can also use the COUNTA which is a worksheet function. And in this tutorial, we will see both of the methods so you can use any of them at your convenience.

Steps to Get the Size of an Array

Here we have an array that contains a list of months and sales quantity for each month.

vba-array-length
  1. Make sure to have an array declared properly with rows and columns.
  2. After that, two more variables (as we have a two-dimensional array) to store the bounds of the array.
  3. Next, you need to use a formula where you have to use the Ubound function to get the upper bound and then Lbound to get the lower bound of the array.
  4. As you have a two-dimensional array you need to get bound for both of the dimensions and set that value to the variables.
  5. In the end, multiply lengths that you have got from the Ubound and Lbound as upper and lower bound.

Here’s the full code.

Dim yearSales(1 To 12, 1 To 2) As Integer
Dim iCount1 As Integer, iCount2 As Integer
   
iCount1 = UBound(yearSales, 1) - LBound(yearSales, 1) + 1
iCount2 = UBound(yearSales, 2) - LBound(yearSales, 2) + 1
   
MsgBox iCount1 * iCount2

Note: You must be wondering that we have a total of 13 rows in the array that I shared with you at the start of the post.

But we have used an array with 13 rows because the first row was a heading. And here we have used an IF STATEMENT and ISEMPTY function to check if the declared array has zero elements.

Dim yearSales(1 To 12, 1 To 2) As Integer
Dim iCount1 As Integer, iCount2 As Integer
If IsEmpty(yearSales) = 0 Then
    MsgBox "This array has zero elements."
Else
    iCount1 = UBound(yearSales, 1) - LBound(yearSales, 1) + 1
    iCount2 = UBound(yearSales, 2) - LBound(yearSales, 2) + 1
    MsgBox "This array has " & iCount1 * iCount2 & " element(s)."

Using COUNTA to get the Length of the Array

As you know that an array is a bunch of elements that are structured in a single or multi-dimensional way and you can use the COUNTA function (worksheet function) to count these elements in one go.

In the following code, you have used the same array that you declared earlier and then used a variable to store the element count returned by the function.

And as you can see the result it has returned is 24 that’s the count of the total number of elements that we have in the array.

Dim yearSales(1 To 12, 1 To 2) As Integer
iCount = WorksheetFunction.CountA(yearSales)
MsgBox iCount

There’s one thing that you need to take care of that this method won’t be ideal to use in all situations, so it’s always good to use the method that we discussed earlier.

You can also write a code to check first if the declared array is not blank.

Dim yearSales(1 To 12, 1 To 2) As Integer
If IsEmpty(yearSales) = 0 Then
    MsgBox "This array has zero elements."
Else
    iCount = WorksheetFunction.CountA(yearSales)
    MsgBox "This array has " & iCount & " element(s)."