Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with SolverAdd. Can anyone help?
I accidentally hit the send button before I was finished...
As I was saying... I'm trying to add a variable number of constraints to Solver using VBA 6.0. The following snippet of code works fine on a machine that has VBA 6.3 installed, but NOT on my machine that has VBA 6.0 installed: 'Insert new constraints For i = 1 to NumRows SolverAdd CellRef:=Range("MatrixLLT").Cells(i,i), Relation:=2, FormulaText:="1" Next i Can anyone propose a way to make this routine work in VBA 6.0? Thanks! Tim Anderson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with SolverAdd. Can anyone help?
Tim Anderson wrote:
The following snippet of code works fine on a machine that has VBA 6.3 installed, but NOT on my machine that has VBA 6.0 installed: In what way does it not work for you? Compile error? Run-time error? Wrong results? If an error message, what is the message and which line is highlighted? Do you have the solver add-in installed and referenced from your VBProject? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - reply in newsgroup |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with SolverAdd. Can anyone help?
Bill,
Thanks for responding. The rest of the code works fine. There are no compile errors, no error messages. The only problem is that the constraints are not added to Solver when the macro is run. Everything else works perfectly. Here is the entire subroutine: Sub SolveIt() ' 'SolveIt Macro 'Macro recorded 12/12/03 by Tim Anderson ' Application.ScreenUpdating = False Count = Range("MatrixL").Count NumRows = Sqr(Count) 'Reset the Solver SolverReset SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _ :=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _ IntTolerance:=5, Scaling:=True, Convergence:=0.0001, AssumeNonNeg:=False 'Insert new constraints For i = 1 To NumRows SolverAdd CellRef:=Range("MatrixLLT").Cells(i, i), Relation:=2, FormulaText:="1" Next i 'Set objective function and decision variables, then solve SolverOk SetCell:="Objective", MaxMinVal:=2, ValueOf:="0", ByChange:="MatrixL" SolverSolve End Sub When I "step through" the code, the "highlight" steps through the loops, but for some strange reason, the constraints do not get added to Solver. Everything else in the subroutine works perfectly. Strangely enough, this exact same code DOES work on other computers. I just can't figure out why it won't work on THIS one. If you have any ideas as to why, I'd sure appreciate hearing them. Tim Anderson -----Original Message----- Tim Anderson wrote: The following snippet of code works fine on a machine that has VBA 6.3 installed, but NOT on my machine that has VBA 6.0 installed: In what way does it not work for you? Compile error? Run-time error? Wrong results? If an error message, what is the message and which line is highlighted? Do you have the solver add-in installed and referenced from your VBProject? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - reply in newsgroup . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with SolverAdd. Can anyone help?
Tim Anderson -
Chris Albright mentions a similar problem (constraints being ignored) in his book VBA for Modelers (Duxbury). In his example the workaround was to repeat the SolverReset and SolverOK lines of code before calling SolverAdd. - Mike Middleton, www.usfca.edu/~middleton wrote in message ... Bill, Thanks for responding. The rest of the code works fine. There are no compile errors, no error messages. The only problem is that the constraints are not added to Solver when the macro is run. Everything else works perfectly. Here is the entire subroutine: Sub SolveIt() ' 'SolveIt Macro 'Macro recorded 12/12/03 by Tim Anderson ' Application.ScreenUpdating = False Count = Range("MatrixL").Count NumRows = Sqr(Count) 'Reset the Solver SolverReset SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _ :=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _ IntTolerance:=5, Scaling:=True, Convergence:=0.0001, AssumeNonNeg:=False 'Insert new constraints For i = 1 To NumRows SolverAdd CellRef:=Range("MatrixLLT").Cells(i, i), Relation:=2, FormulaText:="1" Next i 'Set objective function and decision variables, then solve SolverOk SetCell:="Objective", MaxMinVal:=2, ValueOf:="0", ByChange:="MatrixL" SolverSolve End Sub When I "step through" the code, the "highlight" steps through the loops, but for some strange reason, the constraints do not get added to Solver. Everything else in the subroutine works perfectly. Strangely enough, this exact same code DOES work on other computers. I just can't figure out why it won't work on THIS one. If you have any ideas as to why, I'd sure appreciate hearing them. Tim Anderson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem implementing SolverAdd - Can anyone solve? | Excel Programming |