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.