Exploring Conditional Format Performance Part 2: What’s slow, whats buggy and whats faster!

This is the second in a series of Posts on Conditional Formats (see part 1).

This post looks at the effects (and the resulting bugs!)  on Conditional Formats of:

  • Application.Screenupdating
  • Application.EnableConditionalFormatsCalculation
  • Application.Calculation
  • Whether the cells containing the conditional formats are visible or not
  • Screen Refresh
  • Excel 2007, Excel 2010 and Excel 2013

I am using the same (but slightly updated) test workbook as in Part 1: you can download it from SkyDrive.

Running the Tests

The FormatConditionsA.xlsb workbook contains 12 VBA subroutines to do the testing (Test1 through Test3C).

Cell B2 uses 2 UDFs (Signal1 and Signal2) to determine whether B2 is even or odd, and the B2 formula refers to D21

Cell E2 has 2 conditional format formulas that test directly whether D21 is even or odd.

CFEx1_1You need to run the tests with the VBE window open and the immediate window visible.
For a more detailed explanation of this example workbook see part 1.

If you run the tests using Excel 2007, Excel 2010 and Excel 2013 you will see that a lot of work has been done by the Excel team to minimise the number of times the conditional formats get executed. But (as always when doing optimisations) this has tended to introduce bugs.

Test1: Screenupdating=True, Enable=True, Calc=Auto

  • Excel 2013: OK, 1 call to each UDF, large pause of a second or two before cell B2 refreshes its colour.
  • Excel 2010: OK , 2 calls to each UDF, no noticeable pause.
  • Excel 2007: OK, 4 calls to each UDF, no noticeable pause.

Test2: Screenupdating=true, Enable=true, Calc=manual

  • Excel 2007 & Excel 2010: OK
  • Excel 2013: Bug in cell B2neither of the conditional formats is applied to B2 and neither of the UDFs are executed. Scrolling down and up to refresh the screen does not fix this, but pressing F9 does.

CFEx2_1

Test3: ScreenUpdating=False, Enable=True, Calc=Manual

  • Excel 2013: OK
  • Excel 2010: Bug in B2. Neither of conditional formats are applied to B2 and the UDFs are not executed. Page Down Page Up does not fix but F9 does.
  • Excel 2007: Bug in B2. Page Down Page Up fixes.

Test1A: Screen=True, EnableFormatConditionsCalculation=False, Calc=Auto

So what does setting EnableFormatConditionsCalculation to False actually do?
I am not sure, but what it does NOT do is to permanently switch off the evalution of conditional formats!

  • Excel 2007: OK – the pause in Test 1 has disappeared!
  • Excel 2010: Bug in Cell E2. the left-most vertical border is coloured correctly but the rest of the cell is not! Page Down Page Up fixes it.
  • CFEx2_1A
  • Excel 2013: Bug in cell B2 and E2. Page Down Page up fixes it.CFEx2_1A2013

Test2A: Screen=True, EnableFormatConditionsCalculation=False, Calc=Manual

  • Excel 2007: OK
  • Excel 2010: Bug in cell E2. Page Down Page Up fixes it.
  • Excel 2013: Bug in cell B2. Neither Page Down Page Up nor F9 fix it, but Ctrl/Alt/F9 does.

Test3A: Screen=False, EnableFormatConditionsCalculation=False, Calc=Manual

  • Excel 2013: OK
  • Excel 2010: Bug in cell B2: Neither Page Down Page Up nor F9 fix it, but Ctrl/Alt/F9 does
  • Excel 2007: Bug in cell B2. Page Down Page Up fixes it.

Tests 1B to 3C: switching to another sheet, run the tests, switch back

Its magic: all these tests run correctly in all versions!

Conclusions

  • Looks like using UDFs in conditional format formulas is rather buggy: avoid.
  • EnableFormatConditionsCalculation does not look useful.
    But there were many reports of a problem importing Excel 2003 files with conditional formats into later versions that could be fixed by setting it to True: I don’t know if this problem still exists.
  • The safest way is to activate a sheet that does not contain any conditional formats.

The next post will focus on the performance of conditional formats.

This entry was posted in Calculation, Formatting, UDF, VBA and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s