Digipede: Distributing Excel Computations – Part 3

In Parts 1 and 2 I showed you how to move the Worker workbook, the RunWorker script, and the Data workbook to the compute nodes. In Part 3 I’m going to show you how to start the computation in the Worker workbook from the RunWorker script.

I’m going to let the Worker workbook open the Data workbook itself. The RunWorker script could open the Data workbook and populate Worker but this is an Excel sample, so I’m going to let Excel do all the heavy lifting.

RunWorker must make an Excel VBA call to cause the Worker to begin work. You have two choices: Calculate () or Run (). I recommend staying away from Application.Calculate () because if the Worker is running on a work station that already has other Excel workbooks open, the Application.Calculate () will initiate calculations on those other workbooks. We don’t want that to happen. It breaks one of the first rules of software development which is: Play well with others. If we’re lucky there will be a Workbook.Calculate () in Excel 12, but it’s not available now and our safest bet is to stick to either Application.Run () or Worksheet.Calculate().

Generally if your Worker workbook calculations were being initiated by a subroutine then you would call Application.Run () from the RunWorker script. If you were initiating calculations by pressing F9 on Excel then you will need to identify the worksheet hierarchy and call Calculate() on the top most worksheet, the calculation will then ripple through the workbook.

I’m going to use Run () in this example because all of the parallelizable Excel workbooks I’ve seen so far have had code-behind.

The reason that Part 3 has take so long to get out has been do to the trouble I’ve had figuring out the format the macro name needs to take for the Run () to execute. I thought I had understood it but I find that I understand it much better now! The macro name sent to Run () is made up of three parts: the filename, the Excel Object name, and finally the macro name.

To get the names of the macros available in an Excel workbook:

1. Open the workbook in Excel

2. From the Menu Bar select ‘Tools’ then select ‘Macro’ and finally select ‘Visual Basic Editor’ – these steps open Microsoft Visual Basic with the code-behind for this workbook.

3. Within Visual Basic from the Menu Bar select “Tools” then ‘Macro’. You now see the list of macros available within the workbook for external reference. If the macro you want to call is not listed there, you must redeclare the macro as Public. The format of the macro in the list box is the second and third parts of the macro name that Run () needs. The first part of the macro name is the fully qualified path and name of the workbook (without the .xls extension). It is a safe practice to put single quotes around the fully qualified filename incase there are spaces somewhere in the name.

Update RunWorker Launch Script:

.
.
.
WBook.Sheets(“Automate”).Range(“O3”) = TaskId

Dim strMacroName
strMacroName = "'" & strPath & "\Worker'" & "!Sheet1.btnStartCalc_Click"
myExcelWorker.Run strMacroName
.
.
.

Once the calculation is complete you have the option of storing the results in a
database or a results file. I’m going to store my results in a result workbook and send that back to the Master since that demostates Digipede specific functionality. I’ll show you how to do that in Part 4.

Essay Links:

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

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