+ Reply to Thread
Results 1 to 4 of 4

VBA Worksheet slows dramatically after multiple runs

Hybrid View

  1. #1
    Registered User
    Join Date
    04-04-2012
    Location
    Savannah Ga
    MS-Off Ver
    Excel 2003
    Posts
    1

    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

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,794

    Re: VBA Worksheet slows dramatically after multiple runs

    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

  3. #3
    Registered User
    Join Date
    12-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: VBA Worksheet slows dramatically after multiple runs

    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.

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,794

    Re: VBA Worksheet slows dramatically after multiple runs

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1