Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Running Excel-2010's version of Solver through VBA

Running Excel-2010's version of Solver through VBA

Running Excel-2010's version of Solver through VBA


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.

RE: Running Excel-2010's version of Solver through VBA

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

RE: Running Excel-2010's version of Solver through VBA

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.

RE: Running Excel-2010's version of Solver through VBA


there is no version 13.
Perhaps Office 2008 (released for Mac) got that designation.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close