Section 3.2. Building an Application
3.2. Building an ApplicationWe now understand how this application will work in theory. But, we need to take the theory and turn it into a solutionsomething we can give to the user. In this section we will build a complete Excel application based on the processes described in the last section. 3.2.1. DesignAs we develop an application, things can easily get out of control. Therefore, it is important to start with a plan and some structure. The example has several worksheets, each with its own function. The Data sheet holds information. It has no formulas. The user is not going to be looking at this sheet, so there is no formatting. This sheet stores the information that drives the application. When new information comes in, it goes on the Data sheet. Keeping the data separate has advantages. If the data source changes, only the interface between the data sheet and the data source needs changing. All of the logic in the application is on the Workarea sheet. This is the only sheet with workbook functions. The Display sheet is only concerned with presentation. It is attached to the Workarea sheet by named cells and ranges. There are no formulas, only formatting and information organization. The Settings sheet contains application options, and there are two specialized sheets: AccuracyChart contains a chart and HourlyForecasts contains a table of hourly predictions. These sheets have no formulas and only hold or display information. Prediction is the core of the application. But, to make a complete solution there needs to be a structure around it. What will the user see? How will the user interact with the application? These questions are answered by design. 3.2.2. List the RequirementsThis is important, and should be shared with users. It forces the developer and the user to think through how the application will be used. In this case there are seven basic requirements:
3.2.3. Consider the Source of DataThe data could come from a variety of sources. It could be in a SQL database, in an XML web service, or it could simply be keyed in. In the sample application the user will key the actual call volume for the previous day into the application. A VBA routine will control the process to ensure that the data is sensible and ends up in the right place. The application will, however, work just as well if the user keys it directly onto the Data sheet. Figure 3-6 shows the layout of the Data sheet. The user will only provide the Actual value. The rest of the data will come from the application itself. There are no formulas on this sheet and formats play no role. Note that the Anomaly Flag is set for July 3rd. A VBA routine to delete the last day entered will provide a simple editing scheme. To make the application use a different data source, only the VBA needs to be changed. We will examine the VBA later. Figure 3-6. Data sheet's layout 3.2.4. PresentationThe next step is to decide what the user will see. There are three things to consider. First, and at this point most important, are the information elements. Information is displayed as blocks of related data. The area that displays the forecasts for each day of the current week is an element. The hourly forecast graph is another. The contents and layout of the elements will guide the development of the logic on the Workarea sheet. The next consideration is the placement of the elements. Related elements should support each other. Principal elements should be prominently placed. The last thing to consider is the formatting. A simple color scheme that puts the emphasis on the content is best. Placement and formatting are easy to change. Figure 3-7 shows the main display, which looks and behaves like the home page of a web site, helping the user understand how the application works. The web page is an established metaphor and tells the user what to expect and how to interact with the application. Areas that display information retain an Excel look. This is another use of an established metaphor. Notice that only the information is displayed as black on white. Headings and labels use lower contrast. The emphasis is on the information. The area on the left is set apart by using a different background color . It contains the navigation buttons and displays summary information. The main section shows the detailed forecasts. The display is based on black, gray, and white. Colorful displays can cause problems. People are better at distinguishing the difference between light and dark than between colors. Some color combinations cause eye strain, and some users will be color vision deficient. If the display is printed, information represented by color can be lost. The information shown on this sheet is actually on the Workarea sheet. All the references are to named cells and ranges. This has several advantages. It makes changing the display sheet easy. Areas on the sheet can simply be cut and pasted anywhere. It also preserves the application's organization. You know there is no logic on this sheet, only formatting. Figure 3-7. Application's main display Two additional display areas complete the application: a chart showing the accuracy of the predictions during the last twenty days and a table showing hourly predictions for the whole week. They will not fit on this sheet. So, two additional sheets are used. As with the Display sheet, they reference information on the Workarea sheet. Figure 3-8 shows the accuracy chart. The hourly forecasts are shown in Figure 3-9. 3.2.5. Conventions and NamesThis application is complex. Using names for important values simplifies formulas and makes the application easier to understand. It also aids in troubleshooting or enhancing the logic. Another way to keep things from getting out of hand is following a set of conventions for the Workarea sheet. There are a few simple rules. Information from the Data sheet is displayed in blue font. Information to be displayed has a gray background. Information to be stored on the Data sheet has a blue background. Calculation areas have a border. Calculated values that are used in other calculations or macros are in Column A and are named. Figure 3-8. Accuracy chart Figure 3-9. Hourly forecasts 3.2.6. Named Values and Ranges on SettingsThe Settings sheet, shown in Figure 3-10, contains options that can be changed by the user. The application uses named cells and ranges to isolate the interfaces between sheets. These name cells and ranges are defined on the Settings sheet.
3.2.7. The Named Values on the Workarea SheetThe first part of the Workarea sheet is shown in Figure 3-11. The Workarea sheet contains all the logic. The named values from this sheet are described in Table 3-3. Figure 3-11. The Workarea sheet
3.2.8. Named Ranges on WorkareaIn this section we look at the named ranges on Workarea. Each area manages a set of data and calculations for one piece of the logic. They also arrange the data to fit the needs of the display sheet. This part of Workarea is shown in Figure 3-12. Figure 3-12. Thisweek and Nextweek
Figure 3-13 contains the HourlyNextWeek part of the Workarea sheet. Figure 3-13. HourlyNextWeek
Figure 3-14 shows the part of Workarea that deals with the hourly calculations.
The part of Workarea that handles the error calculations is shown in Figure 3-15. Figure 3-15. Areas and Links on Workarea 3.2.9. Other Important Links on Workarea
3.2.10. Linking to the DataWe now know what the Workarea sheet has to do. It is time to build the logic. The first step is to link the Workarea to the data on the Data sheet. To handle all the possible lags and days of the week, the INDIRECT function is used. The calculations use eight weeks of history and there can be up to seven workdays in a week. So, 56 days of history must be available. The Last_Row named cell on the Workarea sheet contains the row number of the last row used on the Data sheet. The application needs to start 55 rows above the last row. MyTop contains the starting row number. The data starts in cell B1. The formula is =INDIRECT("Data!A" & MyTop + ROW(A1)). It references column A on the Data sheet. Note that MyTop points to the cell one row above the first one needed. This allows the formula to be copied down. Row(A1) is added to MyTop. Row(A1) is one, but as the formula copies down A1 becomes A2, A3, etc. This gives the correct row for each value. The cells in columns C and D work the same way. The three columns are filled down to row 56. Column E masks anomalies. If the anomaly flag is set in column B, the actual value for that day is ignored, and the value from the previous week is substituted. The formula is =IF(B1=0,D1,INDIRECT("Data!C" & (MyTop + ROW(A1)-Lag))). This also fills down to row 56. This is the value used in the prediction calculation. Predictions from the last 20 days are used for several calculations and for a chart. So, we need to bring them onto Workarea. The weekly predictions are in the range F37:F63 and the adjusted predictions are in G37:G56. These cells use the same formula as in columns B, C, and D, but reference a different column on the Data sheet. The weekly predictions in column F fill down to 63 because we are predicting a week into the future. These formulas keep Workarea linked to the Data sheet. When new information is added to the Data sheet, Workarea automatically updates, and all of the values are ready to use. 3.2.11. Visual BasicThis application uses Visual Basic for Applications (VBA). VBA is a powerful tool and is easily over-used. In general, it is best to do as much as possible on the workbook and only use VBA for things that Excel cannot do. The code is in Module One (the default module for a spreadsheet) and can be viewed using the Visual Basic Editor. To reach the editor, select Tools VBA is not necessary for the application to work. The most complex VBA operation is adding a new day's data, and that can be done manually by going to the bottom of the Data sheet and typing the values in. In this application VBA is used for three things. First, for navigation, there are buttons on the sheets that move the user between sheets and ensure the view is set to the top left corner. This works like the sheet tabs, but gives flexibility in securing the application and helps make the application a complete package. Navigation code looks like this. Sub AccuracyChart( ) '******************* ' Navigation ' This macro takes the user to ' the AccuracyChart worksheet and selects ' cell A1. '******************* Sheets("AccuracyChart").Select Range("A1").Select End Sub This code only does two things. It selects the sheet and then it selects cell A1. Next, this macro adds a new day to the Data sheet. This can easily be modified to use an automated data source such as an SQL database. The variable NewActual could be populated by any method. This may look like a fairly involved routine, but actually it merely moves data around. All the calculations are done by Excel. Sub AddDay( ) '***************************************** ' This routine allows the user to ' enter the actual for the next day. ' The entry is checked and if it ' is valid the new data is moved to the ' Data sheet. '****************************************** Dim myItem As String Dim myDate As Date Dim NewActual As Variant Dim NextRow As Integer Dim Anomaly As Double Dim Prediction As Integer Dim AdjustedPrediction As Integer Dim TheLag As Integer myItem = Range("Item").Value ' Read the item from Settings myDate = Range("workarea!c57").Value ' Get the next date ' Use an input box to allow the user to enter the next actual NewActual = InputBox("Please enter the number of " & LCase(myItem) & _ " for " & myDate & ".", "Enter Actual", 0) ' If cancel or no entry quit If NewActual = "0" Or NewActual = "" Then Exit Sub If Not IsNumeric(NewActual) Then ' Is the value a number? MsgBox ("The Actual must be a number.") ' If not put up a message Exit Sub ' and quit End If If Val(NewActual) < 0 Then ' Is the value less than zero? MsgBox ("The Actual cannot be negative.") Exit Sub End If ' Is the value not an integer? If Val(NewActual) <> Int(Val(NewActual)) Then MsgBox ("The Actual must be an integer.") Exit Sub End If NextRow = Range("Last_row").Value + 1 ' Get the row number of the next ' row on the Data sheet Range("data!b" & NextRow).Value = myDate ' put the new date on the sheet Range("data!c" & NextRow).Value = NewActual ' put the new actual on the sheet TheLag = Range("Lag").Value ' get the lag from Settings ' Prediction will update when the new actual is put on the Data sheet Prediction = Range("Prediction").Value ' Anomaly will also update automatically Anomaly = Range("Anomaly").Value ' The anomaly goes on the Data sheet next ' since it is used as part of the calculation ' of the adjusted forecast Range("data!a" & NextRow).Value = Anomaly ' get the adjusted forecast AdjustedPrediction = Range("AdjustedPrediction").Value ' The weekly prediction goes one lag down from the current day Range("data!d" & NextRow + TheLag).Value = Prediction ' the adjusted prediction is put on the next row Range("data!e" & NextRow + 1).Value = AdjustedPrediction ' clear the area where the hourly actuals are entered Range("h7:h17").ClearContents Range("a1").Select ' select A1 End Sub The last macro allows the user to delete the data for the last day entered. This provides a simple editing capability. All this does is find the cells populated when the last day was entered then selects and clears them. Sub DeleteDay( ) '******************************************* ' This sub deletes the last day. All that is ' necessary is to remove the last entry from ' the Data sheet. '******************************************** Dim LastRow As Integer Dim TheLag As Integer LastRow = Range("Last_row").Value ' First we need to know where the ' last row is. The number of the ' last row used on the data sheet ' is in a named cell on the workarea ' sheet. This statement stores the ' row number in a variable called ' LastRow Sheets("data").Select ' All of the data to be deleted is on the ' the data sheet. So we start by selecting ' that sheet. ' The data is in columns A-E. We need only build a reference to ' the cells and clear the contents. ' For columns A,B, and C the data ' to be deleted is in LastRow. So, ' we can clear them at the same time. Range("data!a" & LastRow & ":c" & LastRow).ClearContents TheLag = Range("Lag") ' To determine the row of the last ' weekly prediction we need to know the ' lag. Lag is a named cell on the Settings ' sheet. ' This section deletes the last weekly ' prediction. It is one Lag below the ' LastRow. Range("data!d" & LastRow + TheLag).ClearContents ' The last adjusted prediction is one ' row below LastRow Range("data!e" & LastRow + 1).ClearContents Sheets("display").Select ' Return to the Display sheet Range("h7:h17").ClearContents ' clear the area where the ' hourly actuals are entered. Range("a1").Select End Sub With Workarea and the VBA complete, it is time to build the application's user interface. 3.2.12. FormattingFormatting in this application uses mainly backgrounds and borders. However, there is some conditional formatting on the Display sheet. In the ranges G23:J28 and M23:M28, the number of rows that will contain data varies with the number of workdays in the week. Therefore, a conditional format is set to include those cells in the display if they are used and to gray them out if they are not. The hourly sheet is just patterns and borders, as shown in Figure 3-16. Figure 3-16. Hourly Sheet This sheet may be over-formatted, but it shows how metaphor can imply function and guide user expectations. 3.2.13. Running the ApplicationThe application was written using Excel 2002. It uses no third party software. There are some formatting issues if it is run in Excel 95, but all the logic works even in that older version of the software. Start by opening the application to the Display sheet. In the Next Day area on the left, the value for Current Adjusted is 11,024. That is the adjusted prediction for the current day. The hourly forecasts for the first three hours are 390, 835, and 1,129. Suppose that the actual call counts for those hours are 500, 1,000, and 1,300. In Figure 3-17, in item 1 those volumes have been entered as the actuals for those hours. In item 2, the Hourly Chart is now showing that the day is higher than the forecast. If the first three hours are higher than expected, in this case about 19% higher, we would expect the rest of the day to be high as well. The Current Adjusted has increased to 13,106. In the Hourly Forecasts area the adjusted hourly forecasts have also been increased. The main display is described in Figure 3-17. Figure 3-17. Using the Display Sheet The current day is Friday 9/7/1998. That is the day in the Next Day area on the left. In the This Week area the full current week is shown. Item 3 points to the current day. The Forecast and Adjusted cells are filled in because they are predictions. Actual is blank since this is the day that is now working. In item 4, the Forecast for next Friday is not filled in. This is because we are predicting a week out and cannot predict next Friday until the Actual for this Friday is entered. Use the Hour Forecast button to view the hourly predictions for the current week. The Accuracy Graph button displays a chart showing the weekly predictions, the adjusted predictions, and the actual values for the last 20 days. The sample data ends on 8/6/98. The next week of call volumes is:
Click the NewDay button and enter 10,864, the value for the next day. The display will update automatically. This is illustrated in Figure 3-18. Figure 3-18. The Next Day Monday 8/10/1998 is now the working day, and all the viewing areas are reset to the beginning of the week. The Delete Day button will remove the last entered day from the application. So if you mistyped 10,864 or just want to go back to Friday, press Delete Day.
3.2.14. Customizing the ApplicationTo use the application with your data, the Data sheet should be cleared, and 56 days of dates and actual volumes should be pasted into columns B and C starting in row 2. Row 1 contains headings. Change the Settings sheet for the heading, item, lag, and sensitivity as needed. It will take one more full cycle of data for all the features to populate. It is easiest to enter this week using the AddDay macro, but the values can be typed directly into the Data sheet. The application is self-contained, and needs no other software except Excel. Later in the book we look at more advanced prediction techniques that could be incorporated into this project by changing the Workarea sheet. It is possible to enhance the application in many ways:
The key to successfully modifying the application is to follow the conventions. Keep the logic on Workarea. If you need to introduce a new data item put it on the Data sheet. New parameters go on Settings. Be sure to name everything you use. |
Macro 
EAN: 2147483647
Pages: 101