Excel VBA Arrays

An array can store one or more values in memory and the values should be of the same data type. The elements of an array are the individual values in the collection of an array. They are stored in a continuous order indexing from 0 to nth value. Now, let’s see the types, advantages of the vba array in excel.

Advantages of VBA array:

VBA array function’s benefits:

1. Grouping logically related data

If you are storing a list of groceries such as fruits, vegetables etc. Then, you can use a single array variable that has a separate location for categories i.e., vegetables, fruits.

2. Easy to maintain the codes

It’s easy to maintain the code because defining a single variable is enough, instead of defining more than one variable.

3. Better performance

Retrieving, sorting, and modifying the data in an array are faster.

Array Types in VBA:

There are two types of arrays and those are:

1. Static – Static type array will have a fixed number of elements and it can’t be changed further. These kinds of arrays are efficient when the user is working with the fixed entities. Some of the examples of static arrays are Gender, Number of months in a year, Number of days in a week etc.

Static array syntax:

Dim arrayName (n) as datatype

This syntax declares an array name with the size of n variables and the data type refers to the type of data in an array.
Example:

Dim ArrayGender(3) As String

2. Dynamic – Dynamic type of an array will not have a fixed number of elements and it can be changed further also. These kinds of arrays are useful when the user is not sure about the size of the entities. Some of the examples of dynamic array are employee_id, color_names

Dynamic array syntax

Dim arrayName() as datatype
ReDim arrayName(4)

This “Dim arrayName() as datatype” syntax declares an array with a name and the data type. “ReDim arrayName(4)” declares the array size.

Example:

Dim ArrayEmployee() As Variant

VBA Array Declaration

An array variable uses parenthesis while declaration but otherwise the declaration of an array variable is the same as the other variables. There are three methods to declare an array:

Method 1: Variant Arrays

Dim arrEmployees()

In this method, we are declaring an array without mentioning the size. With a variant type array, the user need not define an array size.

Method 2: Non – Variant Arrays

Dim arrEmployees()
Dim arrEmployees() A s String
ReDim arrEmployees(1 to 100)
End Sub

With non-variant arrays, the user can define the array size before assigning the values to the array. Here, the user uses the ReDim command to set the size of an array.

Method 3: Static Array

Dim arrEmployees(100)

In this method, we are declaring an array mentioning the array size of 100. Although the array size is 100, it can hold 101 data values as array index starts from 0.

Note:

• Array Index cannot be less than 0 or it cannot be negative.
• VBA arrays can store any type of variable in it. A single array variable can store any data type variable such as integer, string, characters etc.

VBA Array Function

The user can quickly initialize an array in Excel VBA. The user can also change the starting index number. By default, the index number starts from 0 but the user can change it to 1 or some other number as per their convenience. In order to change the index number, you have to add option base 1 to the general declarations section.

Code:

Option Explicit
Option Base 1
Sub Button1_Click()
Dim DataFlair As Variant
DataFlair = Array("VBA Article", "Python Article", "Java Article")
MsgBox DataFlair(1)
 
End Sub

Save and run the code, you will get the following output

Assigning Values to an Array

Each data value in the array is assigned by an array index value. It can also be a string.

Sample Code:

Private Sub Employees_Click()
   Dim arrEmp(0 to 2) as String  
     arrEmp(0) = "Deepak" 
     arrEmp(1) = "Anush" 
     arrEmp(2) = "Krish"
 End Sub

Code Explanation:
Here, the code assign=s values to the elements in the array. The 0 th index is occupied by Deepak, 1st index is occupied by Anush and the 2nd index in the array is occupied by Krish. This is how a user can assign values in an array.

Multi-Dimensional Arrays in Excel VBA

Apart from single arrays, there are also multidimensional arrays and the most commonly used one is two-dimensional arrays. An array can contain a maximum of 60 dimensions.

Example
In the following example, a multi-dimensional array is declared with 2 rows and 5 columns.

Code:

Private Sub Employees_Click()
   Dim arr(2,5) as Variant	    
   arr(0,0) = "Deepti" 
   arr(0,1) = "Akkshaya"
   arr(0,2) = "Guhanesvar"           
   arr(0,3) = "Swetha" 
   arr(0,4) = "Ram"
   arr(1,0) = "Hari"           
   arr(1,1) = "Sanchez"           
   arr(1,2) = "Aarya"           
   arr(1,3) = "Sri"
   arr(1,4) = "Akshra"           
   msgbox(" The employee data in the Array index 0,1 : " &  arr(0,1))
   msgbox("The employee data in the Array index 1,2 : " &  arr(1,2))
End Sub

When you execute the function, the following output appears.
Value stored in Array index : 0 , 1 : Akkshaya
Value stored in Array index : 1 , 2 : Aarya

ReDim Statement in VBA

To declare dynamic-array variables, we use the ReDim statement. Using the redim statement, we can also allocate or reallocate the storage spaces.

Syntax

ReDim [Preserve] variable_name(boundlist) [, variable_name(boundlist)]

Parameter Description

• Preserve − When the user changes the size of the dimension, this parameter preserves the data values stored in the existing arrays. It is one of the optional parameters.
• Variable_name − It is one of the required parameters and this denotes the name of the variable. This variable name should follow the standard variable naming conventions.
• Boundlist − It is one of the required parameters and this parameter indicates the size of an array.

Example
Let’s see an example where we change the array dimension and still preserve the existing data values in the array.

Note: The data from the array will be lost if we resize the array size to a smaller size from its original size.

Code:

Private Sub Employees_Click()
   Dim a() as variant
   i = 0
   redim a(5)
   a(0) = "Sheetal"
   a(1) = “Pragya”
   a(2) = “Srithika”
  
   REDIM PRESERVE a(7)
   For i = 3 to 7
   a(i) = “DataFlair Employees”
   Next
  
   'to Fetch the output
   For i = 0 to ubound(a)
      Msgbox a(i)
   Next
End Sub

When you execute the above function, it produces the following output.

Sheetal
Pragya
Srithika
DataFlair Employees
DataFlair Employees
DataFlair Employees
DataFlair Employees
DataFlair Employees

Array Methods

Within VBScript, there are many inbuilt functions available and these functions help the users to develop arrays effectively. The below list are the methods that are used in conjunction with arrays.

Dimensions of an Array:

An array can be of 1d, 2d or multidimensional.

1. One dimensional array: When you use only one index in the array, then those arrays are known as one dimensional arrays. For example, a list of grades.

Example of One Dimensional Array

Sub Button1_Click()
Dim Grades(1 To 5) As String
Grades(1) = a
Grades(2) = b
Grades(3) = C
Grades(4) = D
Grades(5) = F
MsgBox (Grades(1))
End Sub

When you save and run the code, you get the following output

2. Two dimensional array: When you use two indexes in an array, then those arrays are known as two dimensional arrays. For example, the number of employees in each department. It requires a number of departments and the number of employees in each department.

Example of Two Dimensional Array:

Sub twodim()
Dim TwoDimension(1 To 2, 1 To 2) As Long
Dim i As Integer
Dim j As Integer


TwoDimension(1, 1) = 2016
TwoDimension(1, 2) = 2017
TwoDimension(2, 1) = 500
TwoDimension(2, 2) = 556

For i = 1 To 2
    For j = 1 To 2
        Cells(i, j) = TwoDimension(i, j)
    Next j
Next i

End Sub

When you save and run the code, you will get the following output

3. Multi-dimensional array: When you use more than two indexes then those arrays are known as multi dimensional arrays. For example, selling price, cost price, discounted price of a product.

Example of VBA Multi-Dimensional Array

Sub Multi_Dimensional()
Dim MultiDimension(1 To 3, 1 To 2) As Long
Dim i As Integer
Dim j As Integer

MultiDimension(1, 2) = 2016
MultiDimension(2, 1) = 500
MultiDimension(1, 1) = 2017
MultiDimension(2, 2) = 556
MultiDimension(3, 1) = 2018
MultiDimension(3, 2) = 680

For i = 1 To 3
    For j = 1 To 2
        Cells (i, j) = MultiDimension (i,  j)
    Next j
Next i

End Sub

When you save and run the code, you will get the following output

How to use an array in VBA?

To make use of arrays in vba excel, follow the steps:

Step 1 : Open an excel workbook and save it as excel macro-enabled workbook (*.xlsm)

Step 2 : Add a button using macros.

• In order to create a macro, go to the developer tab and click on insert from the ribbon.
• Choose the command button.
• Assign a macro by providing a name and click on the new button

Step 3: Finally, press ok

Step 4: Once, the code window appears, type the following code.

Sub DataFlair_Click()
    Dim names(1 To 4) As String
 		names(1) = "Anant"
        names(2) = "Sheetal"
        names(3) = "Linda"
        names(4) = "Himtaj"
 
        Sheet1.Cells(1, 1).Value = "Employees"
    Sheet1.Cells(2, 1).Value = names(1)
    Sheet1.Cells(3, 1).Value = names(2)
    Sheet1.Cells(4, 1).Value = names(3)
    Sheet1.Cells(5, 1).Value = names(4)
 End Sub

Here, “Dim names(1 To 4) As String” declares an array variable called names with 1 to 4 index.

Here, these statements, ‘names(1) = “Anant”

names(2) = “Sheetal”

names(3) = “Linda”

names(4) = “Himtaj”’ declares the value to each element in an array.

Here, these statements, ‘Sheet1.Cells(1, 1).Value = “Employees”

Sheet1.Cells(2, 1).Value = names(1)

Sheet1.Cells(3, 1).Value = names(2)

Sheet1.Cells(4, 1).Value = names(3)

Sheet1.Cells(5, 1).Value = names(4)’ assigns employees to the first cell in the spreadsheet and each value in the array follows

the reference and falls in order.

Step 5: Click on save button or press ctrl+s

Step 6: Close the vba code editor window

Your GUI appears as follows:

Once you click on the button, you will get the following output

VBA Array Length

The length of an array is known as array length.The function used in excel VBA to get an array length is Lbound and Ubound. The data in the array are a set of elements in two dimensions. We use arrays in day- to-day lives and so knowing the array length is one of the essentials while working on the arrays. The full form of Lbound and Ubound are lower bound and Upper bound. The function arr.length also helps in finding the array length.

Above is an array with six rows and two columns. Now let’s see how to find the length of this array.

Note: Before finding an array length, make sure that the developer’s tab is enabled.

How to Use Excel VBA Array Length?

To find the VBA Array length, follow the steps:

Step 1: Go to the developer tab and click on the visual basic icon.

Step 2: The VBA editor opens up and inside it, go to the insert tab and choose the module option.

Note: A new code window opens up.

Step 3: Write the code by declaring a sub function and an array as integer.

Code:

Sub Sample()
Dim Employees(1 To 10, 1 To 2) As String, x As Integer, y As Integer
x = UBound(Employees, 1) - LBound(Employees, 1) + 1
y = UBound(Employees, 2) - LBound(Employees, 2) + 1
MsgBox "The array declared has " & x * y & " Data values in it."
End Sub

Code Explanation:
Here, we have declared the array as employees with two integer variables. We have also mentioned the upper limit and lower limit to the array and finally we are displaying the size of the array from the code.

Step 7: Save and run the code.

The following output appears:

There are a total of 20 data with 10 rows and 2 columns.

Things to Remember

• Declare the array first to find the array length.
• Lbound and Ubound are much helpful to find the length of an array.
• The data in the array are a set of elements in two dimensions.

Advantages of an Array in VBA

• Array groups the logically related data together.
• The array makes it much easier to write code.
• Arrays give better and more effective performance. Once the array is created, it is easy to sort, retrieve and modify the data.

ArrayList in VBA

ArrayList is a list of an array with nearby memory location and the values in the array are retrieved using the index numbers. The index of an array list starts from 0 and the indexing value continues such as 1,2,3….

ArrayList also contains many inbuilt operations such as sorting, adding, removing, reversing etc. ArrayList is not a part of VBA, it is an external library which can be accessed with VBA. Excel arraylist are dynamic compared to the normal arrays.

How to add the library:

To include the library “mscorlib.dll”, follow the steps:

1: Go to the VBA editor and choose tools from the main menu.

2: Choose the references option as it contains the libraries.

3: The list of libraries appears and choose “mscorlib.dll”, tick mark on the checkbox and press ok button.

Finally, the ArrayList is added to the VBA code.

Example of excel VBA ArrayList

Sorting ArrayList Using VBA Code

Follow the steps to sort the arraylist:

Step 1: Go to the developer tab and click on the visual basic icon.

Step 2: The VBA editor opens up and inside it, go to the insert tab and choose the module option.

Note: A new code window opens up.

Step 3: Write the code by declaring a sub function to perform the sorting to the provided data values.

Sub datasort()
Dim datasort As ArrayList
Set datasort = New ArrayList
datasort.Add "100"
datasort.Add "60"
datasort.Add "80"
datasort.Add "90"
datasort.Add "20"
datasort.Add "40"
datasort.Add "10"
datasort.Add "30"
datasort.Add "70"
datasort.Add "50"
datasort.Sort
 
MsgBox (datasortsort(0) & vbCrLf & datasort(1) _
& vbCrLf & datasort(2) & vbCrLf & datasort(3) _
& vbCrLf & datasort(4) & vbCrLf & datasort(5) _
& vbCrLf & datasort(6) & vbCrLf & datasort(7) _
& vbCrLf & datasort(8) & vbCrLf & datasort(9) _
& vbCrLf & datasort(10))
End Sub

Code Explanation:
Declare a sub function to perform sorting. We have added the arraylist as an object to the function and a new instance is set to this object. Using the add method, we can insert the elements to the arraylist and we have applied the sort property to sort the list using sort method. By default, the lists are sorted in ascending order. We use vbCrLf to print the data values one by one in a new line. Finally, we use the message box to print the sorted list.

Step 4: Save and run the file.

The following output appears:

Testing the Application

Step 1: Go to the Developer tab.

Note: Check whether the design mode is on or off. If it is on, then the application wouldn’t work.

Step 2: Click on the Design Mode to turn it off.

Step 3: Click on the newly created button.

The following output appears.

Using VBA Array and Split Function

The user can use the Array function to fill the array with a list of items. The user can declare the array as a variant type. Here is a sample code using the split function.

By default, the array function will start at zero index. The user can use the split function to split a string into an array based on a delimiter. A comma or space is a delimiter character which separates the items.

The following code will split the string into an array of three elements:

Sub Articles()
Dim s As String
Dim arr() As String
s = "Excel, Python, Java"
arr = Split(s, ",")
MsgBox (arr)
End Sub

Code Explanation:
Here, the s string is split into array elements. The delimiter character comma is used to separate the items using split function. The separated items are stored in the arr variable which is also a string data type.

Using Erase with the VBA Array

The user uses the erase function on arrays to delete the items in an array or an array.

The erase function resets all the values by default in a static array. The value is set to zero if the array is made up of long integers. If the array is made up of string then they are converted to “ ”.

In a dynamic array, the memory is deallocated using the erase function. If the user wanted to use the array again then the ReDim keyword should be used to allocate the memory.

Let’s have a look at a sample.

Private Sub CommandButton1_Click()
Dim articles(0 To 2) As Integer
   articles(0) = 235
   articles(1) = 123
   articles(2) =  99
 Erase articles
    Debug.Print "Location", "Value"
    For i = LBound(articles) To UBound(articles)
        Debug.Print i, articles(i)
    Next i
End Sub

Here, the user assigns string value to each array element in an array. The erase function erases all the values in the array and sets the array value back to zero.

Summary:

• An array is a variable which stores more than one value.
• Excel VBA arrays are classified into static and dynamic arrays.
• Arrays can hold more than one value of the same data type.