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 these 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.
- Make sure to have an array declared properly with rows and columns.
- After that, two more variables (as we have a two-dimensional array) to store the bounds of the array.
- 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.
- As you have a two-dimensional array you need to get bound for both of the dimensions and set that value to the variables.
- 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 have shared with you at the starting of the post.
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 have 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 the situations, so it’s always good to use the method that we have 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)."
More on VBA Arrays
VBA Add New Value to the Array | VBA Clear Array | VBA Loop Through an Array | VBA Multi-Dimensional Array | VBA Range to an Array | VBA Search for a Value in an Array | VBA Sort Array | VBA Array with Strings | VBA Dynamic Array | ISARRAY Function | ARRAY Function | VBA Arrays