Variable Arrays

All the variables discussed so far have been single-instance variables. Often, however, you may find it very useful to work with arrays. An array is a group of variables of the same type, sharing the same name. In this way, processing groups of related areas is easy. For example, you might want to have a group of variables that tracks the sales in each of your company’s four regions. You can declare a currency variable for each region, plus one for the total sales across all regions, like this:

Dim cRegSales1 As Currency, cRegSales2 As Currency
Dim cRegSales3 As Currency, cRegSales4 As Currency
Dim cTotalSales As Currency

Then, if you want to calculate the total sales for all regions, you might use this code:

cTotalSales = cRegSales1 + cRegSales2 + cRegSales3 + cRegSales4

This approach isn’t all that cumbersome. However, what if you have 20 regions? Or several hundred? You can see how working with large numbers of related variables could get messy very quickly.

You can greatly simplify this example by using an array. Here, create an array of variables named cRegSales; the array can contain as many elements (instances of variables) as you have regions. You can rewrite the previous example for 20 regions like this:

Dim cRegSales(1 To 20) As Currency
Dim cTotalSales As Currency
Dim nCounter As Integer
Dim sTemp As String
    cTotalSales = 0
    For nCounter = 1 To 20
        cTotalSales = cTotalSales + cRegSales(nCounter)
    Next nCounter
    sTemp = "Total sales for all regions = "
    sTemp = sTemp & Format(cTotalSales, "currency")
MsgBox sTemp, vbInformation, "Sales Analysis"

Note this example’s use of a loop. The block of code beginning with the For instruction and ending with the Next instruction defines a group of program statements that will be repeated a certain number of times (in this case 20). Using loops makes short work of processing variable arrays. Loops are discussed in the later section "Working with Loops."

Top Home