Solver GUI works fine, but the VBA macro won't return any results
 

I'm using the Solver add-in (Excel 2000) to do a power regression on a data
set. If I use the GUI (menus, point-n-click, etc.) it works great.

I want to use this as part of a larger macro, so I recorded my actions to
VBA code. If I try running the macro, nothing happens. I don't get any
warnings or errors; Excel runs the macro just fine, but there are no results.
I've tried changing the SolverSolve argument "userFinish" between True and
False, and changing the SolverFinish argument "KeepFinal" between 1 and 2,
but nothing is helping. The applicable code is pasted below.

Minimum I've tried:
SolverOk SetCell:="$D$1", MaxMinVal:=3, ValueOf:="0", ByChange:="$A$1:$B$1"
SolverSolve

My most verbose effort:
SolverReset
SolverOk SetCell:="$D$1", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$B$1"
SolverOptions MaxTime:=10, Iterations:=200, Precision:=0.00000001, _
AssumeLinear:=False, StepThru:=False, Estimates:=1, _
Derivatives:=1, SearchOption:=1, IntTolerance:=5, Scaling:=False, _
Convergence:=0.00000001, AssumeNonNeg:=False
SolverOk SetCell:="$D$1", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$B$1"
SolverSolve userFinish:=False
SolverFinish KeepFinal:=1

Thanks in advance.

Solver GUI works fine, but the VBA macro won't return any results
 

Hello Ben,

You need to add the Solver reference library to VBA in order to expose
Solver's VBA interface.

ADDING THE SOLVER REFENCE LIBRARY:

1) *Add-In Solver * to your Workbook
2) Open the Visual Basic Editor from Excel by Pressing *ALT + F11*
3) Select the Tools Menu by pressing *ALT + T*
4) Display the References List by pressing *R*
5) Scroll through the list and find *SOLVER.xls*
6) Click on the checkbox to Add the Library
7) Press *Enter*
8) Press *ALT + S* to save the reference with your VBA project

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=381343

Solver GUI works fine, but the VBA macro won't return any resu
 

"Leith Ross" wrote:

Hello Ben,

You need to add the Solver reference library to VBA in order to expose
Solver's VBA interface.

I did add the ref library, but never explicitly hit Alt-S to save (did you
mean Ctrl-S or is the VBA interface different?).

Since making my original post, the problem has become intermittent. I
haven't yet figured out what makes it sometimes work and sometimes not. It
seems to be affected by whether/how I run the GUI Solver before trying the
VBA-launched Solver. Do you see anything suspicious in my code?

Thanks for the reference tip, and I'll post again if I can isolate the cause.

Solver GUI works fine, but the VBA macro won't return any results
 

In addition to needing the reference to the Solver add-in, something
else that helps (but unfortunately not 100% of the time) is to add,
before any other Solver code, the statement SOLVER.Auto_open

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I'm using the Solver add-in (Excel 2000) to do a power regression on a data
set. If I use the GUI (menus, point-n-click, etc.) it works great.

I want to use this as part of a larger macro, so I recorded my actions to
VBA code. If I try running the macro, nothing happens. I don't get any
warnings or errors; Excel runs the macro just fine, but there are no results.
I've tried changing the SolverSolve argument "userFinish" between True and
False, and changing the SolverFinish argument "KeepFinal" between 1 and 2,
but nothing is helping. The applicable code is pasted below.

Minimum I've tried:
SolverOk SetCell:="$D$1", MaxMinVal:=3, ValueOf:="0", ByChange:="$A$1:$B$1"
SolverSolve

My most verbose effort:
SolverReset
SolverOk SetCell:="$D$1", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$B$1"
SolverOptions MaxTime:=10, Iterations:=200, Precision:=0.00000001, _
AssumeLinear:=False, StepThru:=False, Estimates:=1, _
Derivatives:=1, SearchOption:=1, IntTolerance:=5, Scaling:=False, _
Convergence:=0.00000001, AssumeNonNeg:=False
SolverOk SetCell:="$D$1", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$B$1"
SolverSolve userFinish:=False
SolverFinish KeepFinal:=1

Thanks in advance.