0

I have a 2010 Excel 64-bit model that has a single VBA subroutine to run through 16 combinations of inputs, which all get processed using the same Excel model calculations and which then outputs the results to tabs in the model. I have access to a high performance cluster (HPC) and wish to run the VBA code such that I can run the 16 combinations in parallel, instead of the current sequential process on the HPC. How should I approach this? For examples, do I need to put each combination into a separate subroutine and have a main VBA subroutine to call each of the combinations? Is front end and back end VBA code that I need to include in order to run the model on the HPC?

BruceCH
  • 1
  • This link may be of some use [Building VBA applications and workbooks for a Windows HPC Cluster](http://www.microsoft.com/en-us/download/details.aspx?id=2939) – chris neilsen Aug 01 '12 at 03:23

1 Answers1

0

Excel VBA does not directly allow multithreading, so unfortunately there is no simple VBA solution for this.

I can see a couple options here, and it will depend on your problem whether you will be able to use them.

  1. In Excel 2007 and 2010, worksheet functions can execute in parallel. If your VBA code is a function and not a sub, and if most of your data comes from the worksheet, you could try to take advantage of that.

  2. You could write a DLL that handles multithreading yourself, and call it from Excel. For this, you'd have to port your code to VB 6 or VB.NET (or straight up rewrite it in C/C++), and manually deal with multithreading.

Community
  • 1
  • 1
ikh
  • 2,336
  • 2
  • 19
  • 28
  • Thanks that's very helpful. I have two of these problems. With one of them, the time consuming process within each iteration called from VBA is actually running SOLVER. It is the SOLVER component that is the most time consuming, e.g. 24 hours for all combinations. Will SOLVER run the problem in parellel on the HPC? If it will, I could still run the combinations in sequence but have each iteration shortened in duration. – BruceCH Aug 01 '12 at 03:02
  • Have you looked at the commercial version of Solver: it can be orders of magnitude faster ? http://www.solver.com/exceluse.htm – Charles Williams Aug 01 '12 at 08:07
  • Yes, I've tried using the commercial version of Solver and there was little difference. – BruceCH Aug 01 '12 at 18:05