Do you think IIT Guwahati certified course can help you in your career?
Introduction
Excel VBA (Visual Basic for Applications) is the interface between spreadsheets and programming. While not as complex or powerful as the usual programming language, Excel VBA can be tough to master for all. At the same time, VBA's applications and capabilities are broad.
In this blog, we'll learn about the VBA array, one of Excel's most powerful features, and how to use it in our spreadsheets.
Without wasting any more time, let's get started with Array.
What is an Array?
Arrays are most commonly used to group or classify data of a similar type. It is similar to a variable in the function, except that a variable can only keep a single item, whereas an array can hold several items.
For example, if I have a list of Objects that I want to assign to a variable, I can do the following:
A = “Table”
B = “Man”
C = “Dog”
D = “4”
E = “Paint”
This is just long and tedious. Instead, I can use an array like this:
Rather than defining five different variables. We declared only one array that could store all five of our items. What's the best part? We can refer to or extract any specific element at any time. As a result, arrays are very useful for programming.
Arrays, like variables, are declared in VBA using Dim. Aside from the array name, you must also specify the number and type of values stored in the array.
The complete syntax is as follows:
Dim MyArray(6) As String
Where:
Dim = Variable/array declaration command
MyArray=Array Name
(6) = The number of values that will be stored in the array*
As String = The data type stored in the array
Note: Count begins at zero in VBA, as it does in most programming languages. As a result, (6) indicates seven stored values (from 0 to 6). If you want to count from 1 to 6, write (1 to 6) as follows:
Dim MyArray(1 to 6) As String
One Dimensional Array
The One(1)-Dimensional Array has a single index (Subscript). The one-dimensional array is made up of a list of elements of the same data type. It is made up of either a single row or a column of data. Using the index value, we read values from or into an array.
Add the following code lines to your worksheet and a command button:
Dim Animals(1 To 5) As String
Animals(1) = "Dog"
Animals(2) = "Cat"
Animals(3) = "Lion"
Animals(4) = "Panther"
Animals(5) = "Tiger"
MsgBox Animals(3)
When you press the command button on the sheet, the following is displayed:
I hope you got a fair idea about how a One-dimensional Array works.
Two Dimensional Array
Multi-dimensional arrays comprise more than one dimension, often two or three dimensions. However, arrays can have up to 32 dimensions.
The complete syntax follows:
Dim Animals(5,2) as String
Firstly, create a 2D Array and follow the steps to iterate through elements.
Add the following code lines to your worksheet and a command button:
Dim Animals(1 To 5, 1 To 2) As String
Dim i As Integer, j As Integer
For i = 1 To 5
For j = 1 To 2
Animals(i, j) = Cells(i, j).Value
Next j
Next i
MsgBox Animals(3, 2)
When you press the command button on the sheet, the following is displayed:
Dynamic Array
A dynamic array can be resized and extended while the code is being executed. In this article, we will look at how to develop code that allows you to accomplish both resizing and adding extra elements.
Let us now see how dynamic Allocation works,
The complete syntax follows
For One Dimensional
Dim NewArray() As String
ReDim NewArray(5)
For Two Dimensional
Dim NewArray() As String
ReDim NewArray(5, 8)
Firstly, declare an array and name it.
The elements count will be left void, i.e., the parenthesis left unfilled.
Use the ReDim statement now.
Finally, provide the number of elements you want to add to the array.
Array Function
In Excel VBA, the Array function can be used to quickly and effectively initialize an array.
Add the following code lines to your worksheet and a command button:
Dim cars As Variant
To assign an array to the variable departments, use the Array Function. Add the following line of code:
cars = Array("Suzuki", "Tata", "Skoda")
Add the following code line to display the element with index 1:
MsgBox cars(1)
Result
How to get the size of an Array?
The UBound and LBound functions in Excel VBA can be used to determine the size of an array.
Let us understand this with an example.
Firstly, the array must be declared. The array we're working with has two dimensions. It is made up of 5 rows and 2 columns. Declare two Integer variables as well.
Dim cars(1 To 5, 1 To 2) As String, x As Integer, y As Integer
The Array may look like this.
To determine the size of the array, add the following lines of code:
x = UBound(cars, 1) - LBound(cars, 1) + 1
y = UBound(cars, 2) - LBound(cars, 2) + 1
UBound(cars, 1) will return the upper limit of the first dimension, that is 5.
LBound(cars, 1) will return the lower limit of the first dimension, that is 1.
UBound(cars, 2) will return the upper limit of the second dimension, that is 2.
LBound(cars, 2) will return the lower limit of the second dimension, that is 1.
As a result, x will be equal to 5 and y equal to 2.
To display the number of array elements, we used a MsgBox.
MsgBox "Size is equal to" & x * y
Result
I hope you got a fair idea, of how these functions are used to return the size of an Array.
Frequently Asked Questions
How to find the length of the array in VBA? The Ubound function is used to determine the length of an array. This function returns the top subscript of an array.
How is an array declared in VBA? Dim Myarray (Lowerbound To UpperBound) As DataType
Define array variant in VBA? A variant array will accept any data type for its index, allowing you to store multiple types of values in a single array.
How Dynamic Array is used? Dim myArray() As String ReDim myArray(5, 8)
Conclusion
In this article, we have extensively discussed how Arrays are used in VBA, and we saw how Arrays are different from a regular variable. We explored one-dimensional and two-dimensional arrays with examples. We also discussed fixed and dynamic arrays.
24+ registered 
