EDUCBA

Home VBA VBA Resources VBA Array Functions VBA Array Length

VBA Array Length

Madhuri Thakur
Article byMadhuri Thakur

VBA Array length

Introduction to VBA Array Length

Basically, an array is a set of elements that is in two dimensions. In excel we use arrays in our day to day lives. To calculate the length of an array in excel we either do it manually or use some functions to do so. But how do we get the length of an array in Excel VBA? We use two separate functions to do so. Lbound and Ubound functions are used to get an array length in excel VBA.

 

 

So we discussed above that we use Lbound and Ubound functions to get array length. But what are Lbound and Ubound functions. Lbound stands for lower bound and Ubound stands for Upper bound. Array length is also identified by function arr.length.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

First, let us see how an array looks like. Look at the screenshot below for the same.

Array Example

Above is an array with four rows and two columns. Now how will we find the length of this array or other sizes we will learn in this topic.

First, ensure we have the developer’s tab enabled so that we can use Excel VBA.

How to Use Excel VBA Array Length?

Now let us try on some examples on how to find array length in excel VBA.

You can download this VBA Array Length Excel Template here – VBA Array Length Excel Template

Example #1 – VBA Array Length

First, imagine we have an array size of four elements in each row and columns i.e. four rows and four columns. So the array size would be 16. Array length is the calculated product of a number of rows and columns. Let us do this in Excel VBA.

Step 1: Enter VB editor by going in the developer’s tab and then clicking on visual basic as follows.

Array length Developer

Step 2: Once we are inside the VB editor let us insert a new module which will open code window for us.

Module

Step 3: Double click on the module we just inserted which will open code window for us. Now we can start writing the code by declaring a Sub Function.

Code:

Sub Sample()

End Sub

VBA Array Length 1.1

Step 4: Now declare an array as an integer as follows.

Code:

Sub Sample()

Dim arr(3, 3) As Integer

End Sub

VBA Array Length 1.2

Step 5: Now use the console. writeline and arr.length function to find the array length as follows.

Code:

Sub Sample()

Dim arr(3, 3) As Integer
MsgBox Application.CountA(arr)

End Sub

Example 1.3

Step 6: When we run the above code we get 16 as output as 16 is the length of the integer.

VBA Length 1.1

Example #2 – VBA Array Length

In the above method, we used arr.length method which is not ideal in many cases. We will use the traditional Lbound an Ubound method to find the array length. I have some data in sheet 1 as follows.

Array length 2

In the above screenshot, we have an array and we want to find the size of this array in VBA. We will follow similar steps from example 1 on how to enter the VB editor as follows.

Step 1: Enter VB editor by going in the developer’s tab and then clicking on visual basic as follows.

VBA Array Developer 2

Step 2: Click on insert tab and add a new module.

Module 2

Step 3: Now we can start writing the code by declaring a sub-function.

Code:

Sub Sample1()

End Sub

VBA Array Length 2.2

Step 4: Now first we need to declare an array for our data above and two another integers as follows.

Code:

Sub Sample1()

Dim Grades(1 To 5, 1 To 2) As String, x As Integer, y As Integer

End Sub

Example 2.3

Step 5: Now as we have a size of the array we give an upper and lower limit to the dimension by the following code.

Code:

Sub Sample1()

Dim Grades(1 To 5, 1 To 2) As String, x As Integer, y As Integer
x = UBound(Grades, 1) - LBound(Grades, 1) + 1
y = UBound(Grades, 2) - LBound(Grades, 2) + 1

End Sub

Example 2.4

Step 6: Now let’s use the Msgbox function to display the size of the array from the data above.

Code:

Sub Sample1()

Dim Grades(1 To 5, 1 To 2) As String, x As Integer, y As Integer
x = UBound(Grades, 1) - LBound(Grades, 1) + 1
y = UBound(Grades, 2) - LBound(Grades, 2) + 1
MsgBox "This array Has " & x * y & " Data"

End Sub

Example 2.5

Step 7: Now run the code from the run button or press F5. When we run the code we see the following result,

VBA Array Example 1

Now we can check from the data it has 5 rows and two columns and each cell has one data so total there are 10 data.

Example #3 – VBA Array Length

Now let us try to find the size of an array in another example. Now we have data in another sheet.

Example 3.1

Now the data has one more rows from the above example 2. Let us try to find out the size of this array. Follow the following steps,

Step 1: Enter VB editor by going in the developer’s tab and then clicking on visual basic as follows,

VBA Array Example 3.1

Step 2: Double click on the module we just inserted which will open code window for us. Now we can start writing the code by declaring a Sub Function.

Code:

Sub Sample2()

End Sub

VBA Array Length 3.2

Step 3: Now similarly declare an array and two different variables as integers.

Code:

Sub Sample2()

Dim Dept(1 To 6, 1 To 2) As String, x As Integer, y As Integer

End Sub

VBA Array Length 3.3

Step 4: Now use Lbound and Ubound function to find the size of an array as follows.

Code:

Sub Sample2()

Dim Dept(1 To 6, 1 To 2) As String, x As Integer, y As Integer
x = UBound(Dept, 1) - LBound(Dept, 1) + 1
y = UBound(Dept, 2) - LBound(Dept, 2) + 1

End Sub

VBA Array Length 3.4

Step 5: Now use the Msgbox function to display the size of the array.

Code:

Sub Sample2()

Dim Dept(1 To 6, 1 To 2) As String, x As Integer, y As Integer
x = UBound(Dept, 1) - LBound(Dept, 1) + 1
y = UBound(Dept, 2) - LBound(Dept, 2) + 1
MsgBox "This array size is " & x * y

End Sub

VBA Array Length 3.5

Step 6: When we run the code we get the following result,

VBA Array 3

Things to Remember

There are few things which we need to keep in mind for VBA Array length as follows:

  • To find a length of an array we need to declare the array first.
  • We use Lbound and Ubound function to find the length of an array.
  • An array is a set of elements in two dimensions.

Conclusion

  • Now as we discussed earlier what is an array. It is a set of elements in two dimensions. So in excel VBA, we can use Lbound and Ubound function to find the size of the array length.
  • How do we calculate the array size. It is the product of a number of rows to the number of columns.
  • How to use the VBA Array Length Function.
  • In the above examples, we have learned how to use Ubound and Lbound function as follows.
  • UBound(Array, 1) – LBound(Array, 1) + 1

Recommended Articles

This is a guide to VBA Array Length. Here we have discussed how to use Excel VBA Array Length along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Active Cell
  2. VBA Delete Row
  3. VBA Transpose
  4. VBA LBound