Using Solver in VBA
 

I wrote a macro so I can use Solver by hitting a button and having the macro
pick up the appropriate information from the worksheet rather than going
through the Tools|Solver menu item and entering the data manually. When I
load the worksheet and hit the "solver" button, I get the following
Microsoft Excel error message: "Solver: An unexpected internal error
occurred, or available memory was exhausted." However, if I run Solver using
Tools|Solver, update the worksheet, and THEN push my macro button (after
having run Solver once from the menu) it works fine! Is there a way to get
the macro to work the FIRST time without going through the menu once?

Windows XP Professional SP2, Excel 2003
In VBA editor, Tools | References, SOLVER is checked

Range names defined:
LastCalc: "=OFFSET(Sheet1!$G$3,COUNTA(Sheet1!$G:$G)-1,0,1,1)" <-- This is
the "Target" Cell
LastActual: "=OFFSET(Sheet1!$B$3,COUNTA(Sheet1!$B:$B)-2,0,1,1)" <-- This is
the "Equal to Value" Cell
InterestRate: "=Sheet1!$G$3" <-- This is the "By Changing" Cell

VBA macro:

Private Sub CalcInterestButton_Click()
SolverReset
SolverOptions Precision:=0.00001
SolverOK SetCell:=Range("LastCalc"), _
MaxMinVal:=3, _
ValueOf:=Range("LastActual").Value, _
ByChange:=Range("InterestRate")
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End Sub

Using Solver in VBA
 

You need to reference Solver in the VBE
The quote below is taken from
http://www.vertex42.com/ExcelArticle...-examples.html

Before the macro will work, a reference to the Solver VBA add-in functions
must be added.

Adding the Solver Reference:
a.. Step 1: Edit the macro you just created (Tools Macro Macros... or
Alt+F8). This will open up Visual Basic. You can also press Alt+F11 to open
up VBA.
b.. Step 2: Add the Solver reference in visual basic (Tools
References..., then make sure that SOLVER is checked).
The VBA code for the Solver macro that was recorded for Example 2 is shown
below.

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"redbird" wrote in message
...
I wrote a macro so I can use Solver by hitting a button and having the
macro pick up the appropriate information from the worksheet rather than
going through the Tools|Solver menu item and entering the data manually.
When I load the worksheet and hit the "solver" button, I get the following
Microsoft Excel error message: "Solver: An unexpected internal error
occurred, or available memory was exhausted." However, if I run Solver
using Tools|Solver, update the worksheet, and THEN push my macro button
(after having run Solver once from the menu) it works fine! Is there a way
to get the macro to work the FIRST time without going through the menu
once?

Windows XP Professional SP2, Excel 2003
In VBA editor, Tools | References, SOLVER is checked

Range names defined:
LastCalc: "=OFFSET(Sheet1!$G$3,COUNTA(Sheet1!$G:$G)-1,0,1,1)" <-- This is
the "Target" Cell
LastActual: "=OFFSET(Sheet1!$B$3,COUNTA(Sheet1!$B:$B)-2,0,1,1)" <-- This
is the "Equal to Value" Cell
InterestRate: "=Sheet1!$G$3" <-- This is the "By Changing" Cell

VBA macro:

Private Sub CalcInterestButton_Click()
SolverReset
SolverOptions Precision:=0.00001
SolverOK SetCell:=Range("LastCalc"), _
MaxMinVal:=3, _
ValueOf:=Range("LastActual").Value, _
ByChange:=Range("InterestRate")
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End Sub