.jpg "Microsoft Certified Professional")
V - Arrays and Enumerations
In a previous chapter, I wrote about how to store information in Visual Basic for Applications (VBA) with variables. Another way to store values in VBA is the array. A single array can hold multiple values in a way that can be individually referenced through code. The advantage of using arrays is when you have many values in the same category, i.e. sales figures or sports scores. Instead of declaring multiple variables, you can declare a single array variable with enough places to hold all of the information. Functions can return arrays so a custom function could use an array to return multiple values or even a set of data from a table.
You declare an array just as you would a variable except that in parentheses after the variable name, you specify the number of elements to allow.
Dim MonthlySales(11) as Integer
One of the ideas that you'll need to remember in VBA and other types of programming is that numbered items often start with zero. This is referred to as being zero-based. Arrays are an example of this; array elements are numbered for reference purposes and by default, the numbering beginning with zero. Therefore, the above declaration statement actually declares an array that can hold 12 separate integer values.
If you want the array's lower limit (also called the lower boundary) to start at a number other than 0, there are two ways to do this.
- Use the Option Base statement at the top of the module. This
statement can declare the lower boundary for all arrays in the module as either 0
or 1.
Option Base 1
Option Base 0
- To set the lower boundary for a specific array, you can use the declaration itself to any value. For example:
Dim Months(1 to 12)
Dim Century(1901 to 2000)
Setting or reading information from an array is also the same as working with a standard variable except that you reference a specific element by number as shown in these examples which assume the array starts at 1.
MonthlySales(4) = 12000
JanuarySales = MonthlySales(1)
The screenshot below shows how Intellisense recognizes MonthlySales as an array variable when the first parenthesis is typed and requests an element number.
You can use a variable to indicate the element being referenced as in this example which assumes that the MonthlySales array has already been declared and each element has a value:
Dim ArrayPlace as Integer
Dim MaxSales as Integer
MaxSales = 0
For ArrayPlace = 1 to 12
If MonthlySales(ArrayPlace) > MaxSales Then
MaxSales = MonthlySales(ArrayPlace)
End If
Next ArrayPlace
MsgBox "The maximum monthly sales is: " & MaxSales & "."
The bold block is a For ... Next loop which uses the ArrayPlace value to cycle through each element in the MonthlySales array. The code then reads the value of the array element where the element number matches the value in the ArrayPlace variable. If it's greater than the value in MaxSales, then the MaxSales value is replaced. At the end, a message box announces the highest monthly sales.
Using the functions available through VBA, you could also write an expression like this one with the above array:
CurrentSales = MonthlySales(Month(Date))
The Month() function returns an integer between 1 and 12 representing the month part of the specified date so the above expression uses this function to fill in the month part of the current date in a reference to the MonthlySales array.
Multidimensional Arrays
In the examples above, I've shown you how to declare arrays with a single list of values but sometimes you need the data to go in more than one direction. For this, you need to declare an array with multiple dimensions. For example, if you wanted a zero-based array to hold the monthly sales for five different sales reps:
Dim MonthlySalesByRep(5,12)
Although the array is entirely in memory, it may be easiest to think of it in rows and columns. The above example would therefore be a grid with a row for each of the five sales reps (1 to 5) and a column for each month (1 to 12). If you wanted the above array to hold five years worth of data you could add a third dimension like this:
Dim MonthlySalesByRep(5,12,5)
In this case, the third dimension could be seen as a set of pages. So if you were referring to the sales for the third sales rep in the list during January of the second year represented, the reference could be:
Sales = MonthlySalesByRep(3,1,2)
The rules for changing the lower boundary on each dimension are the same as with single dimension arrays and the above examples assume you've added Option Base 1 to the top of the module. In VBA, you can declare an array with up to 60 dimensions although you'll probably stick to 2 or 3 most of the time to avoid confusion. If you do need to go beyond three dimensions, it may help to think of the array references in terms of coordinates rather than a physical representation of rows and columns.
Resizing Arrays
All of the examples so far have involved fixed arrays where the size is determined when the array is declared. With this type of array, the declaration requires that a hard number or a constant be used to specify the number of elements. You may have a situation where you want to use a variable to specify the number of elements in an array. An example would be if you read all of the values from a table and wanted an array that would match the number of records.
To do this, you would declare a dynamic array and use the ReDim keyword to resize it as needed.
Dim TableValues() as String
Dim RecordCount as Integer
RecordCount = DCount("[Business]", "BusinessTypes")
ReDim TableValues(RecordCount)
The code above declares TableValues() as a dynamic array simply by omitting the number of elements. It then uses the DCount VBA function to count the number of records in the BusinessTypes table and resizes the TableValues array to match that number.
You can use the ReDim statement more than once on an array but be aware that ReDim will erase all of the information stored in the array unless you use the Preserve keyword with it.
ReDim Preserve TableValues(RecordCount)
If you shrink an array using the Preserve keyword, you will lose the data in the elements that are removed. You can also resize an array to add or remove dimensions.
Enumerations
Enumerations are groups of constants that you can define within VBA in order to make it easier to reference a list of values. Enumerations can only be declared at module level (not within procedures) and use the Enum ... End Enum syntax shown below.
Enum Offices
Boston
Detroit
Toronto
Phoenix
Miami
[Los Angeles]
End Enum
Note that the value for Los Angeles above is enclosed in brackets. This is necessary if there are any spaces in the enumeration names or VBA will not recognize any of the items from that item onward. Ideally, it's best not to include spaces in these names and I'll mention more about that later.
The enumerations themselves are actually treated as custom data types within VBA so to reference the one above, you would declare a variable as shown here:
As you can see, when you assign a value to the variable you've declared as the enumeration type, Intellisense kicks in, recognizes the custom type and supplies a list of the possible values. This is very useful when you're dealing with a list of items that you need to reference frequently and want to make sure that the items are always referenced correctly.
In the background, the enumeration values are stored as integer values despite being represented with the strings that you supply. By default, the list starts with 0 and increments by 1 for every value you add but you could supply your own integer values so long as they were sequential.
Enum Offices
Boston = 100
Detroit = 200
Toronto = 300
Phoenix = 400
Miami = 500
[Los Angeles] = 600
End Enum
In the example above, you can see where the members of the Offices enumeration have been given custom values. These could represent location codes. In the screenshot, the code declares a variable of the Offices type, sets it to the [Los Angeles] enumeration member and then calls a message box to display the Integer value of that member which is 600.
Although I've used ordinary names for this example, it's considered good practice to use a naming convention for enumeration members that makes them recognizable as such. An example would be something like this:
Enum Offices
eBoston = 100
eDetroit = 200
eToronto = 300
ePhoenix = 400
eMiami = 500
eLosAngeles = 600
End Enum
Remember that when referencing enumeration members, VBA's focus is on the integer value. The string value is merely used to provide a recognizable reference for each member so the above names would make it easy enough to work with this enumeration in code.
Enumeration types can be used in procedure arguments just like other data types. In the screenshot below, you can see a function that is declared with the Offices type as an argument. Then a subroutine calls this function and Intellisense supplies the list of enumeration members to complete the function call.
As a final note, enumerations can contain both positive and negative values.
Enum ConnectStatus
eError = -1
eClosed = 0
eOpen = 1
End Enum
Enumerations are a very useful tool whether you need to reference a list of items as shown above or supply a short list of possible values for a custom form property as I do in the main Employee form from the demonstration database with the FormModes enumeration.
Public Enum FormModes
eAdd
eEdit
eRead
End Enum
Private menmMode As FormModes
Public Property Let CurrentMode(vdata As FormModes)
menmMode = vdata
subFormMode vdata
End Property
Public Property Get CurrentMode() As FormModes
CurrentMode = menmMode
End Property
In this example, the FormModes enumeration contains only three members and I don't even worry about their integer values. VBA can take care of that in the background. The enumeration is used as the type for the Form property that the rest of the code uses to determine if the form should allow the addition of records, the editing of data or keep the data read-only.
