How to find the length of an array in VBA in Excel

Visual Basic for Applications (VBA) is a programming language used in Excel and other Office applications to automate tasks and create more sophisticated models and tools. VBA allows users to develop custom functions and algorithms that can be used within Excel, which can be particularly useful for complex or repetitive tasks. It also allows users to automate many aspects of Excel, such as formatting, data entry, and calculations, which can save time and improve the accuracy of their work. 

Sometimes, while working with VBA we need to find the number of total elements in a data range that we have acquired from Excel sheet or any other source and then take a necessary decision based on that value. In VBA two types of arrays can be used i.e., one dimensional and two dimensional.

In this tutorial we’ll learn how to find the length of both one and two dimensional arrays by using VBA. There is no straightforward method for this task. So, we’ll have to do it by using the Upper and Lower bound functions of VBA.

Method 1: length of one dimensional array

Step 1 – Create formula using Upper and Lower bounds of the array 

• Use the following functions to find the Upper and Lower bound of the array.

Ubound(ArrayName, [dimension])

Lbound(ArrayName, [dimension])

ArrayName is the name of the array whose upper and lower boundary is to be found and dimension is the argument to define if the array is one or two dimensional array. Default value is 1.

• Now subtract Upper bound from Lower bound to get the difference and then add 1 to get the exact length. We’ll use the following simple code

Step 2 – Execute the code to find the length of array

• To execute the code, press the play action button in the menu item list or just press F5. This will execute the code and you will get the length of the array as shown above.

Method 2: Length of two dimensional array

Step 1 – Create formula using Upper and Lower bounds of the array 

• Use the following functions to find the Upper and Lower bound of the array.

Ubound(ArrayName, [dimension])

Lbound(ArrayName, [dimension])

ArrayName is the name of the array whose upper and lower boundary is to be found and dimension is the argument to define if the array is a one or two dimensional array. Default value is 1.

• We’ll subtract the Upper bound from the Lower bound of both dimensions to get the difference and then add 1 to get the exact length. Then we’ll multiply both dimensions to get the final length of a two dimensional array. We’ll use the following simple code

Step 2 – Execute the code to find the length of array

• To execute the code, press the play action button in the menu item list or just press F5. This will execute the code and you will get the length of the array as shown above.