How to get Array Length/Size in VBA

Get array length in VBA

You may get the array length (size) by using the UBound function.

Following is the syntax of the UBound function:

An example of getting array size

In the following example,

• We declare 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:

Sub Arrays_length()

Dim arr_days(7) As String

'Creating array elements
arr_days(0) = "Sat"
arr_days(1) = "Sun"
arr_days(2) = "Mon"
arr_days(3) = "Tue"

'Display Array Length
arr_len = UBound(arr_days) + 1

MsgBox "Array has " & arr_len & " elements."


End Sub

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 the array index starts at 2 or more?

If we have an array like this:

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:

Sub arr_ex()

Dim arr_days(2 To 7) As String

Dim arrLen As Integer


'Creating array elements
arr_days(2) = "Sat"
arr_days(3) = "Sun"
arr_days(4) = "Mon"
arr_days(5) = "Tue"
arr_days(6) = "Tue"
arr_days(7) = "Wed"


'Display Array Length
arrLen = UBound(arr_days) - LBound(arr_days) + 1

MsgBox "Array has " & arrLen & " elements."

End Sub

Output:

Getting the array size of a two-dimensional array

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

Sub Arrays_ex()

'Creating a 2D array
Dim arr_products(1 To 6, 1 To 3) As String
Dim i As Integer, j As Integer, k As Integer

'Using UBound and multiply
i = UBound(arr_products, 1)
j = UBound(arr_products, 2)

k = i * j

MsgBox "2-D Array has " & k & " elements."

End Sub

Result:

What if array index starts beyond 1?

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

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

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

See the following solution for our 2D array:

Sub Arrays_ex()

'Creating a 2D array
Dim arr_products(3 To 6, 2 To 3) As String
Dim i As Integer, j As Integer, k As Integer

'Using UBound and LBound

i = UBound(arr_products, 1) - LBound(arr_products, 1) + 1
j = UBound(arr_products, 2) - LBound(arr_products, 2) + 1

k = i * j

MsgBox "2-D Array has " & k & " elements."

End Sub

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).

Following is our two-dimensional array:

Assign the cell values as array elements

For x = 1 To 8

    For y = 1 To 4

        arr_products(x, y) = Cells(x, y).Value

    Next y

Next x

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

For i = 3 To UBound(arr_products)

    Str_Prods = Str_Prods & arr_products(i, 2) & "    " & arr_products(i, 3) & vbNewLine

Next i

'Display cells/element

MsgBox Str_Prods

Get the 2-D array length:

i = UBound(arr_products, 1) - LBound(arr_products, 1) + 1

j = UBound(arr_products, 2) - LBound(arr_products, 2) + 1

k = i * j

MsgBox "2-D Array has " & k & " elements."

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

Sub Arrays_ex()

'Creating a 2D array
Dim arr_products(3 To 6, 2 To 3) As String
Dim x As Integer, y As Integer
Dim i As Integer, j As Integer, k As Integer

'Assigning cell values as array element values

For x = 3 To 6

    For y = 2 To 3

        arr_products(x, y) = Cells(x, y).Value

    Next y

Next x

'Displaying whole array in Message Box

For i = 3 To UBound(arr_products)
    Str_Prods = Str_Prods & arr_products(i, 2) & "    " & arr_products(i, 3) & vbNewLine
Next i

'Display cells/element
MsgBox Str_Prods

'Using UBound and LBound to get array length

i = UBound(arr_products, 1) - LBound(arr_products, 1) + 1

j = UBound(arr_products, 2) - LBound(arr_products, 2) + 1

k = i * j

'Display array length

MsgBox "2-D Array has " & k & " elements."

End Sub

Output:

Message box 1:

Message box 2:

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