How to Get Length of Array in Excel VBA?
Last Updated : 22 Nov, 2021
We use UBound and LBound functions to get the length of an Array in Excel VBA. In this article, we will discuss them in detail.
Syntax: UBound() function
UBound (arrayname, [ dimension ])
Parameters:
- arrayname: required. Array variable name
- dimension: optional
Returns: Return upper limit of an array dimension.
Syntax: LBound() Function
LBound (arrayname, [ dimension ])
Parameters:
- arrayname : required. Array variable name
- dimension : optional
Returns: Return lower limit of an array dimension
Sample Data:
VBA Code to get the length of Array (one-dimensional array):
Declare Variables:
Declaring a customer array with the size of 10.
Sub oneDimArrayLength() ' Array variable Declaration Dim customer (1 To 10) As String
Assign values to array elements
customer(1) = "ANTON" customer(2) = "BERGS" customer(3) = "BOLID" customer(4) = "KOENE" customer(5) = "FRANS"
Use UBound function to get the size of an array and Message box to display the result
'Message box to popup length of 1D array MsgBox "Array has " & UBound(customer) & " element(s)." End Sub
To Run VBA Code
Press Alt+F8 to popup macro window. Select " oneDimArrayLength" and Click Run button.
Output
VBA Code to get the length of Array (multi-dimensional array)
Declaring variables:
Declaring ProdAndCustomer multi-dimensional array size of 10 rows and 2 columns
Sub twoDimArrayLength() ' Array variable Declaration Dim ProdAndCustomer(1 To 10, 1 To 2) As String, noOfRow As Integer, noOfCol As Integer, noOfElements As Integer
Assign values to array elements
ProdAndCustomer(1, 1) = "Alice Mutton" ProdAndCustomer(2, 1) = "Boston Crab Meat" ProdAndCustomer(3, 1) = "Camembert Pierrot" ProdAndCustomer(4, 1) = "Alice Mutton" ProdAndCustomer(5, 1) = "Ipoh Coffee" ProdAndCustomer(1, 2) = "ANTON" ProdAndCustomer(2, 2) = "BERGS" ProdAndCustomer(3, 2) = "BOLID" ProdAndCustomer(4, 2) = "BOTTM" ProdAndCustomer(5, 2) = "FURIB"
Compute Number of Rows, Number of Columns using UBound and LBound function. Multiply by noOfRow and noOfCol variable to get Number of elements in multi-dimensional array.
noOfRow = UBound(ProdAndCustomer, 1) - LBound(ProdAndCustomer, 1) + 1 noOfCol = UBound(ProdAndCustomer, 2) - LBound(ProdAndCustomer, 2) + 1 noOfElements = noOfRow * noOfCol
Message box to popup result
'Message box to popup length of 1D array MsgBox "Array has " & noOfElements & " element(s)." End Sub
To Run VBA Code
Press Alt+F8 to popup macro window. Select " twoDimArrayLength" and Click Run button.
Output:
Similar Reads
How to Get Value of Multidimensional Array in C?
Prerequisite: Array in C An array is a type of data structure where we can store multiple elements of similar data types. A multidimensional array can be termed an array of arrays that stores homogeneous data in tabular form. Data in multidimensional arrays are stored in row-major order. Declaration
8 min read
How to Use for Each Loop in Excel VBA?
A For Each loop is used to execute a statement or a set of statements for each element in an array or collection. Syntax: For Each element In group [ statements ] [ Exit For ] [ statements ] Next [ element ] The For...Each...Next statement syntax has the following three parts: PartDescriptionelement
3 min read
How to find length of data frame in R
In this article, we will see What is a Data Frame and how to find the length of a data frame in R programming Language. Return the length (total number of rows) of the Data Frame using nrow()nrow() function is used to return the number of rows of the specified object (Matrix/DataFrame etc). we will
3 min read
How to Get a List of User Defined Functions in Excel VBA?
Microsoft provides many built-in functions to speed up your work in Excel. However, you can technically create functions using VBA coding called "user-defined functions" (UDFs). Also known as a "user-defined function" in Excel VBA. A formula that can be accessed from a worksheet using a piece of cod
9 min read
How to Delete a Module in Excel VBA?
Have you ever seen a text file in a notepad or a source code file in visual studio code? These all are just basic files where you can write some code or information. Similarly, we have modules in excel, each module has its code window, where you can write some code and allow it to automate your repe
3 min read
How to Declare and Initialize String Array in Excel VBA?
A string array is an array where we can store only string values in the array, with the help of a string array, we can store more than one string value. We can declare the string array in many ways like declaring a static string array, declaring a variant size array of string using the Array functio
2 min read
How to Convert VBA Collections to Array in Excel?
An object that can store a number of values whether it can be of string data type or integer which can be easily manipulated or iterated is called Collection. On the other hand, Array is also used to store the data but it is multidimensional but collections are single dimensions. Now, we will see ho
1 min read
How to Run Code from a Module in Excel VBA
VBA Macro is for developers. In Excel, the macro is a piece of code written in VBA and VBA is Microsoft's programming language, it stands for Visual Basic for Applications. The module is a file with a .bcf extension that stores the code written in the Visual Basic for Applications editor. Let's lear
4 min read
How to Insert and Run VBA Code in Excel?
In Excel VBA stands for (Visual Basic for Application Code) where we can automate our task with help of codes and codes that will manipulate(like inserting, creating, or deleting a row, column, or graph) the data in a worksheet or workbook. With the help of VBA, we can also automate the task in exce
2 min read
How to Use For Next Loop in Excel VBA?
If you are familiar with the programming you must have an idea of what a loop is, In computer programming, a loop is a sequence of statements that are repeated until a specific condition is satisfied. In Excel VBA the "For Next" loop is used to go through a block of code a specific number of times.
4 min read