Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA errors in 2007 (works fine in 2003) | Excel Discussion (Misc queries) | |||
Macro fine Run fine from Select but not from KB Shortcut? | Excel Discussion (Misc queries) | |||
Macro hangs up often but sometimes works fine | Excel Worksheet Functions | |||
VBA problem, works fine as formula but not in VBA | Excel Programming | |||
Macro works fine in xl2002 but does not in xl 2000 | Excel Programming |