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

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

**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:**

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 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:**

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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 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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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:

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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).

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**

1 2 3 4 5 6 7 8 9 |
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:**

1 2 3 4 5 6 7 8 9 |
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:**

1 2 3 4 5 6 7 |
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:**

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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.