Running Excel-2010's version of Solver through VBA

[Denial]

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?

» I cannot find any new documentation of the VBA routines that invoke the various Solver operations (SolverOptions, SolverSolve, etc). Is there any such documentation specifically for Solver-2010?

» In particular, SolverSolve used to issue an integer return code between 0 and 13. Are these return codes unchanged with Solver-2010?

» 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.

 

[MiketheEngineer]

You might check over at Tek-tips.com - the sister to this site where the geek squad lives.

 

[Denial]

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.

There is some Solver-2010 documentation available through the F1 button in the VBA environment. I must have done some sort of mis-type to have missed it.

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.

 

[cowski]

there is no version 13.

Perhaps Office 2008 (released for Mac) got that designation.