EasyExcelVBA.com

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 larger data sets such as lists/tables in the Excel VBA code itself. In this chapter we will review the use of the following Excel VBA elements:

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:

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 any 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”.

Option Explicit
Option Compare Text

Sub SimpleArray()

    Dim PrimaryColours(2) As String

    PrimaryColours(0) = "blue"
    PrimaryColours(1) = "yellow"
    PrimaryColours(2) = "red"
    
    Range("PrimaryColours") = PrimaryColours

End Sub

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

Figure 1: Result from array with primary colours in spreadsheet

One of the first things you might have noticed in the above Figure 1 is that the array PrimaryColours was declared with one dimension of size (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

When working with Excel VBA I do not find it intuitive to start with index number zero as for example the first cell in the spreadsheet has coordinates (1, 1). To maintain consistency with this I generally 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 and in all code for this tutorial. Of course this is a personal preference, so you can also choose to leave it out and use the default base of zero, as long as you are consistent to avoid errors. Using Option Base 1 the same piece of code would look like:

Option Explicit
Option Compare Text
Option Base 1

Sub SimpleArray()

    Dim PrimaryColours(3) As String

    PrimaryColours(1) = "blue"
    PrimaryColours(2) = "yellow"
    PrimaryColours(3) = "red"
    
    Range("PrimaryColours") = PrimaryColours

End Sub

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 2: 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:

Option Explicit
Option Compare Text
Option Base 1

Sub SimpleArray()

    Dim PrimaryColours(1, 3) As String

    PrimaryColours(1, 1) = "blue"
    PrimaryColours(1, 2) = "yellow"
    PrimaryColours(1, 3) = "red"
    
    Range("PrimaryColours") = PrimaryColours

End Sub

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 a Static Array, I therefore generally do the dimensioning with constants. The following code example shows the same code when using constants to declare the PrimaryColours array.

Option Explicit
Option Compare Text
Option Base 1

Sub SimpleArray()

    Const PCNumRows = 1
    Const PCNumCols = 3
    
    Dim PrimaryColours(PCNumRows, PCNumCols) As String

    PrimaryColours(1, 1) = "blue"
    PrimaryColours(1, 2) = "yellow"
    PrimaryColours(1, 3) = "red"
    
    Range("PrimaryColours") = PrimaryColours

End Sub

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. Just remember it helps detecting typos by dimensioning the variables with some capital letters and then type code only lower case, so you can immediately see after each line of code you write if the variables are recognized as they should automatically switch to the declared capitalisation.

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.

Sub SimpleArrayDynamic()

  Dim PrimaryColours() As String

  Dim PCNumRows As Integer
  Dim PCNumCols As Integer

  PCNumRows = 1
  PCNumCols = 3

  ReDim PrimaryColours(PCNumRows, PCNumCols)

  PrimaryColours(1, 1) = "blue"
  PrimaryColours(1, 2) = "yellow"
  PrimaryColours(1, 3) = "red"

  Range("PrimaryColours") = PrimaryColours

End Sub

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:

ReDim Preserve PrimaryColours(PCNumRows, PCNumCols - 1)

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 and free up the memory:

Erase PrimaryColours

Transfer data between array and worksheet 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. Write “blue”, “yellow” and “red” (without quotation marks) in cells B2, B3, B4 respectively. The predefined VBA function we will use to change the colour names from lower to upper case is UCase() , which basically works the same as the Excel function UPPER() (more about VBA functions in Chapter 7).

Sub SimpleArrayTransferWithCells()

'declare the constants for the size of the array
Const PCNumRows = 1
Const PCNumCols = 3

'declare the variables
Dim PrimaryColours(PCNumRows, PCNumCols) As String
Dim RowCounter As Integer
Dim ColCounter As Integer

'read the inputs from the spreadsheet to the PrimaryColours array in VBA
For RowCounter = 1 To PCNumRows
    For ColCounter = 1 To PCNumCols
        PrimaryColours(RowCounter, ColCounter) = _
        Cells(RowCounter + 1, ColCounter + 1)
    Next ColCounter
Next RowCounter

'make the values uppercase with the UCase() function and write in the spreadsheet
For RowCounter = 1 To PCNumRows
    For ColCounter = 1 To PCNumCols
        Cells(RowCounter + 3, ColCounter + 1) = _
        UCase(PrimaryColours(RowCounter, ColCounter))
    Next ColCounter
Next RowCounter
        
End Sub

The result in the spreadsheet should look like:

Figure 3: 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 you want to import get moved in the sheet (e.g. inserted or deleted rows or columns) 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 worksheet 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):

If you have correctly renamed the ranges then 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:

Sub SimpleArrayImportFromSheet()

'declare the constants for the size of the array
Const PCNumRows = 1
Const PCNumCols = 3

'declare the variables
Dim PrimaryColours(PCNumRows, PCNumCols) As String
Dim RowCounter As Integer
Dim ColCounter As Integer

'read the inputs from the spreadsheet to the PrimaryColours array in VBA
PrimaryColours = Range("LowerCasePrimaryColours")

'make the values uppercase with the UCase() function
For RowCounter = 1 To PCNumRows
    For ColCounter = 1 To PCNumCols
        PrimaryColours(RowCounter, ColCounter) = _
        UCase(PrimaryColours(RowCounter, ColCounter))
    Next ColCounter
Next RowCounter
        
'write the upper case results in the spreadsheet
Range("UpperCasePrimaryColours") = PrimaryColours
          
End Sub

Note I used an underscore to continue a command on the next line to make it fit on the page.

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 and will give the compile error message “Can't assign to array” (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 (so its size can be determined by variables) and the data type should coincide. The Excel spreadsheet itself is a Variant while we declared a Static Array of the String data 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:

Sub SimpleArrayImportFromSheetWithTempLong()

'declare the constants for the size of the array
Const PCNumRows = 1
Const PCNumCols = 3

'declare the variables
Dim PrimaryColours(PCNumRows, PCNumCols) As String
Dim RowCounter As Integer
Dim ColCounter As Integer
Dim TempPrimaryColours As Variant

'read the inputs from the spreadsheet to the PrimaryColours array in VBA
TempPrimaryColours = Range("LowerCasePrimaryColours")

For RowCounter = 1 To PCNumRows
    For ColCounter = 1 To PCNumCols
        PrimaryColours(RowCounter, ColCounter) = _
        TempPrimaryColours(RowCounter, ColCounter)
    Next ColCounter
Next RowCounter

'make the values uppercase with the UCase() function
For RowCounter = 1 To PCNumRows
    For ColCounter = 1 To PCNumCols
        PrimaryColours(RowCounter, ColCounter) = _
        UCase(PrimaryColours(RowCounter, ColCounter))
    Next ColCounter
Next RowCounter
        
'write the upper case results in the spreadsheet
Range("UpperCasePrimaryColours") = PrimaryColours
          
End Sub

“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() function in another For… Next loop. This Sub can be simplified as it is not necessary to first transfer all values to “PrimaryColours” before applying the UCase() function. In the following example I have combined transferring the contents from “TempPrimaryColours” to “PrimaryColours” with the function UCase().

Sub SimpleArrayImportFromSheetWithTemShort()

'declare the constants for the size of the array
Const PCNumRows = 1
Const PCNumCols = 3

'declare the variables
Dim PrimaryColours(PCNumRows, PCNumCols) As String
Dim RowCounter As Integer
Dim ColCounter As Integer
Dim TempPrimaryColours As Variant

'read the inputs from the spreadsheet to the TempPrimaryColours array in VBA
TempPrimaryColours = Range("LowerCasePrimaryColours")

'convert to upper case and store in PrimaryColours
For RowCounter = 1 To PCNumRows
    For ColCounter = 1 To PCNumCols
        PrimaryColours(RowCounter, ColCounter) = _
        UCase(TempPrimaryColours(RowCounter, ColCounter))
    Next ColCounter
Next RowCounter
        
'write the upper case results in the spreadsheet
Range("UpperCasePrimaryColours") = PrimaryColours
          
End Sub

In this chapter we have used UCase(), which is a predefined Excel VBA function. In the next chapter we will look at how to write a custom function in Excel VBA and used either in the code or in the Excel worksheets.

Next: Chapter 7: How to write functions

Easy Excel VBA Tutorial: Contents