Error message when using the Solver in a VBA macro loop

[Mathieu Fournier]

Hi everybody,

In Excel 2003 (Windows XP), I am trying to use the solver from a VBA macro
to solve a formula referring to its own result. The formula is included in a
spreadsheet table and is to be solved for every row of the table..

When running the macro, it is interrupted before completion and I get the
message: "Solver: an error has occurred, or available memory is saturated".

While trying to resolve the problem, I found out that the simple fact of
opening the input parameter msgbox of the Solver from the menu
Tool/Solver... and to exit it without having made any change solved the
problem!?! Afterwards, the macro could be used any number of time without
any problem.

It seems that the simple fact of opening the input parameter msgbox of the
Solver from the menu
Tool/Solver... activated some paramèters of the Solver
which are necessary to run my macro.

Here is the VBA code for my procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Dim i As Long 'declare variable i loop
Dim x As Long 'declare variable x row
' A loop that will solve the equation for the 26 rows of my table.
i = 1
For i = 1 To 26
x = i + 6 'set row number
' Set up the parameters for the model.
' Set the target cell Ex to a maximum value by changing cell
Fx.
SolverReset
' Add constraint: Ex = Fx.
SolverAdd CellRef:=Cells(x, 5).Address, Relation:=2, _
FormulaText:=Cells(x, 6).Address
SolverOK SetCell:=Cells(x, 5).Address, MaxMinVal:=1, _
ByChange:=Cells(x, 6).Address
' Solve the model but do not display the Solver Results
dialog box.
SolverSolve UserFinish:=True
' Delete constraint: Ex = Fx.
SolverDelete CellRef:=Cells(x, 5).Address, Relation:=2, _
FormulaText:=Cells(x, 6).Address
' Finish and keep the final results.
SolverFinish KeepFinal:=1
Next i
End If
End Sub


What would I need to change to get this macro to work right? (without having
to first open the input parameter msgbox of the Solver from the menu
Tool/Solver... and to exit it).

I tried using the SolverOptions command to modify a few of the advanced
options of the solver but it did not help my case.


Thanks for your help,

Mathieu Fournier

 

[Tushar Mehta]

Somewhere along the way (2000? 2002?), something happened between the
XL VBA and Solver interface. Now, it appears one must force Solver to
'initialize' itself. Stick in a SOLVER.Auto_open before the For loop.

Also, note that embedding a Solver optimization inside the
Worksheet_Change procedure is a prescription to a guaranteed disaster.
It will result in an infinite recursive loop -- or, if you are unlucky,
in wrong results. How? Think of this: how did the code in the
procedure get started? When you call Solver, what will it do? Change
something in the worksheet, right? What will XL do because of that?
And, you will be...back where?

The common solution is to set EnableEvents to False. But, I don't know
how Solver operates to confidently say that it would be safe to do so.
That leaves you with a self implemented switch. Something along the
lines of:

Option Explicit
Dim AlreadyBusy As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
If AlreadyBusy Then Exit Sub '<<<<<
AlreadyBusy = True
On Error GoTo ErrXIT
'do my stuff
AlreadyBusy = False
Exit Sub
ErrXIT:
'clean up the results of the error
AlreadyBusy = False
End Sub

But, honestly, you are best off not embedding solver optimizations
inside worksheet change (or calculate or selectionchange or any such)
events.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Mathieu Fournier]

Thanks for your tips, Tushar

It works just fine now.


Somewhere along the way (2000? 2002?), something happened between the
XL VBA and Solver interface. Now, it appears one must force Solver to
'initialize' itself. Stick in a SOLVER.Auto_open before the For loop.

Also, note that embedding a Solver optimization inside the
Worksheet_Change procedure is a prescription to a guaranteed disaster.
It will result in an infinite recursive loop -- or, if you are unlucky,
in wrong results. How? Think of this: how did the code in the
procedure get started? When you call Solver, what will it do? Change
something in the worksheet, right? What will XL do because of that?
And, you will be...back where?

The common solution is to set EnableEvents to False. But, I don't know
how Solver operates to confidently say that it would be safe to do so.
That leaves you with a self implemented switch. Something along the
lines of:

Option Explicit
Dim AlreadyBusy As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
If AlreadyBusy Then Exit Sub '<<<<<
AlreadyBusy = True
On Error GoTo ErrXIT
'do my stuff
AlreadyBusy = False
Exit Sub
ErrXIT:
'clean up the results of the error
AlreadyBusy = False
End Sub

But, honestly, you are best off not embedding solver optimizations
inside worksheet change (or calculate or selectionchange or any such)
events.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions