CODEWITHSUNDEEP
excelsolvervba
bwindi
bwindi

Reputation: 11

vba solver - adding a delay to the iterations

I am trying to control the excel solver in vba. I use it to minimise the value of a target cell (i,10) by adjusting an input value (i,8). I also have a user defined function that calls an external application for some computations, using cell(i,8) as input. The target cell is the difference of the UDF output and another fixed value.

I am finding that the solver periodically sets up the problem and then skips it. I can manually adjust the values to get a better solution, so I think that the solver skips to a new iteration faster than the UDF returns a value.

Is there a way to slow down the solver iterations? Code below if it helps...

Sub Main()

Dim Msg As String, MyString As String
Dim Style As Variant, Response As Variant

Application.ScreenUpdating = False
Application.EnableEvents = False

'Define a confirmation message due to long duration of calculations
Msg = "This calculation takes long. Do you want to proceed?"

'Define message box style
Style = vbOKCancel + vbCritical + vbDefaultButton2

'Record the user response
Response = MsgBox(Msg, Style)

If Response = vbOK Then
        Call RateTool.RateSolver
    End If

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

And the Solver script

Sub RateSolver()

Dim First As Integer, Last As Integer
Dim i As Integer

First = Cells(2, 4).Value
Last = Cells(3, 4).Value

For i = First To Last

    SolverReset

    'Define parameters for the solver: Minimise target cell (i,8) by changing input cell (i,6)
    SolverOk SetCell:=Cells(i, 10), MaxMinVal:=2, ByChange:=Cells(i, 8), Engine:=1

    SolverSolve UserFinish:=True

Next i

End Sub

Upvotes: 0

Views: 288

Answers (1)

Kelaref
Kelaref

Reputation: 517

Application.Wait Now + TimeValue("00:00:02")

The above will pause for 2 seconds

Upvotes: 2

Related Questions