1 UTC+2h 2 13-09-28 17:21:34.44 13-09-28 Δ2h ┌13-09-28 16:09:52┐ Sep 28 Saturday 17:21 Δ2h 0 2 {$AE$2} {$AF$2} IF( ) ) ) {$AG$2} & IF( N2>1, IF( N2>1/24, IF( ))))) {$AH$2} =IF($L2="_","_", IF(COUNT(AO$1:AO2)=1, hh:mm:ss┐ "), hh:mm:ss┘ "), "")) & TEXT(AE2, IF(AF2,"mmm d dddd hh:mm", "hh:mm")) & REPT(" &AG2 ) {$AO$2} The columns between columns AH and AO are discussed in the previous section and are skipped in the above table. construction The capture shows the data source of the chart. The data source range of the chart will be changed by a VBA script before generating and mailing the image file. This step is carried out for the cases when the user changes the number of intervals. The mailing VBA script will be presented later in this document (see sections 14 and 15). The chart looks as follows: The vertical axis on the left side is associated to the areas of cost (in pink) and margin (in green). All other curves, minutes per hour, PDD, spoken per minute, and failed per minute, are associated to the blue vertical axis on the right. Gradients are used for the areas to underline the lack of precision of the fresh data with pale colors and more consistent statistics for the old data with more opaque colors. Similar gradients are used for the curves. ,
Sub Oval_SMTP_Click() Chart_Source Chart_Email End Sub Chart generation and the emailing can be triggered by clicking on a circle labeled “SMTP”. Otherwise the mailing is carried out by a periodic script which will be presented in following sections. Sub Chart_Source() Charts(1).SetSourceData Source:=range(range("chart_range").Value) End Sub The Excel is updated in case user has changed the number of intervals. The value of [chart_range] is computed by Excel formulas as a function of the number of intervals. Sub Chart_Email() Dim As String Dim As = range("chart_path").Value Set = If Len(Dir(chart_path, vbDirectory)) = 0 Then MkDir End If .Calculate Charts(1).Export & "\" & .Value SMTP (chart_path & "\" & .Value) End Sub The folder name for stocking the charts is computed in the Excel worksheet’s [chart_path] cell. This is a folder in the same location as the Excel file with the same name as the Excel file but suffixed by “_Chart” text. We verify if the folder exists. If it does not, we create it. We refresh the value of the filename of the chart (as it contains the current time) by recalculating cell [chart_name]. The PNG file is created by the Export
Else On Error GoTo Error_Handler_1 Application.OnTime range("schedule_next").Value, "Periodic", False On Error GoTo 0 End If If the status of the scheduler is changed to False, find out in [schedule_next] cell the next execution time of the scheduled “Periodic” subroutine and remove it. If errors occur while removing, go to the error handler. Worksheets(1).Protect Password:="" Exit Sub Protecting the worksheet and quitting the subroutine . Error_Handler_1: MsgBox "nothing to stop" On Error GoTo 0 Worksheets(1).Protect Password:="" We land here if the scheduled procedure “Periodic” is not found. Such a situation will occur if you exit the Excel file without stopping the scheduler and then try to stop the procedure after reopening the Excel file. A message will be displayed that there is nothing to stop, the worksheet will be protected, and the subroutine will quit. End Sub End of the schedule subroutine Sub Periodic() Worksheets(1).Unprotect Dim schedule_next As Dim schedlue_report As range Set schedule_next = range("schedule_next") Set schedule_report = range("schedule_report") Beginning of the subroutine periodic. The subroutine does the actual stuff and also, upon the completion, schedules itself for the next execution. We begin by creating two