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.

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

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 <[email protected]>,
[email protected] 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.
>