VBA-Lesson 13.1. Using Arrays

An array is essentially a variable that can hold many values. We have already considered this in VBA-Lesson 6.1. Data Types but let's dive even deeper now …

Why are arrays used?

Imagine that you are trying to write a procedure in which you need to store up to 500 values. If you have to create 500 separate variables, it will be very difficult. In the middle of the array, storing and working with these values will be much easier.

The second reason to use arrays is their speed. It takes significantly less time to read data from arrays than from tables (made of cells) in an Excel worksheet.

So, here's an example that will show this clearly...

On the first worksheet ("DS") there is a data set: 5000 rows by 3 columns:

On the second sheet, you will find a summary table that takes into account all "YES" answers by years and clients:

In this case, the procedure will use a loop to process the data set and record the number of "Yes" responses for each year and each customer number, and then enter this data into the appropriate cells.

Without arrays, it would take Excel 131.44 seconds to complete this procedure:

But for the first time saving the data (from the "DS" worksheet) to an array and then performing the same calculations (using the arrays instead of the "DS" worksheet dataset) will only take 1.74 seconds :

If we decide to optimize our procedure by storing only the data containing "YES" responses in the array (which is about 3/4 of the data), it would only take 1.02 seconds:

This is a good example of how using arrays allows you to perform a procedure 128 times faster. The result of our optimization would be even better if we worked with many data sets at the same time.

We will return to the details of our example at the end of the lesson.

Declaration of arrays

Below are some examples of array declarations (if the first two are not clear to you, read it):

An example of declaring a one-dimensional array
Dim array1(4)

An example of declaring a two-dimensional array
Dim array2(6, 1)

An example of declaring a dynamic array
Dim array3()

If you cannot enter fixed values when declaring arrays (because they depend on, for example, the size of the dataset), leave the brackets empty.

You don't have to declare a data type (string, long, etc.), although in many cases it will slow down your procedure...

Storing data in an array

Let's try to store some data in an array:

We want to store 11x1 values in this case, so we need to create a one-dimensional array:

'Declaration
Dim array_example(10)

Don't forget that numbering the elements in the array starts with 0 (this is standard in programming, so it's worth getting into this habit right away, although you can actually change this approach in VBA).

Each element in the array will now get its value:

"Saving values in an array."
array_example(0) = Range("A2")
array_example(1) = Range("A3")
array_example(2) = Range("A4")
array_example(3) = Range("A5")
array_example(4) = Range("A6")
array_example(5) = Range("A7")
array_example(6) = Range("A8")
array_example(7) = Range("A9")
array_example(8) = Range("A10")
array_example(9) = Range("A11")
array_example(10) = Range("A12")

You can operate on or modify each array element as if it were a normal variable.

The following is an example where we use array_example(8):

Sub example()
      "Declaration
      Dim array_example(10)
     
      "Saving values in an array."
      array_example(0) = Range("A2")
      array_example(1) = Range("A3")
      array_example(2) = Range("A4")
      array_example(3) = Range("A5")
      array_example(4) = Range("A6")
      array_example(5) = Range("A7")
      array_example(6) = Range("A8")
      array_example(7) = Range("A9")
      array_example(8) = Range("A10")
      array_example(9) = Range("A11")
      array_example(10) = Range("A12")
     
      Test 1
      MsgBox array_example(8) '=> returns : 04/02/2016
    
      'Changing one of the values
      array_example(8) = Year(array_example(8))
     
      Test 2
      MsgBox array_example(8) '=> returns : 2016
End Sub

A For loop would be a better option to save the array faster:

"Declaration
Dim array_example(10)

"Saving values in an array."
For i = 0 To 10
      array_example(i) = Range("A" & i + 2)
Next

Articles on the topic: