Excel VBA Beginner Tutorial - Fundamentals

Excel VBA Tutorial Chapter 6: Arrays

Excel VBA Tutorial Chapter 6: Arrays

Goal of this chapter

Up to this point we have used variables in the VBA code and lists/tables in the excel spreadsheet. The natural next step are Arrays which are the way to keep data in lists/tables in the Excel VBA code. In this chapter we will review the use of the following Excel VBA elements:

• Arrays
• Declare a Static Array
• Option Base 1
• Using Constants to declare Static Arrays
• Declare a Dynamic Array
• Transfer data between Array and sheet
• Comments

Arrays

In Chapter 2 we discussed Variables. Our definition of a variable is: a storage location with one particular value. As such, the variable “Greeting” could contain for example “Hello”, “Good morning”, etc.
An Array is a Variable holding one to many values, organised in one to many dimensions. As an illustration, multiple sized arrays as you could find them in Excel are:

• A column is a one dimensional array (dimension: row)
• A table is a two dimensional array (dimensions: row, column)
• A table repeated on different sheets is a three dimensional array (dimensions: row, column, sheet)
• A table repeated in different sheets and workbooks is a four dimensional array (dimensions: row, column, sheet, workbook)

Arrays can practically have as many dimensions as you want. Keep in mind though that the size of the Array will increase rapidly by adding a dimension. The number of cells in the four dimensional Array above for example would be number of rows * number of columns * number of sheets * number of workbooks.
Arrays come in two basic forms: Static and Dynamic. A Static Array has one particular size that is set in the declaration, which is maintained throughout the VBA code. A Dynamic Array can be resized in function of events in the code execution. Though a Dynamic Array is more flexible, a Static Array is often easier to use as you know exactly its dimensions at any point of the code execution.

Declare a Static Array

Like a Variable an Array requires declaration. In addition to what we already reviewed in Chapter 2 in the declaration of Variables, we also need to define the size in the declaration of a Static Array.  The below Sub gives an example where we define a one dimensional Array called “PrimaryColours” which can hold three String values. We then put contents into the Array and return it to the spreadsheet in a named range, also called “ PrimaryColours”. We reviewed Naming Excel Ranges in Chapter 2. In this example, we name the Range B2:D2 as “PrimaryColours”.

Figure 1: array with the values of the three primary colours

The result in the Range “ PrimaryColours” in the spreadsheet should look as follows:

Figure 2: Result from array with primary colours in spreadsheet

One of the first things you might have noticed in the above Figure is that the Array PrimaryColours was declared with dimension of (2). This reads as if it can hold only two values. The reason is that Excel VBA by default expects Arrays to begin with the index number 0. In this case we defined an Array PrimaryColours with three values (which are index 0, 1 and 2).

Option Base 1

As I do not find it intuitive to start with index number zero I prefer to start the indexing of the elements in the Arrays with 1. To change this default setting in your module you need to add a Module level statement called Option Base 1. I do this as part of my basic settings whenever I write a new macro. Together with Option Explicit and Option Compare Text these are the 3 standard settings in practically all my modules. Using Option Base 1 the same piece of code would look like:

Figure 3: Array using Option Base 1

The next detail to note in the code is that it does not explicitly identify the rows and colums of the “PrimaryColours” Array. The Excel spreadsheet is two dimensional while the Array we created is one dimensional. We are able to paste the result into the spreadsheet though since we correctly assumed one row and three columns. This is not obvious in the code though. A common mistake would be to try and paste the Array in one column with three rows. For instance, what you will see if you redefine the Range(“PrimaryColours”) to be Range B2:B4 is:

Figure 4: Erroneous result if using a spreadsheet column instead of row.

So the first value is repeated three times in the first column. The other two values are not shown in column C and D (they would if we had defined Range(“PrimaryColours”) to be B2:D4). In order to avoid this issue, when transferring information between a one dimensional Array and the two dimensional Excel spreadsheet, it is good practice to define all Arrays as two dimensional. If we redefine the PrimaryColours as a two dimensional Array with one row and three columns our code would be:

Figure 5: Explicitly define the rows and columns.

Note that this also provides the option to define the Array as having three rows and one column, should you want to paste the result later as a column into the Excel spreadsheet. To do this, declare the Array as PrimaryColours(3,1) and make corresponding changes in the remainder of the code. For example, "yellow" should be assigned to PrimaryColours(2,1) in that case.

Using Constants to declare Static Arrays

As discussed in Chapter 5, once the amount of code increases it becomes necessary to define Constants in order to be able to maintain the code efficiently. In case of an Static Array, I therefore generally do the dimensioning with constants. The following Figure shows the same code when using constants to declare the PrimaryColours Array.

Figure 6: Use Constants to define the size of an Array.

For a Static Array such as PrimaryColours we can only use Constants to declare the Array. Variables are not allowed. In case we need to size the Array on the basis of Variables we need to use Dynamic Arrays instead. The names I used are in accordance with what I generally use as a naming convention: one or two capital letters that indicate which Array, followed by “NumRows”  and “NumCols”. But it is entirely up to you what names to use for this purpose as these are not required to be written in any particular syntax in Excel VBA.

Declare a Dynamic Array

In contrast to a Static Array, when declaring a Dynamic Array it is not necessary to define the dimensions of the Array. Simply adding "()"indicates it is an Array you are declaring. Also, independent if you give the Dynamic Array dimensions in the declaration, it is always possible to resize a Dynamic Array during code execution in a Sub with the ReDim statement, on the basis of both constants or variables. Even the amount of dimensions and type can be changed.
The following code amends the above example to declare PrimaryColours as a Dynamic Array. Note it also uses Variables to define the size of the array, something not allowed in the Static Array definition.

Figure 7: A Dynamic Array declared with Variables.

Whenever you use ReDim it will empty the contents of the Dynamic Array. If we want to resize the Dynamic Array and keep its contents we need to use the "ReDim Preserve" statement. For example, the following statement will decrease the size of the Array with one column, but maintain the contents of the first two columns:

Figure 8: Use the ReDim Preserve statement to keep the contents of an Array while resizing.

You can only apply ReDim Preserve to change the size of the Array, not the the type or the amount of dimensions. Also, any data that existed in eliminated elements is lost.
One final note on the use of Public Dynamic Arrays. Due to the way in which system memory is allocated to a Dynamic Array it is necessary to include an Erase statement to free the memory allocated to Public Dynamic Arrays after their use. The Erase statement can only be used within a subprocedure. Would PrimaryColours have been defined with a Public statement at the module level then we should include the following statement after exporting the contents to the sheet:

Figure 9: Use the Erase statement to free the memory used by Public Dynamic Arrays.

Transfer data between Array and Sheet with Cells object

In the last sections of this chapter we review how to import input data from an Excel sheet into an Array and export the results back to the sheet. The first common approach is to use the Cells object, which we reviewed in Chapter 5.
To continue our colours example from previous sections, let’s assume we want to read the lower case primary colours from a sheet, change them to upper case and return them to a different section on the same sheet. Enter “blue”, “yellow” and “red” (without quotation marks) in cells B2, B3, B4 respectively. The VBA function we will use to change the colour names from lower to upper case is UCase().

Figure 10: Transfer data between Array and Sheet using the Cells object

The result in the spreadsheet should look like:

Figure 11: Result in spreadsheet after executing correct VBA code.

In this manner the values from the cells are transferred to the Array one-by-one. Though a viable option for quick, simple calculation this method has some issues. Firstly, if the contents get moved in the sheet the Cells reference needs to be manually adjusted in the code as well. This will become very cumbersome once you get a larger workbook with many references. Secondly, each time data is transferred between a sheet and a macro it takes considerable system resources. That means it takes for example much more time to read a cell and transfer 200 times, than to read 200 cells in one go. This is not noticeable as a user in the above example with three cells, but is becomes time consuming once you get to hunderds or thousands of cells (until the point that the Cells option becomes unpractical). The solution for both issues is to use named ranges.

Transfer data between Array and Sheet with named ranges

Using named ranges the reference will move if you cut and paste a range in the Excel sheet and allows transferring data in one step. In the above sections, starting with Declare a Static Array, you have already seen how to transfer data from the Array PrimaryColours to the sheet. In comparison with how to export the values of the Array to the spreadsheet the import operation is actually a bit trickier. Let's start by writing a Sub that would make sense given what we have seen before, but actually does not work.
Rename the Excel ranges so that we have (without quotation marks):

• Range B2:D2 is named “LowerCasePrimaryColours”
• Range B4:D4 is named “UpperCasePrimaryColours”

If you have correctly renamed the ranges the Name Box on the top left should display the name when either range is selected. Again, enter “blue”, “yellow” and “red” (without quotation marks) in cells B2, B3, B4 respectively.
The adjusted code from the previous sections is:

Figure 12: Invalid code to import contents for an Array from a spreadsheet

While this subprocedure might make sense on the basis of what we have seen previously, it does not work when you try to run it (to reset the debugger click the square “stop” symbol in the taskbar of the VBA editing window). There are two reasons: you can only assign a spreadsheet range in this manner to a Dynamic Array (it size can be determined by variables) and the type should coincide. The Excel spreadsheet is a Variant while our Static Array is of the String type.
We therefore perform the import from the spreadsheet via an intermediate step by first going from the spreadsheet to a (unsized, hence dynamic) Variant Array and from the Variant Array to the String Array. Generally I would define the Variant Array with the same name as the one I want to use ultimately, preceded by “Temp”. In this case “TempPrimaryColours”. An adjusted version to resolve our issue is therefore:

Figure13: Correct method to import data from a sheet into an Array

“TempPrimaryColours” picks up the full contents from the Range(“LowerCasePrimaryColours”) in one step. Then, to transfer the values to “PrimaryColours”, we need to loop through all the values (as always assigning from right to left). Once all the values are in “PrimaryColours” we perform the UCase() operation in another For… Next loop.This Sub can be simplified as it is not necessary to first transfer all values to “PrimaryColours” before performing the UCase() operation. In the following example I have combined transferring the contents from “TempPrimaryColours” to “PrimaryColours” with the operation UCase().

Figure 14: Shorter version combining the import of data and UCase operation.

Comments

Since the code is becoming quite lengthy by this stage of the tutorial I will finish the chapter with a short description in how to use Comments in the Excel VBA code. Anything written in the Excel VBA editor that is preceded by a ‘ (single quote) will be viewed as commentary and not compiled as part of executable code. It is good practice to add comments to your code in order to help others (and yourself if you have a memory like mine) understand what your code is aimed to achieve. If I would add comments to the example code it would look like:

Figure 14: Example code with comments.

It is not necessary to explain in the comments what each line does as that should be clear from the code itself. For example, I did not mention we use the UCase() function, just what the objective is of the piece of code. Therefore, someone could replace that part of the code with an alternative version with the same result. I also added a line between the declaration section and the operations.
Another benefit of using the single quote is that you can temporarily change executable code to non-executable comments while you are writing a macro or debugging an existing one. To make this process efficient with large pieces of code there are two buttons on the Edit Toolbar in the VBA editing window, called "Comment Block" and "Uncomment Block". You select a section of code and with the help of these buttons a single quote will be added to or removed from the start of each line. If you do not have the Edit Toolbar in your VBA editing window, go to "View" -> "Toolbars" -> "Edit".
Next: Chapter 7: How to write functions

Easy Excel VBA Tutorial: Contents

• Introduction: How to get started
• Chapter 1: How to record a macro
• Chapter 2: How to write a macro
• Chapter 3: Condition statements
• Chapter 4: Repeat statements
• Chapter 5: Combine condition and repeat statements
• Chapter 6: How to use Arrays
• Chapter 7: How to write functions