What is an Array?

An array is a memory location capable of storing more than one value. The values must all be of the same data type. Let's say you want to store a list of your favourite beverages in a single variable, you can use an array to do that.

By using an array, you can refer to the related values by the same name. You can use an index or subscript to tell them apart. The individual values are referred as the elements of the array. They are contiguous from index 0 through the highest index value.

This tutorial assumes you are using Microsoft Excel version 2013. The knowledge still applies to other versions of Microsoft Excel as well.

In this tutorial, you will learn-

• What are Advantages of arrays?
• Types of arrays
• VBA Array Demonstrated with Example
• Testing our application

What are Advantages of arrays?

The following are some of the benefits offered by arrays

1. Group logically related data together – let's say you want to store a list of students. You can use a single array variable that has separate locations for student categories i.e. kinder garden, primary, secondary, high school, etc.
2. Arrays make it easy to write maintainable code. For the same logically related data, it allows you to define a single variable, instead of defining more than one variable.
3. Better performance – once an array has been defined, it is faster to retrieve, sort, and modify data.

Types of arrays

VBA supports two types of arrays namely;

• Static – These types of arrays have a fixed pre-determined number of elements that can be stored. One cannot change the size of the data type of a Static Array. These are useful when you want to work with known entities such as the number of days in a week, gender, etc.

  For Example: Dim ArrayMonth(12) As String

• Dynamic – These types of arrays do not have a fixed pre-determined number of elements that can be stored. These are useful when working with entities that you cannot predetermine the number.

  For Example: Dim ArrayMonth() As Variant

Syntax for declaring arrays

Static arrays

The syntax for declaring STATIC arrays is as follows:

Dim arrayName (n) as datatype

HERE,

Dynamic arrays

The syntax for declaring DYNAMIC arrays is as follows:

Dim arrayName() as datatype
ReDim arrayName(4)

HERE,

Array Dimensions

An array can be one dimension, two dimensions or multidimensional.

• One dimension: In this dimension, the array uses only one index. For example, a number of people of each age.
• Two dimensions: In this dimension, the array uses two indexes. For example, a number of students in each class. It requires number of classes and student number in each class
• Multi-dimension: In this dimension, the array uses more than two indexes. For example, temperatures during the daytime. ( 30, 40, 20).

VBA Array Demonstrated with Example

We will create a simple application. This application populates an Excel sheet with data from an array variable. In this example, we are going to do following things.

• Create a new Microsoft Excel workbook and save it as Excel Macro-Enabled Workbook (*.xlsm)
• Add a command button to the workbook
• Set the name and caption properties of the command button
• Write the code that populates the Excel sheet

Let do this exercise step by step,

Step 1 – Create a new workbook

1. Open Microsoft Excel
2. Save the new workbook as VBA Arrays.xlsm

Step 2 – Add a command button

Note: This section assumes you are familiar with the process of creating an interface in excel. If you are not familiar, read the tutorial VBA Subroutines and Functions. It will show you how to create the interface

1. Add a command button to the sheet

1. Set the name property to cmdLoadBeverages
2. Set the caption property to Load Beverages

Your GUI should now be as follows

Step 3 – Save the file

1. Click on save as button
2. Choose Excel Macro-Enabled Workbook (*.xlsm) as shown in the image below

Step 4 – Write the code

We will now write the code for our application

1. Right click on Load Beverages button and select view code
2. Add the following code to the click event of cmdLoadBeverages

Private Sub cmdLoadBeverages_Click()
    Dim Drinks(1 To 4) As String
     
    Drinks(1) = "Pepsi"
    Drinks(2) = "Coke"
    Drinks(3) = "Fanta"
    Drinks(4) = "Juice"
     
    Sheet1.Cells(1, 1).Value = "My Favorite Beverages"
    Sheet1.Cells(2, 1).Value = Drinks(1)
    Sheet1.Cells(3, 1).Value = Drinks(2)
    Sheet1.Cells(4, 1).Value = Drinks(3)
    Sheet1.Cells(5, 1).Value = Drinks(4)
End Sub

HERE,

Testing our application

Select the developer tab and ensure that the Design mode button is "off." The indicator is, it will have a white background and not a coloured (greenish) background. (See image below)

Click on Load Beverages button

You will get the following results

Download Excel containing above code

Download the above Excel Code

Summary

1. An array is a variable capable of storing more than one value
2. VBA supports static and dynamic arrays
3. Arrays make it easy to write maintainable code compared to declaring a lot of variables for data that is logically related.