Problem with SolverAdd. Can anyone help?

[Tim Anderson]

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

 

[Bill Manville]

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

 

[Guest]

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

 

[Michael R Middleton]

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