J-Walk & Associates, Inc.

Exploring Theme Colors

I've been playing around with colors in Excel 2007. As an exercise to help me figure it out, I'm trying to create a tear-off color picker that works for any selected object. I've managed to create the movable dialog box (see below) that works great for ranges. But then I ran into all sorts of problems when I tried to extend it to work with shapes and charts.

In a previous blog post, I issued a challenge to write a VBA macro that makes the active cell exactly the same color as a shape (and keep the color "theme-able." I dug a little deeper, and now I'm 90% convinced that the task is not possible.

Create a shape and give it a solid background color. Save the workbook in Excel 2007 format and examine the XML code. You'll see something like this (displayed in an XML viewer):

Notice that the theme color for the shape is stored (in this case, "accent6"). The "tint and shade" variation of the color is (somehow) stored in two other parameters lumMod and lumOff.

If you use VBA to determine the TintAndShade property of a color chosen from the UI, the answer is always 0.

Now use VBA to assign the color to the shape:

Sub shadeShape()
    With ActiveSheet.Shapes(1).Fill.ForeColor
       .ObjectThemeColor = msoThemeColorAccent6
       .TintAndShade = 0.8
     End With
End Sub

Save the file again, and look at the XML. This time, you see something like this:

Now the TintAndShade property has a value (accessible via the object model), and it's also stored in the XML file.

I turned to the Office Open XML Primer to see if I could get a clue.

• lumMod: Yields the input color with its luminance modulated by the given percentage. A 50% luminance modulate will reduce the luminance by half. A 200% luminance modulate will double the luminance.
• lumOff: Yields the input color with its luminance shifted, but with its hue and saturation unchanged.

No help there.

There are two ways to give a shape a color: using the UI, or using the object model. Apparently, the method you choose determines which color model is used.

The question remains: Can VBA determine the TintAndShade value for a shape color assigned via the UI? VBA can get two pieces of the puzzle: the theme color RGB value, and the actual RGB color of the shape. So can we calculate the TintAndShade value from these to values? I think not.

Just for fun, I wrote some code that should make a range exactly the same color as a shape:

Sub ShadeShapeAndRange()
     With ActiveSheet.Shapes(1).Fill.ForeColor
          .ObjectThemeColor = msoThemeColorAccent5
        .  TintAndShade = -0.4
     End With
     With Range("A1:A10").Interior
          .ThemeColor = xlThemeColorAccent5
          .TintAndShade = -0.4
     End With
End Sub

And here's what you get:

In an earlier post, I showed how the same VBA code produces different colors in Word and Excel. Here, I show that the same VBA code produces different colors in Excel!

Bottom line? Working with color in Excel is more complicated than ever, and it seems that some aspects of color aren't even accessible via the object model. Microsoft's idea of document themes is a good one, but it's implementation is seriously flawed.

It would be very helpful if someone at Microsoft wrote a white paper that explains exactly how colors work in Office 2007.

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By Jon Peltier. Comment posted 15 September, 2008 7:07pm

   I presume you've looked at Tony Jollans' articles on colors in Word 2007.
   Colours in Word 2007, Part 1
   Colours in Word 2007, Part 2
   
   (What's this 1000 character limit???)
2. By Jon Peltier. Comment posted 15 September, 2008 7:09pm

   Charley Kyd and I have discussed the theme color thing, how incompletely it's been implemented, and how much less control you have in 2007. In Excel 2003, you had only 56 colors, but you had complete control over them, and if you changed a color, everything else in that color changed to match the adjusted color.
   
   In 2007, you have a funky palette with gradations of ten colors, two of which supply all the shades of gray between black and white. So you have control over eight colors, and everything else is based on them. You can define as many custom colors as you like, but they are not linked to the theme, and therefore do not update globally across all objects using that color. This reduces the effectiveness of the theme concept.
   
   In 2003, you could count on certain colors being default colors in a chart, and you could tailor the palette accordingly. This is no longer the case. You have a fancy gallery of themes, but it's not clear how the colors correspond with the theme colors.
3. By John Walkenbach. Comment posted 15 September, 2008 7:18pm

   (What's this 1000 character limit???)

   
   It's based on the notion that if you can't make your point in 1,000 characters, you should get your own blog and post a link.
4. By Doug Jenkins. Comment posted 16 September, 2008 8:40pm

   I've just posted here: http://newtonexcelbach.wordpress.com/2008/09/17/drawing-in-excel-6-getting-shape-properties/ a UDF that will return some of the property values (94 of them to be exact) for any named shape.
   
   It doesn't help with the TintandShade value, which mysteriously always returns zero (and will also prevent the function from running in XL 2000), but I do find it pretty useful nonetheless.