VSTO & .NET & Excel

March 26, 2007

Conditional Formats

Filed under: VSTO & Excel — Dennis M Wallentin @ 7:37 pm

For some days ago I received an e-mail about creating conditional formatting through code which I sent a reply on. The next e-mail asked specifically about when to create these formats via code.

I can only answer for myself and the below list gives some indication when I use it, i e creating conditional formattings, via code:

  • To highlight various groups of data in pivottables, lists and tables. Usually the raw data is retrieved from database(s).
  • Creating standalone workbooks and/or templates through code where it’s necessary to highlight data in specific areas.

In general I try to avoid other situations then above as I find it complicated to work, through code, with conditional formatting under other circumstances.

With Excel 2007 we now have several new conditional formats to use.  However, the new conditional formats also bring ‘new rules’ for working with them in code. Therefore we need to be aware of it and, of course, know how to work with them.

The following code example shows how we work with one of the old conditional formats, adding a conditional formatting based on an expression, in VSTO:

cfold.png

The most interesting aspect in the above sample is the declaration of the variable p_cfOld as an Excel.FormatCondition’s object.

The following code example shows how we work with one of the new conditional formats, adding a conditional formatting based on the Icon Sets, for Excel 2007 in VSTO:

cfnew.png 

As the above sample shows, we now use the Icon Set’s object instead of the general FormatCondition’s object. If we should try to use the FormatCondition’s object it would end with an exception.

The new conditional format’s objects seem not to be part of the general FormatCondition’s object. Instead the new conditional formats have their own collections and therefore we need to explicit declare the format type(s) involved for specifik tasks. However, as the above samples show, they seem at least to be part of the range object’s conditional format collection.

Kind regards,
Dennis

Advertisements

6 Comments »

  1. Nice overview Dennis, conditional formatting is a deceptvely tricky area. Chip Pearson had some nice stuff on “regular” conditional formatting, I wonder if Chip has an update?

    Conditional formatting is not easy to set programmatically because its a hierarchical group of settings and there are various ways of evaluating which really are different from one another. That is, while “Greater Than”, “Less Than” and “Between” evaluations are related, they are different forms of evalution from “Top 10” or “Percentile”, etc.

    I find conditional formatting to be much easier to set up manually and then copy-paste to the cells you need, rather than trying to set it up programmatically. On the other hand, we might need to READ conditional formatting programmatically, in which case we have no choice but to understand the object model.

    Comment by Mike Rosenblum — March 27, 2007 @ 2:08 am

  2. Mike,

    Yes, I agree that a manually set up is far better and easier.

    It’s a little bit surprising that MSFT didn’t implement an evaluation/read method of the Range class, to grab the present conditional formatting in a specific ranges. I know it was discussed when Excel 2007 was in the beta stage. Perhaps it will be added in the next version.

    For my primary purposes it’s not a big deal to do it programmtically. I’ve used the copy/paste approach in some minor solutions.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — March 27, 2007 @ 1:56 pm

  3. I can’t say that I have ever used it programmatically. The “icon set” is that the new icons that 2007 has in CF? – Does anyone know if you can add your own icon – I thought you could not.

    Comment by Ross — March 28, 2007 @ 4:47 pm

  4. Ross,

    Yes, the “Icon set” CF is new in Excel.

    As for the question of adding customized icons I don’t know as I have not considered in the first place.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — March 28, 2007 @ 5:29 pm

  5. There is no ability to use custom icon sets, just the ones provided by Microsoft.

    Comment by Jon Peltier — March 29, 2007 @ 4:11 am

  6. That’s what i thought – shame
    Cheers
    Ross

    Comment by Ross — March 30, 2007 @ 12:37 pm


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: