Dynamic arrays

Our declarations so far have been of the form:

Dim expenses (5) As Currency

This statement shows that the array called expenses contains 5 fixed elements, and that the lower and upper bounds of the array index are 0 to 4: meaning that the starting index is 0 and the highest index is 4. However, there are some situations that arise in which the size of an array would be unknown to the programmer in advance of writing the program. For example, if an array was going to itself contain a variable number of data items. A dynamic array can be declared to deal with this situation. It is declared just like any other array, except that the parentheses are empty. Hence, a dynamic currency array called expenses, would be declared as:

Dim expenses () As Currency

A dynamic array can be resized using the ReDim statement, as in:

ReDim expenses (10)

Arrays are primarily used to deal with lists. The following example is typical of the use of arrays. The SALESMAN workbook uses a macro to add the five working weekdays to get a total for the weekly mileage. The figure is then entered into the ActiveCell (this must be in the range WeekSales in order that it can be used). The program outputs, using a MsgBox, the daily mileages that were entered and a chance to change any of the values in case there was an error during entry. The user will be prompted with a MsgBox that will contain Yes and No options. Should the user select No, the program will ask them to re-enter the data again until they respond with Yes in the MsgBox. After this the program will then respond with another MsgBox stating the total for the daily sales. The program will also enter this value in the ActiveCell of the blank worksheet Sheet2. Although this could be done without the use of arrays, they make the job easier. Listing 5.3 shows how this program would work.

This example uses an array to store the daily sales for an employee. This is done by reading the data into an array using the InputBox function. These items are then added to get a week sales total. The result is placed in the ActiveCell in the Sheet2 (a temporary location). In the declaration section, the array called thisDaySales(5) has been declared as an Integer array. After

Listing 5.3 Using an array to total daily sales

Sub totalDailySales()

'Adds together five items of data to get a week sales total for the employee. 'For each working day

Dim answerStr As String Dim totalSales As Integer Dim answerNo As Integer Dim thisDaySales (5) As Integer Dim dayCount As Integer answerStr = "" TotalSales = 0

Worksheets ("Sheet2").Select Do

For dayCount = 1 To 5 thisDaySales (dayCount) = InputBox(prompt:= "enter sales for day: "

& dayCount) totalSales = totalSales + thisDaySales(dayCount) Next

For dayCount = 1 To 5

AnswerStr = Str (thisDaySales (dayCount)) + answerStr Next

AnswerStr = "Do you want to change these values?" +answerStr answerNo = MsgBox (answerStr, 4, "Check daily Mileages") Loop Until answerNo = 7

ActiveCell.Value = totalSales 'set value of the active cell to total sales ActiveCell.Offset (0, -1).ColumnWidth = 14

'extend the column width in the left cell, so complete label will be visible ActiveCell.Offset (0, -1).Value = "Total Sales =" 'insert "Total Sales" in this cell End Sub declaring all the variables, the line Worksheets ("Sheet3").Select will select the Sheet2 for storing the final result.

The purpose of the Do.. .Until loop is to allow the user the chance to check the correctness of the data entered posting the total to the ActiveCell on the weeklysales worksheet. (Loops will be studied in more detail in Chapter 7.) Notice how two For.. .Next loops are used in sequence in this program. The first is used to read the daily sales data into the array called thisDaySales. The second is used to create a string variable called answerStr. The purpose of the answerStr variable is to contain the sequence of data input for the five daily sales; data stored as a string is easier to output with a MsgBox statement. The variable answerNo that is used for the MsgBox response will contain the values Yes or No. The Loop Until answerNo = 7 line will test to see if the value No has been chosen. When it has, the loop will terminate and the contents of the totalSales variable will be written to the active cell of the week_sales range of the worksheet. The program will then terminate.

There are three screenshots showing action points during the program run. Figure 5.6 shows a InputBox request for day 2. Figure 5.7 shows the MsgBox that displays the data for the five days. Notice that this box lets the user correct any mistakes by retyping the data set. Figure 5.8 displays the Sheet2 display after the user has accepted the data in the MsgBox.

1 Microsoft Excel


enter sales for day: 2

OK Cancel


Figure 5.6 Entering daily data for the VBA program Listing 5.5 EBXB^BL

Do you want to change these values? 18 4 7 3. |; ges ij Mo

Figure 5.6 Entering daily data for the VBA program Listing 5.5 EBXB^BL

Do you want to change these values? 18 4 7 3. |; ges ij Mo

Figure 5.7 MsgBox output showing the daily values from program Listing 5.5

I E Microsoft Excel - SALESMAN

. P X

8Q File Edit

□ y É

View Insert Format Tools Data Window Help - ¿P J (?) » Arial - 10 B / U WE W. m

X »



















Total Sales=




H 1 ► M\sh™t2/sheetl / wei

äWysales / week sa h > 1


Figure 5.8 Value of TotalSales and label in left hand column in Sheet2

0 0

Post a comment