Arrays in Microsoft Excel: An Overview
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.
preserve command in conjunction with the redim command to resize the array and still retain the values.
redim a multi dimensional array after you have initialized it.
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
