Arrays

Arrays are containers of variable. Arrays can either be one dimensional or multi dimensional, which we will explain later in an example. It is also important to remember that arrays are great to use when you need to access and work with large sets of data during the execution of your VBA module. Accessing arrays is also a lot faster than accessing data contained on a spreadsheet as the data is already contained in the computer’s memory.

Arrays can be defined using different variables types. To understand variable declaration a bit better refer to the general VBA guidance on the ExcelHelp site.

The Basics:

So, what is a one dimensional array and how do we declare it?

Dim arrCars(5) As String

With this command we just declared an array called arrCars with 6 variable slots. Note that I said 6 as the first slot or space is represented by the value 0.

How do we load values into the array?

We load and access the array values by referencing their array index values. For example, to load to values into the array we would do the following:

arrCars(0) = “Pontiac”

arrCars(1) = “BMW”

Now let’s access the values stored in the array. Let’s say we want to access array values and assign them to other variables to use. We would do that as follows:

Dim tmpVariable As String

tmpVariable = arrCars(0) ‘We have just assigned the value ‘Pontiac’ to do the tmpVariable varable.

What is a multi dimensional array and how do we access it?

Multi dimensional arrays are declared as follows. For the purpose of our example I will use the same array name.

Dim arrCars(5,5) As String

If we want to graphically display the array values it would look as follows.

# # # # #

# # # # #

# # # # #

# # o # #

# # # # #

# # # # #

So let’s assume we want to assign a value to the o location. We would do that as follows:

arrCars(3,3) = “Truck”

Now that we are more familiar with arrays there are a number of key functions that are important to remember when working with arrays.

· You can re-initialize an array subsequent to having initialized it for the first time. You do that using the redim command. Usually you would lose all the values contained in the array when you re-initialize it. However, you can use the preserve command in conjunction with the redim command to resize the array and still retain the values.

Using our example, let’s say we want to re-initialize the array to resize it to 10. That way the array contains 11 slots. We would do that as follows: redim preserve arrCars(10). It is however important to remember that you cannot redim a multi dimensional array after you have initialized it.

· To identify the lowest and highest index value in the array we use the commands lbound (lower boundary) and ubound (upper boundary). Using our initial variable example lbound(arrCars) would equal 0 and ubound(arrCars) would equal 5. Be sure not to forget the 0 starting position.

For our example I am going to load values from a worksheet into an array. I am going to load the data in two stages re-initializing the array to load the values as I load it from the worksheet.

Code

Dim A As Long 'The variable we will use to process through the rows

Dim arrCars() As String 'If I initialize the array with a value I cannot redim / resize it again

Dim Counter As Long 'Counter to use to use when populating our array

ReDim arrCars(4)

Counter = 0

For A = 2 To 6

arrCars(Counter) = ActiveSheet.Cells(A, 1).Value

Counter = Counter + 1 'We increment the counter value with 1

Next

'Now that we have 5 values in our array. Let's load the other values

ReDim Preserve arrCars(UBound(arrCars) + 5) 'Very easy way just to add 5 more values to our starting 5

Counter = 5

For A = 11 To 15

arrCars(Counter) = ActiveSheet.Cells(A, 1).Value

Counter = Counter + 1 'We increment the counter value with 1

Next

'Now let's just write the values to the adjuscent column

Counter = 2

For A = LBound(arrCars) To UBound(arrCars)

ActiveSheet.Cells(Counter, 2).Value = arrCars(A)

Counter = Counter + 1

Next

Output