Menu
Friday, August 15, 2014
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 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”.
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: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.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.
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:
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().
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”
The adjusted code from the previous sections is:
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:
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: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
Subscribe to:
Posts (Atom)