Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solver | Excel Discussion (Misc queries) | |||
Solver | Excel Discussion (Misc queries) | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
Solver | Excel Discussion (Misc queries) | |||
solver | Excel Discussion (Misc queries) |