+ Reply to Thread
Results 1 to 4 of 4

Document is Running VERY slowly ... Too many equations to process?

Hybrid View

  1. #1
    Forum Contributor jonvanwyk's Avatar
    Join Date
    06-28-2010
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2010
    Posts
    452

    Document is Running VERY slowly ... Too many equations to process?

    Attached to this post is a copy of an electronic timesheet I have been developing. There are many nuances that go into our organization's schedule resulting in a lot of formulas. I just now began experiencing a sluggish document and have no idea why it is being slow. If you type in a time such as "1500", VBA code automatically converts it to 15:00 (representing 3:00pm). You will notice that there is a significat delay when you type in a time and hit the tab or arrow button to go to the next cell. Any light anyone may shed on this issue is greatly appreciated!
    Attached Files Attached Files
    Last edited by jonvanwyk; 12-07-2010 at 01:22 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Document is Running VERY slowly ... Too many equations to process?

    Without getting into the merits of the models design you should note that Conditional Formatting is super volatile - the sheer volume of rules in place is the main culprit.

    To illustrate - if you run the below code (removes all conditional formatting) in a back up version of your file and then test the subsequent performance you will (I think) notice the difference.

    Sub Remove_CF()
        Dim ws As Worksheet, xlCalc As XlCalculation
        On Error Resume Next
        Set ws = Sheets("0703")
        With Application
            xlCalc = .Calculation
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .ScreenUpdating = False
            ws.Cells.SpecialCells(xlCellTypeAllFormatConditions).FormatConditions.Delete
            .Calculation = xlCalc
            .EnableEvents = True
            .ScreenUpdating = True
        End With
        Set ws = Nothing
    End Sub
    I suspect you can revise your rules if you need the conditional formatting but I imagine from my initial glance there are other gains to be had by virtue of a re-design.

  3. #3
    Forum Contributor jonvanwyk's Avatar
    Join Date
    06-28-2010
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2010
    Posts
    452

    Re: Document is Running VERY slowly ... Too many equations to process?

    Thank you for taking the time to look at my project. I will attempt your solution, but I am fairly new to VBA code and may have additional questions. Thanks again.
    Last edited by DonkeyOte; 11-24-2010 at 03:41 AM. Reason: removed unnecessary quote

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Document is Running VERY slowly ... Too many equations to process?

    To be clear - the VBA is not a "solution" per se - it is merely to illustrate that the Conditional Formatting is the cause of the slow down [by removing all the rules you will see the difference in performance]
    On that basis: run on backup version

    In terms of resolution - you will need to revisit your Conditional Formatting requirements and/or configuration.

+ 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