VBA Worksheet slows dramatically after multiple runs

I have been running an excel Macro for a while now and keep running into the same issue with it. After multiple runs of the macro on a worksheet the performance eventually slows down dramatically. The code runs the Excel solver function on a bunch of different design points. when first created it will run 600 points in 3-4 minutes. Once it slows down it takes upwards of 30 minutes. The slowdown doesn't happen gradually. Rather one day I will run the code and it will be fast and the next day it will be slow.

I believe it is a worksheet issue more than a VBA issue because I can re-create the sheet (even within the same workbook) and it will run fast again. If I copy the cells and VBA code directly to a new sheet nothing changes. However, copy and paste special the cells as values, copy the VBA code, and manually re-insert the formulas in the appropriate cells it will run fast again for a while.

Any help that would eliminate the need for me to copy to a new sheet would be much appreciated.

Thanks
DKlutzke

Sub Macro_2_Pass_Fuel_Solver()

    Dim i As Long
    Application.ScreenUpdating = False
    i = 6
    Do Until IsEmpty(Range("A" & i))

        SolverReset

        SolverAdd CellRef:=Range("I" & i), Relation:=1, FormulaText:="$I$2" 'sets maximum fuel vol tank 1
        SolverAdd CellRef:=Range("J" & i), Relation:=1, FormulaText:="$J$2" 'sets maximum fuel vol tank 2
        SolverAdd CellRef:=Range("K" & i), Relation:=1, FormulaText:="$K$2" 'sets maximum fuel vol tank 3
        SolverAdd CellRef:=Range("I" & i), Relation:=3, FormulaText:="$I$1" 'sets Min fuel vol tank 1
        SolverAdd CellRef:=Range("J" & i), Relation:=3, FormulaText:="$J$1" 'sets Min fuel vol tank 2
        SolverAdd CellRef:=Range("K" & i), Relation:=3, FormulaText:="$K$1" 'sets Min fuel vol tank 3
        SolverAdd CellRef:=Range("W" & i), Relation:=2, FormulaText:=0 'says sum of three tanks must equal total fuel vol
        SolverOptions MaxTime:=1000, Iterations:=1000, Precision:=0.000001, AssumeLinear:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, IntTolerance:=2, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True
        SolverOk SetCell:=Range("v" & i), MaxMinVal:=3, ValueOf:="0", ByChange:=Range("I" & i & ":J" & i & ":K" & i)
        
        SolverSolve userFinish:=True
        i = i + 1
    Loop
    
    Application.ScreenUpdating = True
End Sub

Could you possibly update your workbook? As I´m on holyday at the moment it will take me some days before I can have a look at it but it looks like an interesting problem.

Alf

Do you happen to be using conditional formatting in this sheet?

For some reason Excel allows the same conditional formatting rules to stack repeatedly on the same cells.
If you run some code that affects conditional formatting, you can end up with literally hundreds or thousands of copies of the same rule in each cell it's applied to.

The only work-around I came up with for this (which was easy because of the nature of my program) was to clear all conditional formatting and then re-apply it. Since I already had a sub that applied all the formatting, it was easy to update. You don't have to do this each time your code runs (that takes time) but rather, what I did, was create a button labeled "Sheet running slow? This could help" and when pressed it clears and updates all formatting.

Long-winded response that may not be relevant so hopefully it helps someone out there if not you.

Hi Methodican

As the OP posted this question in April 2012 and never gave any feed back to my input I don't think you can expect any response to your post.

Still I was happy to read your comments as I was aware that Excel is a memory hog but I did not know conditional formatting may "stack up" repeatedly in the same cell. Useful knowledge!

Alf