Digipede: Distributing Excel Computations – Part 5

Well, we’re in the home stretch now. We have our results but how do we know when the Tasks and Job are complete? The Digipede Network will tell you. In Part 1 I didn’t show you how mJob was declared. It’s declared as a global like this:

Dim WithEvents mJob As Digipede_Framework.job
.
.
Dim mFileResultsLoc As String
Dim mResultsCount As Integer

Using WithEvents tells the VBA interpreter to expect the object to receive
events. Digipede will send events to the Master workbook for the mJob object.

Update to Excel Master with VBA code-behind: (Add this code)

' Handle the completion of a job
Private Sub mJob_JobFinished(ByVal sender As Variant, 
            ByVal e As Digipede_Framework.JobStatusEventArgs)

    Sheet1.Range("FinishedTime") = Format(Time, "hh:mm:ss")

    Dim errMsg As Variant
    If e.Error Is Nothing Then
        If e.JobStatus = Digipede_Framework.JobStatus_Completed Then
            ' Do Work Here – Create a report file 
            Dim oResultsBook As Workbook
            Dim strFilePath As String
            
            Dim iIndex As Long
            For iIndex = 1 To mResultsCount
                strFilePath = mFileResultsLoc & "\results" &
                  Application.WorksheetFunction.Text(iIndex, "00") & ".xls"
                Set oResultsBook = Application.Workbooks.Open(strFilePath)
                
                'Do analysis and create report

                'Clean up
                oResultsBook.Close
                Set oResultsBook = Nothing
            Next i1
            
            Sheet1.Range("Messages") = "Success"
        Else
            errMsg = "Job completed with status: " & e.JobStatus
            Sheet1.Range("Messages") = errMsg
        End If
    Else
            errMsg = "JobCompleted Error: " & e.Error.Message
            Sheet1.Range("Messages") = errMsg
    End If
End Sub

' Handle the successful completion of a task
Private Sub mJob_TaskFinished(ByVal sender As Variant, 
            ByVal e As Digipede_Framework.TaskStatusEventArgs)

    Dim errMsg As Variant
    If e.Error Is Nothing Then
    
        If e.TaskStatusSummary.FailureMessage = "" Then
            mResultsCount = mResultsCount + 1
        Else
            errMsg = "Task " & e.TaskId & " exited with error: " &
                 e.TaskStatusSummary.FailureMessage
            Sheet1.Range("Messages") = errMsg
        End If
    Else
        errMsg = "Task " & e.TaskId & " exited with error: " & e.Error.Message
        Sheet1.Range("Messages") = errMsg
    End If
End Sub

You, the programmer, can process each Results workbook in mJob_TaskCompleted()
as it is finished and/or you can process the workbooks at the end of the Job in mJob_JobFinished().

I have just Digipede-enabled a workbook that uses a data workbook to initialize calculations. The calculations produce results workbooks for each calculation instance. The Master workbook then processes the results workbooks into a report workbook.

So yes, you can distribute and run Excel on a grid. There is no silver bullet; you will have to do a little work. But it’s only a little work and the reduced execution time will likely more than make up for it. There are two things left to do to complete this exercise: 1) Write up all the debugging tricks I used and tell you about the strange Excel quirks I discovered. 2) Create a consolidation post with some pictures to show you what happened and links to all the parts.

Essay Links:

Start
Automation
Part 1
Part 2
Part 3
Part 4
Part 5

Technorati tags: Digipede, Grid Computing, Distributed, Excel, .VBA, .VBS, Microsoft