Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling the Solver via a subroutine
I'm trying to set up a program that will go down a list
in Excel and solve for some particular items in a given row using the Excel Solver. I can do this by "hand". However, the idea is to automate it to save time. I can't seem to be able to call the Solver using VBA code. I am using the following code to call the solver with some constraints: SolverOk SetCell:=Cells(i, Column_GSV),MaxMinVal:=2, ValueOf:="0", ByChange:=Cells(Row_#, Column_CV) SolverAdd CellRef:=Cells(i, Column_CV), Relation:=1, FormulaText:=Max_Airflow SolverAdd CellRef:=Cells(i, Column_CV), Relation:=3, FormulaText:=0 SolverSolve When running, I get the following message: "Sub or Function not defined" Any ideas of what will correctly call the Solver with VBA code, or can I call the Solver using VBA code? I greatly appreciate your help. Thanks James |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling the Solver via a subroutine
Hi James,
In order to use Solver in the fashion shown below you need to set a reference to Solver.xla from your project file. With both files open in the Visual Basic Editor (Solver is a demand-loaded add-in, so you may need to select Tools/Solver from the Excel menu to get it to open), select your project in the Project window. Next choose Tools/References from the VBE menu and put a check mark beside the list entry called SOLVER. After doing this you should be able to run your code. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "James" wrote in message ... I'm trying to set up a program that will go down a list in Excel and solve for some particular items in a given row using the Excel Solver. I can do this by "hand". However, the idea is to automate it to save time. I can't seem to be able to call the Solver using VBA code. I am using the following code to call the solver with some constraints: SolverOk SetCell:=Cells(i, Column_GSV),MaxMinVal:=2, ValueOf:="0", ByChange:=Cells(Row_#, Column_CV) SolverAdd CellRef:=Cells(i, Column_CV), Relation:=1, FormulaText:=Max_Airflow SolverAdd CellRef:=Cells(i, Column_CV), Relation:=3, FormulaText:=0 SolverSolve When running, I get the following message: "Sub or Function not defined" Any ideas of what will correctly call the Solver with VBA code, or can I call the Solver using VBA code? I greatly appreciate your help. Thanks James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Call a subroutine using variable subroutine name | Excel Discussion (Misc queries) | |||
Stopping a userform subroutine | Excel Discussion (Misc queries) | |||
SUBROUTINE HELP | Excel Discussion (Misc queries) | |||
How to use this subroutine on multiple cells? | Excel Worksheet Functions | |||
How do I exit a macro subroutine? | Excel Worksheet Functions |