Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is an Array?
3.
One Dimensional Array
4.
Two Dimensional Array
5.
Dynamic Array
6.
Array Function
7.
How to get the size of an Array?
8.
Frequently Asked Questions
9.
Conclusion
Last Updated: Mar 27, 2024
Easy

Array in VBA

Author Ankit Mishra
0 upvote
Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

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:

animals = array( “Table”, “Man”, “Dog”, “4”, “Paint”)

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

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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

  1. 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.
  2. How is an array declared in VBA?
    Dim Myarray (Lowerbound To UpperBound) As DataType
  3. 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.
  4. 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.

To learn more about Excel you can refer to this blog, also to get some basic idea about the terms of excel you can checkout these, WorksheetExcel-Sheets, and Function and Sub.

Recommended problems -

 

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enroll in our courses, refer to the mock test and problems; look at the interview experiences and interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow.

“Happy Coding!”

 

 

Live masterclass