9

I've been exploring VBA and excel and I've been writing a few basic codes around the Excel Solver. However robust the Excel Solver may appear, it's just not fast enough for my optimization problem (one iteration takes 20 minutes).

I've been looking around and speaking with more experienced programmers, and the consensus is that VBA is simply not fast and that C++, C#, and especially CUDA languages are much faster.

Looking at the VBA modules in Excel, the Solver module is locked by a password. I've been searching for that password and where it can be acquired for weeks to no avail.

Questions: Can this password be acquired? If so, how? Can it be bought from Microsoft? How much?

Objective: To get the Solver VBA to be able to potentially convert it to C# or C++ to make the optimization process faster.

P.S. I've looked at Microsoft Solver Foundation but it wasn't helpful.

Thank you.

Gian
  • 113
  • 1
  • 1
  • 6
  • C and C++ are potentially faster, but depending on the problem you might have to write quite much code to get it to solve your problem. Now the more interesting question is have you done all possible actions to ensure Excel is running as efficiently as it can? There are quite a many things you should turn on/off to ensure excel is not doing unnecessary iterations. – Han Soalone Feb 20 '17 at 13:11
  • I agree with you. Majority of the time (99% or more) is spent in running the Microsoft Solver algorithm and waiting for it to come up with the solution. Will turning those on/off increase the speed of Solver, or just the spreadsheet itself? – Gian Feb 20 '17 at 13:21
  • Depends, many times the solver might for example trigger work sheet calculation on every iteration. This is in many cases not a good thing as it might make millions of calculations after every small update potentially adding hours or minutes to the total run time. So I would have to say vaguely both, as the solver is relying on the work sheet data it might have to wait for the work sheet to finish, which makes it overall sluggish. – Han Soalone Feb 20 '17 at 13:25
  • Thanks, Han - that makes sense. The worksheet has around 6000 cells each with equations in them. I managed to isolate the redundant formulas to specific cells that it can just refer to instead of re-calculate, and it still has around 6000 unique cells. Charts and cells are not updating while solver is running. Thanks for this and I'll look for more ways along these lines. – Gian Feb 20 '17 at 13:38

3 Answers3

59

You can use this password to unlock the SOLVER.XLAM project;

Wildebeest!!

Old Nick
  • 995
  • 9
  • 19
6

The Solver solution module is not written in VBA: its already in C++. It is proprietary software developed by FrontLine Systems, who also market more capable solvers for a premium price. see

http://www.solver.com/
Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • I've been to solver.com and have communicated with them a couple of times. Is their Solver the same with Microsoft Excel Solver? – Gian Feb 20 '17 at 13:11
  • Sorry, I'm confused. Do you mean that the solver.xlam that came with Microsoft Excel is also from Frontline Solvers? – Gian Feb 20 '17 at 13:26
  • Yes: the Solver that comes with Excel is a baby version of Frontline systems Solver – Charles Williams Feb 20 '17 at 13:28
  • Thank you for the clarification, Charles. Much appreciated. – Gian Feb 20 '17 at 13:30
  • Just to be clear, `Solver.xlam` is written in VBA, but it is a wrapper around `Solver32.dll` that is indeed written in C++. – DecimalTurn Jul 27 '23 at 03:40
-1

Have you tried turning off Auto Calculate? If not it's under: Options > Formulas.

I have a file with 15 worksheets and if I updated 1 cell it would recalc all sheets :( I turned off auto calc and now use the ShiftKey+F9 to recalc the sheet I'm on. Much faster now.

Larry B.
  • 1
  • 1
    Yes. But calculation of cells is needed while Solver is running. It’s been almost two (2) years and looks like the limitation is in how complicated my system of equations is. – Gian Apr 25 '19 at 14:20