Conditional formatting rules that rely on UDFs break on worksheets created via Excel Interop.

[Matt Puentes]

Note: this is a re-post of an issue I posted on the Github repo here: https://github.com/Excel-DNA/ExcelDna/issues/759. I'm not sure which is preferred, the google group or the github, so apologies for the doublepost.

=============

Hello, I've recently been developing an ExcelDNA project in which I am trying to use a User-Defined Function from within a conditional formatting rule. However, I've run into some odd behavior that I can't find a workaround for.

If I apply conditional formatting rules to a sheet that was created via the Excel UI (for example, with the "+" button on the bottom of the screen), I don't run into any issues. Both normal conditional formatting rules and rules that include user-defined functions work as expected.

However, if I apply conditional formatting rules to a sheet that was created through the Excel Interop (Via Worksheets.Add), conditional formatting rules with user-defined functions in them break, and don't format any cells at all. This is despite the rules showing up in the rule manager, and breakpoints in the user-defined functions showing that the functions are being run and returning as expected. Conditional formatting rules that do not rely on user-defined functions are not effected.

As an additional wrinkle, the behavior is slightly different if the conditional formatting rule explicitly sets a border as part of it's applied style. In this case, the rule will apply, but only once. If you scroll to a new part of the sheet (even one that should still be in range), the rule with a user-defined function in it will not format new cells as they come on-screen. Additionally, editing any cell within the format condition's range will cause the rule to break and stop formatting.

Attached is a sample project I made to demonstrate.

It includes buttons that populate a sheet and provide two new conditional formatting rules- one that highlights every cell with a value of 2 via a traditional conditional formatting rule, and one that highlights every cell with a value of 3 via a user-defined function also included in the project.

If you run the solution, you should have a new tab in the ribbon ("Conditional Formatting Example") With several buttons on it. These can help demonstrate the behavior.

1. If you create a new sheet via the Excel UI, the "On This Sheet" buttons work as expected. The rules are created and work properly.

2. If you click the "On New Sheet" button in this first group, you will notice that only the twos are highlighted, despite both rules existing in the conditional formatting rules manager. You can use breakpoints to show that the IsThree function is being run and returning properly. This demonstrates that only conditional formatting rules that are based on user-defined functions are breaking.

3. If you click the "On New Sheet" button in the second group, you will see the rules supposedly working fine- however, as soon as you edit any cell within column B, the rules will stop working. Additionally, if you scroll down before editing a cell, you'll see that only a portion of the sheet had the Conditional Formatting rule working in the first place (by scrolling around and re-applying the bordered style via the "On This Sheet" button, you can see that cells that are on-screen are the ones that get formatted).

I have found limited other threads with this issue. One of them is here: https://groups.google.com/g/exceldna/c/WgU2r56LylE, in which it's suggested to toggle the EnableFormatConditionsCalculation property. I included a button to toggle that for the sake of demonstration (it does not work).

It's worth noting that you cannot directly use UDF from within a conditional formatting rule, and instead need a named object to bridge the gap, as documented here: https://groups.google.com/g/exceldna/c/3-PWh-TSVx8. I don't know if this is related to the issue, but the names always appear as expected in the name manager.

This issue has been a real blocker for me, and I'm not sure how it could be circumvented. It seems like the use-case of needing a C# user-defined function based conditional formatting rule that works on sheets made dynamically via the Excel interop is pretty rare, but it's important for my project and it would be very helpful if I could find a way to make it work.

Thank you for your time, I am a big fan of the project.

[Govert van Drimmelen]

Hi Matt,

This sounds like a tricky problem, and you've clearly done a lot of exploring already.
My initial thoughts are

* Does this happen with a simple native code C .xll add-in? Confirming that will at least show that Excel-DNA does not have anything specifically causing the problem.

* Can you reproduce the issue using UDFs defined in VBA?

* Can you see a difference in the persisted files (the XML extracted when unzipping the xlsx) for file where the sheets were made in different ways? That might be some clue to what us going wrong (or different) when using the interop to make the sheet.

Once you have a bug that reproduces without 3rd party software, you can try to get it reported to the Excel team. This can be quite hard though.

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/exceldna/8a3cf85b-6e80-4ddd-8a9f-82fa54783624n%40googlegroups.com.