Running Excel-2010's version of Solver through VBA

[Deniall]

Over the years I have developed a few Excel spreadsheets that are intended to be used by engineers of vastly differing Excel skill levels. Several of these spreadsheets use Excel's Solver, and to make them as bullet-proof as possible I have used VBA to automate the Solver. I am in the process of xgrading from Excel-2007 to Excel-2010 (where I am yet to decide whether x=up or x=down), and have some queries regarding the VBA implications of the new version of the Solver.

» I have already noticed that what I will call Solver-2010 constrains the solution variables to non-negative values by default, where its predecessors did not. Are there any other, similar, booby traps awaiting me?

» In order to avoid the need to explain to some of my users how to ensure that VBA has a "reference" to the Solver, I use Jon Peltier's Application.Run approach (see peltiertech.com/Excel/SolverVBA.html), corrected for the .XLA to .XLAM change imposed by Excel-2007. Does Peltier's method still work rigorously with Solver-2010?

» Being lazy (wrt support) I try to keep my spreadsheets backwards-compatible with earlier versions of Excel, so my VBA needs to know what version it is running under. I have noticed with my Excel-2010 that Application.Version returns the value 14, whereas with my Excel-2007 it returned 12. Was there a version between 2007 and 2010, or is it just that Microsoft is superstitious?

Thanks in advance.

 

[Deniall]

I have finally found the time to delve further into this. My "findings" (to be slightly grandiloquent) are below, in case any other members find themselves facing a similar problem.

The change to the default treatment of the AssumeNonNeg parameter to the call to SolverOptions seems (to my eyes at least) to be the only significant booby-trap (and one of my spreadsheets ran straight into it, luckily). There are other changes to the default values to parameters, but none likely to lead to wrong answers. However there are a lot more parameters able to be set, so anyone moving to the new Solver should read the documentation carefully.

SolverSolve has new return codes numbered 14 through 20, in addition to the previous ones (whose meanings are unchanged).

Jon Peltier's Application.Run approach does still work (and I still don't know how it works but remain eternally grateful that is does). However make sure that you consult the documentation to make sure you know the expected entry order for the parameters.

It appears that Microsoft IS superstitious: there is no version 13.