VBA for Excel WorkSheet_Calculate method with Add-In Solver creates infinite loop

Question

I have an excel sheet with a set of about 300 filled out cells: about half data and about half calculations. In cells C5, C6, C7, C8, C9 and C10 I have formulas. If the result in those changes (i.e., if they are recalculated) then I need to run the solver add-in with a particular set of values depending which cell it is. Below is my working code so you see what I mean (I've included the first 2/6 as examples):

Private Sub Worksheet_Calculate()
    Static oldVal
    Static oldVal2
    Static oldVal3
    Static oldVal4
    Static oldVal5
    Static oldVal6

    If Range("C5").Value <> oldVal Then
        oldVal = Range("C5").Value
        SolverOk SetCell:="$F$5", MaxMinVal:=3, ValueOf:=0.1075, ByChange:="$G$5", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve True
    End If

    If Range("C6").Value <> oldVal2 Then
        oldVal2 = Range("C6").Value
        SolverOk SetCell:="$F$6", MaxMinVal:=3, ValueOf:=0.1075, ByChange:="$G$6", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve True
    End If

    '...
    '...

End sub

I have two problems. First is that it doesn't seem to only run solver if the cell (i.e., C5) was changed. It runs all of them no matter what is calculated in the workbook. The second and much bigger problem is that when it gets to solver and goes to run it, solver immediately seems to trigger the Worksheet_Calculate method again and so it gets stuck in an infinite loop and crashes.

Any smart workarounds for this?

Thanks so much in advance!

The typical way to handle this is to use Application.EnableEvents = False before doing anything which would trigger a recalculation. don't forget to re-enable events before exiting the procedure. — Tim Williams
Thank you so much, Tim! Didn't realize that was something you could do :-) Very much appreciated! — Darren Cole

Darren Cole Darren Cole · Accepted Answer · 2015-07-19T17:14:48

Answer found from Tim Williams' answer (https://stackoverflow.com/users/478884/tim-williams): The typical way to handle this is to use Application.EnableEvents = False before doing anything which would trigger a recalculation. don't forget to re-enable events before exiting the procedure.

VBA for Excel WorkSheet_Calculate method with Add-In Solver creates infinite loop

1 Answers