10.3 Example: Automatic Data Reporting


10.3 Example: Automatic Data Reporting

The file Chart.xls demonstrates the application of Excel to the reporting of measurement data. Data reporting is necessary whenever relatively large data sets need to be documented and perhaps analyzed over an extended period of time. The data source can be just about anything, from the automatically measured amounts of hazardous chemicals in a waste treatment plant to the results of quality control in a factory.

The task of data reporting is to generate informative and readable printouts from the trash heap of numbers consisting of many small, or one large, file or database. It should be clear that charts for data visualization can play an important role in this operation.

Since in the sample files we cannot provide a technological method of data production, the application Chart.xls has available the menu command ReportCreate Test Data, which creates Excel files with simulated measurement data. In practice, you would need such a command only during the test phase of the program. In general, you would have more genuine measurement data at your disposal than you probably want, and you would not need to increase your data supply with a data simulation program.

Using the Example Program

When the file is opened, a custom menu appears. If you wish to try out the program quickly, execute the following commands in sequence: ReportCreate Test Data, Create Daily Report, and Create Monthly Report. Simply approve with "OK" the forms (such as that shown in Figure 10-4) that appear for data input.

The program then produces for each day of the current month a data file (requiring about 900 kilobytes of storage and half a minute to create on a reasonably modern computer). Then the daily report for the current day and the monthly report for the current month are presented in page view.

click to expand
Figure 10-4: The form for input of the data range

Test Data

The menu command ReportCreate Test Data leads to the creation, for each day, of files with the names D_yyyymmdd.xls (such as D_19991231.xls for 12/31/1999). In addition to the actual measurement data (96 values in each of the data series A1, A2, A3, B, C) these files contain six- hour average and maximum values as well as the daily average and maximum (see Figure 10-5). The files D_yyyymmdd.xls can be deleted after the program has been tested , of course.

click to expand
Figure 10-5: The construction of the daily files for the measurement data

In the reporting of the data it is assumed that the dataseries A1, A2, A3 are related . Therefore, these series are presented in a single chart (see Figure 10-6). In the monthly report this was no longer possible due to the complexity of the data, since in the charts for each data series the daily average as well as the daily maximum are presented in their own graph (see Figure 10-7).

click to expand
Figure 10-6: A daily report
click to expand
Figure 10-7: A page from the three-page monthly report

Program Code

Overview of the Components of Chart.xls

The Excel file Chart.xls consists of the following worksheets:

"Intro":

Worksheet with information about the use of the application.

"DailyReport":

Worksheet in which the daily report is constructed. The charts contained in it are deleted for each new report and constructed anew.

"MonthlyReport":

Worksheet in which the monthly report is constructed. The charts contained in it are final; they are not changed further in program code. In program code only the content of cells B9:M39 is changed.

"DataTemplate":

Worksheet that serves as template for the files with simulated data.

The construction of the worksheets must not be altered , since access to particular cells is carried out directly in program code.

The program code is divided into the following modules:

"ThisWorkbook":

display menu on opening; delete it on closing.

"FormDateInput":

form for input of date range.

"MenuEvents":

event procedures for the menu commands.

"CreateDateFiles":

procedures for generating the test data.

"CreateReports":

procedures for building and printing the daily and monthly reports .

On the following pages the most interesting details of the program code are described. The same order is observed as that for using the program (generate test data, daily report, monthly report). The code not only demonstrates the various possibilities for chart programming, it also shows how you can consolidate data from several Excel files when the Excel function DataConsolidate is too inflexible for your requirements.

Creating the Test Data

The program segment for creating the test data is of little interest to the extent that it would not exist in a real-world application (in which one has genuine data!). In our example GenerateDailyWorksheet creates a new Excel file based on the template worksheet in the sheet "DataTemplate." This template contains not only various formatting data, but also some formulas for calculating the six-hour average and maximum values as well as the daily average and maximum values.

The simulated test data are calculated on the basis of six superposed sine curves of various frequencies. The parameters of these functions (amplitude, frequency, and phase) are stored in the global field rndmat . The global variable rndInit determines whether this field already contains valid values. This avoids the necessity of providing new random numbers for each day. (Random numbers are generated only the first time this procedure is called.)

The random numbers are initialized in the procedure InitRandomnumbers (not presented here). Here the attempt is made to choose similar values for the three data series A1, A2, A3. For each day the procedure DailyRandomnumbers is called anew. This procedure changes the existing values of the zfmat field by a small amount, so that the data do not appear to be too regular.

 Chart.xls, Module CreateDataFiles Dim rndInit As Boolean        'tests whether random matrix is already initialized Dim rndmat#(5, 18)    'matrix with random numbers Const Pi = 3.1415927 ' create workbook with (random) measurement data for one day Function  GenerateDailyWorksheet  (dat As Date) As Boolean   Dim filename$            'name of the new workbook   Dim wb As Workbook       'new workbook   Dim ws As Worksheet      'sheet in this book   Dim cell As Range        'first data cell on the sheet   Dim i%, j%, k%           'loop variables   Dim x#, z As Date   filename = ThisWorkbook.Path + "\d_" + _     Format(dat, "yyyymmdd") + ".xls"   Application.DisplayAlerts = False   ' creates new workbook; copies sheet "DataTemplate" from   ' this workbook into new workbook; deletes all other sheets   Set wb = Workbooks.Add   ThisWorkbook.Sheets("DataTemplate").Copy Before:=wb.Sheets(1)   For i = wb.Sheets.Count To 2 Step -1     wb.Sheets(i).Delete   Next i   wb.Sheets(1).Name = "Sheet1"   ' insert random numbers into sheet   Set ws = wb.Worksheets(1)   Set cell = ws.[A4]   ws.[a1] = "Data for " & dat   If Not rndInit Then InitRandomnumbers   DailyRandomnumbers Application.Calculation = xlManual   For i = 1 To 96                                                 '00:00 through 23:45     z = dat + CDbl(#12:15:00 AM#) * (i - 1)     cell.Cells(i, 1) = z     cell.Cells(i, 1).NumberFormat = "hh:mm"     For j = 1 To 5                                                'five series of data       x = rndmat(j, 0)       For k = 1 To 18 Step 3         x = x + rndmat(j, k) * (1 + Sin(rndmat(j, k + 1) * z + _             rndmat(j, k + 2)))       Next k       cell.Cells(i, j + 1) = x     Next j   Next i   Application.Calculation = xlAutomatic   Application.DisplayAlerts = True   On Error Resume Next   ' delete existing file   If Dir(filename)<>"" Then Kill filename   wb.SaveAs filename   wb.Close False   If Err = 0 Then     GenerateDailyWorksheet = True   Else     MsgBox "An error has occured: " & Error     GenerateDailyWorksheet = False   End If End Function 
Note  

It happens again and again with automated measuring processes that due to some error, data are missing for a period of time (hours, or even days). In the procedure above, error simulation was not implemented. However, the reporting in DailyReport and MonthlyReport will continue to function if you simply delete some of the data from the generated files. But be careful in the calculation of average values. Missing measurements must not be taken to be zero values. The Excel worksheet function AVERAGE behaves admirably in this case and considers only those cells in the given range that are not empty. Only when all of the measurements of an averaging range are missing does it return the error result "division by 0."

Daily Report

The daily report contains three charts, in which the exact course of the measurements is presented. Here the curves A1, A2, A3 are united in a single chart. So that charts from several days can be compared easily, a uniform scaling is required. For this reason the Y range is set with a fixed range of 0 to 300. (Normally, Excel changes the scaling automatically and fits it to the values that actually occur.) Integrated into the daily report are a tabular overview of the daily average values and the daily maximum of the five curves.

The daily report for a given date is created by the procedure DailyProtocol . The charts are created completely in program code and inserted into the worksheet "DailyReport." Any existing charts in this worksheet (from the previous report) are first deleted.

The procedure opens the file with the daily data and copies some basic information (daily average and maximum) from it into the worksheet "DailyReport." Furthermore, the title of the report is extended to include the relevant date.

To generate a new chart, first three empty ChartObject frames are placed in the worksheet. Then ChartWizard is used to create charts within them corresponding for the most part to the actual requirements. (Some details that are not within control of ChartWizard have to be changed later on.) The three ChartWizard instructions differ only in that the charts are associated to differing ranges of cells from those of the daily data table.

Then begins the actual detail work of formatting the chart. The three charts can be worked on as a unit in a loop. The procedure ends with the daily data file being closed and the daily report being printed. (On account of the option Preview:=True printing takes the form of a page view.)

 'Chart.xls, Module CreateReports Sub  DailyProtocol  (dat As Date)   Dim filename$                       'report file name   Dim protWBook As Workbook           'workbook of this file   Dim protWSheet As Worksheet         'sheet of this book   Dim protRange As Range              'first data cell in this sheet   Dim chartWSheet As Worksheet        'reference to sheet with daily data   Dim i%, chobj As ChartObject        'loop variables   Application.ScreenUpdating = False   filename = ThisWorkbook.Path + "\d_" + _              Format(dat, "yyyymmdd") + ".xls"   If Dir(filename) = "" Then     MsgBox "The file " & filename & " does not exist. " & _       "Please create test data"     Exit Sub   End If   Set protWBook = Workbooks.Open(filename)   Set protWSheet = protWBook.Worksheets(1)   Set protRange = protWSheet.[A4]   Set chartWSheet = ThisWorkbook.Worksheets("DailyReport")   ' delete all existing charts on this sheet   For Each chobj In chartWSheet.ChartObjects     chobj.Delete   Next chobj   ' copy caption, daily averages and daily maximum values in table   chartWSheet.[ReportLabel] = "Daily report " & dat   protWSheet.[I19:M19].Copy   chartWSheet.[DailyAverage].PasteSpecial xlValues   protWSheet.[I21:M21].Copy   chartWSheet.[DailyMax].PasteSpecial xlValues   ' create three charts   For i = 1 To 3     chartWSheet.ChartObjects.Add(30, 150 + 200 * (i - 1), 400, 185). _       Name = "Daily data " & i     chartWSheet.ChartObjects("Daily data " & i).Activate     If i = 1 Then       ActiveChart.ChartWizard protWSheet.[A3:D99], _         xlLine, 4, xlColumns, 1, 1     ElseIf i = 2 Then       ActiveChart.ChartWizard protWSheet.[A3:A99, E3:E99], _         xlLine, 4, xlColumns, 1, 1     ElseIf i = 3 Then       ActiveChart.ChartWizard protWSheet.[A3:A99, F3:F99], _         xlLine, 4, xlColumns, 1, 1     End If   Next i   ' format charts   For Each chobj In chartWSheet.ChartObjects     chobj.Border.LineStyle = xlNone    'no border for entire chart     With chobj.Chart       .HasTitle = False                              'no title       .PlotArea.Border.LineStyle = xlAutomatic       'border       .PlotArea.Interior.ColorIndex = xlNone         'no pattern/fill       .Axes(xlCategory).TickLabelSpacing = 8       .Axes(xlCategory).TickMarkSpacing = 4          'x axis       .Axes(xlValue).MinimumScale = 0                'y axis       .Axes(xlValue).MaximumScale = 300       .Axes(xlCategory).TickLabels.Orientation = 45  '45 degrees       .Axes(xlCategory).TickLabels.NumberFormat = "h:mm AM/PM"       For i = 1 To .SeriesCollection.Count           'format data         .SeriesCollection(i).Border.ColorIndex = 1   ' series         .SeriesCollection(i).Border.Weight = xlThin         .SeriesCollection(i).Border.LineStyle = xlContinuous         .SeriesCollection(i).MarkerStyle = xlNone       Next i       If .SeriesCollection.Count > 2 Then           'distinguish         .SeriesCollection(2).Border.LineStyle = xlDot  ' 2nd and 3rd         .SeriesCollection(3).Border.LineStyle = xlDash ' series       End If       ' diagram size, legend size       .PlotArea.Left = 5: .PlotArea.Top = 5       .PlotArea.Width = 290       .PlotArea.Height = 140       .Legend.Left = 340       .Legend.Width = 50       .Legend.Border.LineStyle = xlNone     End With     Next chobj ActiveWindow.Visible = False 'deactivate chart     protWBook.Close     chartWSheet.PrintOut Preview:=True   End Sub 

Monthly Report

The monthly reports are somewhat more lavishly decked out than the daily reports, taking three pages in all. The first side consists of an overview of all daily average and maximum values as well as the resulting monthly averages and maxima. The next page contains three charts, and the last page sports two charts. These show the progression of the average and maximum values. The curves for the averages have been smoothed (click on the curve, open the pop-up menu Format Data SeriesPatterns, option Smoothed Line). Figure 10-7 shows the second page of the monthly report with the curves for the measurement values A1 through A3.

For generating the monthly report we have chosen a method completely different from that used for the daily report. The charts were inserted (with the mouse) into the worksheet "MonthlyReport" and are not touched at all by the procedure MonthlyProtocol . MonthlyProtocol merely changes those data cells that the finished chart accesses .

This way of proceeding has advantages and disadvantages. The advantage is that the programming effort is greatly reduced. Thus you can achieve good results with minimal experience in programming charts. The disadvantages become evident when you attempt to generate five identical charts by mouse click. This is almost as much effort as the programming (even if you first create a chart, and then copy it and change only the ranges of cells of the data series). Furthermore, this way of proceeding is possible only if the chart, as in this example, is to a great extent independent of the data. However, if such items as the number of data series, the number of data points, and the range of values of the data series can vary, then there is no avoiding "real" programming.

Remark  

The charts assume a month of 31 days. In the case of months with fewer days there are one to three empty data points. Thus the space available for the chart is not used to full capacity, but in exchange there is a distinct advantage: The scaling of the X-axis is independent of the number of days in the month. The charts are thereby more comparable.

Onward to the program code, which for the reasons cited above contains not a single line of instructions that typically apply to charts. The procedure is rather an example of how data from up to 31 files can be consolidated in a single table. The individual files are not opened, but rather direct access to individual cells of other worksheets is made via formulas of type = 'C:\Test\[D_20000101.XLS]Sheet1'!$L$19 . This form of data access proceeds surprisingly quickly. The creation of the monthly report takes only a little longer than that of the daily report.

The most complicated part of the procedure relates to the creation of these formulas, which are inserted into the worksheet by changing the FormulaR1C1 property of the affected cells. The formulas must be created relatively laboriously as character strings. The R1C1 format is better suited for such tasks , because at least there is no transformation from column numbers into letters .

 Sub  MonthlyProtocol  (dat As Date)   Dim sdat As Date, edat As Date 'start and end date   Dim nrdays As Integer         'number of days   Dim filename$                 'name of report file   Dim chartWSheet As Worksheet  'sheet of report file   Dim chartRange As Range       'first data cell   Dim z As Date, i%, j%         'loop variables   sdat = DateSerial(Year(dat), Month(dat), 1)   nrdays = DateSerial(Year(dat), Month(dat) + 1, 1) - _            DateSerial(Year(dat), Month(dat), 1)   edat = dat + nrdays - 1   ThisWorkbook.Activate   Set chartWSheet = ThisWorkbook.Worksheets("MonthlyReport")   chartWSheet.Activate   chartWSheet.[a1].Select   Set chartRange = chartWSheet.[B9]   ' build monthly table   Application.Calculation = xlManual   chartWSheet.[B1] = "Monthly report " & Format(dat, "mmmm yyyy")   For i = 1 To nrdays     z = dat + i - 1     chartRange.Cells(i, 1) = z     filename = ThisWorkbook.Path + "\d_" + _                Format(z, "yyyymmdd") & ".xls"     If Dir(filename) = "" Then       For j = 1 To 5         chartRange.Cells(i, 1 + j).FormulaR1C1 = ""         chartRange.Cells(i, 7 + j).FormulaR1C1 = ""       Next j     Else       filename = "='" & ThisWorkbook.Path + "\[d_" + _                  Format(z, "yyyymmdd") & ".xls]Sheet1'"       For j = 1 To 5         chartRange.Cells(i, 1 + j).FormulaR1C1 = _           filename & "!R19C" & 8 + j         chartRange.Cells(i, 7 + j).FormulaR1C1 = _           filename & "!R21C" & 8 + j       Next j     End If   Next i   If nrdays < 31 Then     For i = nrdays + 1 To 31       For j = 1 To 12         chartRange.Cells(i, j).ClearContents       Next j     Next i   End If   Application.Calculate   chartWSheet.Range("B9:M39").Copy   chartWSheet.Range("B9:M39").PasteSpecial Paste:=xlValues   Application.CutCopyMode = False   chartWSheet.PrintOut Preview:=True   Application.Calculation = xlAutomatic End Sub 

After all references have been inserted into the worksheet and the worksheet recalculated, the entire range of cells is copied to the clipboard. Then, with PasteSpecial only the numerical values (instead of the formulas) are pasted. This process saves memory and increases the speed of further processing. Furthermore, it does not occur to Excel to ask at the next opportunity whether it should update the existing references.

The procedure ends, like DailyProtocol , with printing the worksheet together with the five charts contained therein. Furthermore, in the page layout of the worksheet (FilePage Setup) "none" is selected for the header, and for the footer the page number is inserted (since the report always contains three pages).

Menu Management

The management of the menus has nothing new about it in comparison to what has been discussed in earlier chapters, for this reason we have not included the code here for the event procedures. The menu is realized as an independent CommandBar object. It is made visible in Workbook_Open when Chart.xls is opened, and is hidden again in Workbook_BeforeClose .

Dialog Management

The form FormDateInput is used universally for the three commands ReportCreate Test Data, Daily Report, and Monthly Report. The text in the text box lblInfo is changed according to the purpose for which it is to be used. With the procedures ProtocolMenu_GenerateNewFiles , _DailyProtocol , and _MonthlyProtocol , of which only one is reproduced here, the text in the text boxes txtFrom and txtTo is preset.

The two dates can be increased or decreased with spin buttons . The values are preset to 0, and the permissible range is from “1000 to 1000. Therefore, you can theoretically change the date by ±1000 days. (Theoretically, because you would not have the patience to keep pushing the button. Much quicker is simply to input the date via the keyboard.)

 ' Chart.xls, Module MenuEvents Sub  ChartSampleMenu_MonthlyProtocol  ()   Dim dat As Date, lastmonth As Integer   lastmonth = -1   With FormDateInput     .dat1 = DateSerial(Year(Now), Month(Now), 1)     .dat2 = DateSerial(Year(Now), Month(Now), _                DateSerial(Year(Now), Month(Now) + 1, 1) - _                DateSerial(Year(Now), Month(Now), 1))     .txtFrom = CStr(.dat1)     .txtTo = CStr(.dat2)     .spinTo = 0     .spinFrom = 0     .lblInfo = "Date range for which monthly reports will be " & _       "created and printed"     .Show     If .result = False Then Exit Sub     ' create report     Application.ScreenUpdating = False     Application.DisplayStatusBar = True     For dat = CDate(.txtFrom) To CDate(.txtTo)       If lastmonth <> Month(dat) Then         Application.StatusBar = "Create monthly report for " & _           Format(dat, "mmmm yyyy")         MonthlyProtocol CDate(dat)         lastmonth = Month(dat)       End If     Next dat     Application.StatusBar = False     Application.DisplayStatusBar = False   End With End Sub 

If the input is terminated with OK and if in btnOK_Click no input error is discovered , then a loop runs through all the days of the date range. Each time the month changes, MonthlyProtocol is called. Admittedly, the algorithm has not been overly carefully programmed, but it is surely the simplest solution that functions for arbitrary time intervals (even for more than twelve months). A calculation of the first day of each new month would probably require more time than simply running through all the days. In any case, it would have required more thought in the programming, and programmers are known not always to be in the mood for heavy-duty thinking.

The actual form event procedures turn out to be comparatively short and trivial. Note that the spin button is not synchronized when a new date is input via the keyboard. For this reason it is impossible to input a date via the keyboard and then change it with the spin button.

 ' event procedure for the form for date input Option Explicit Public result As Boolean, dat1 As Date, dat2 As Date Private Sub  btnCancel_Click  ()   result = False   Hide End Sub Private Sub  btnOK_Click  ()   If IsDate(txtFrom) And IsDate(txtTo) Then     result = True     Hide   Else     MsgBox "Invalid date!!"   End If End Sub Private Sub  spinFrom_Change  ()   txtFrom = CStr(dat1 + spinFrom) End Sub Private Sub  spinTo_Change  ()   txtTo = CStr(dat2 + spinTo) End Sub 



Definitive Guide to Excel VBA
Linux Application Development For The Enterprise (Charles River Media Programming)
ISBN: 1584502533
EAN: 2147483647
Year: 2002
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net