A Day in the Life

A day in my life. Thoughts on leadership, management, startups, technology, software, concurrent development, etc... Basically the stuff I think about from 10am to 6pm.

3/01/2006

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

Labels:

0 Comments:

Post a Comment

Links to this post:

Create a Link

<< Home