Application.CheckAbort RRS feed

  • Question

  • The Help description for this Method is suspect and its behavior, or lack thereof, even more so. It takes an optional Boolean argument but the example uses a Range object; peculiar.

    While failing to find a way to prevent a worksheet's Conditional Formatting over a large range from running every time the selection changed, I found the subject method which sounded promising. Reason is that Worksheet.enableFormatConditionsCalculation = False is being ignored by the calculation engine.

    Unfortunately, Application.CheckAbort is also failing to change any behavior. Maybe these issues are somehow related to a corruption in the dependency trees but I've run Application.CalculateFullRebuild with no improvement to the problem.

    I've tight control over when Application.Screenupdating is toggled. The same is true for the usual suspects; enableEvents, enableCalculation, and Calculation. Still, the Conditional Formatting is slowing use of the app down to ridiculous.

    Looking for info on CheckAbort and more importantly how to control the Conditional Formatting calculations.


    • Edited by tocguy001 Thursday, March 30, 2017 12:05 AM typo
    Wednesday, March 29, 2017 11:33 PM

All replies

  • My interest here is how you set up the Conditional Format. I have seen a similar slow response with Conditional Formatting where the person set up the format on a single cell and then copied the format of that cell and then PasteSpecial -> Formats to other cells and it resulted in each individual cell (or sometimes a small range of cells) having its own Conditional format.

    Please forgive me it I am telling you something you already know but the correct method of expanding the conditional format to include other cells/ranges:

    1. Select the cell containing the Conditional format
    2. Select Conditional formatting -> Manage Rules
    3. Select the rule and then  edit the "Applies to" field for the entire range to which to apply the conditional format (To edit "Applies to" field, delete the existing range and then click the icon at right of field and then select the new range with the mouse on the worksheet. Include the original cell in the new range. The Ctrl key can be used to apply to split ranges.)

    If Conditional format is applied as above then normally it is very fast. 

    Following added after initial posting:

    If you have got fragmented conditional formatting then the existing formatting must be deleted before attempting to correct as per my suggestion otherwise you will still have all of the old formatting plus the new. Use Conditional Formatting -> Clear and follow prompts to remove old formatting first.

    If the above is not the problem then if you can upload an example of the workbook to OneDrive then I will have a look at it.

    Regards, OssieMac

    • Edited by OssieMac Thursday, March 30, 2017 9:57 AM
    Thursday, March 30, 2017 4:12 AM
  • Irrespective of what Help documents for CheckAbort the optional KeepAbort argument, on inspecting Excel's TypeLib it's a Variant not a Boolean. Obviously the variant could accept a boolean but hard to imagine how a boolean might be used in context. But even with a Range or address CheckAbort doesn't seem to do anything!

    Also EnableFormatConditionsCalculation does not appear to do anything as has been reported by others over the years since introduced undocumented in 2007.

    I don't know any way to prevent CFs from updating with recalc, and indeed many CF's over a large range can slow things down.

    It might be worth checking your CF rules to ensure there aren't any unnecessary or duplicate rules as a result of copying ranges with CFs, as OssieMac suggested.

    Thursday, March 30, 2017 9:39 AM
  • Hello @OssieMac,

    The model is a table of 85 columns. I'd setup the CF based on column ranges, i.e., select the column -> CF -> Manage Rules. The Rule would employ a UDF as follows. This same UDF was used for all columns. The UDF itself used the info contained within 'reference cell on this same sheet' to determine the applicable CF calculation to perform in the UDF and return value. I'd thought, mistakenly in hindsight, that this was efficient.

    =myCFRule(thisCell, reference cell on this same sheet)

    Sadly, as the table row count grows the performance drops geometrically. It's as if the CF engine goes berserk. No amount of holding 'ESC', toggling enableFormatConditionsCalculation or enableCalculation off, or setting xlCalculationManual makes any difference whatsoever. One can only go grab another cup of coffee while waiting for CF to finish so many times...

    In the end, I created a Sub to perform the equivalent CF duty of evaluating and marking errant cells, i.e., scan the entire table once and mark errant cells. Next, the Worksheet_Change event method was employed to re-evaluate cells as they are changed to clear or set marking as needed.

    This technique works quite nicely and avoids the CF engine's uncontrollable behavior.

    I am curious where one finds the info @Peter Thornton located about the CheckAbort function within Excel's Typelib.

    Wednesday, April 5, 2017 6:51 PM
  • I did some tests on conditional formats performance:

    Charles Excel MVP The Excel Calculation Site

    Wednesday, April 5, 2017 8:28 PM
  • I am curious where one finds the info @Peter Thornton located about the CheckAbort function within Excel's Typelib.

    I have my own app which dumps details of the public methods of exe/dll/ocx/olb/tlb type files. However in object browser you can also infer the KeepAbort argument is a Variant by the fact its type is not specified, in contrast to non Variants which are.
    Friday, April 7, 2017 9:08 AM
  • I did once talk to someone from the Excel team about CheckAbort - I don't recall exactly what its supposed to do but I think it had something to do with checking or ignoring user interrupts. The only thing I definitely recall is that it did not seem very useful and that the Help is complete rubbish.

    Charles Excel MVP The Excel Calculation Site

    Friday, April 7, 2017 10:00 AM
  • Very interesting, particularly about the relevance if on the active sheet. Is there a similar delay following a calculation to update when the CF sheet is activated, similar to what you mentioned about how disabling screenupdating merely postpones until re-enabled?
    Saturday, April 8, 2017 10:58 AM