Coloring Excel charts the hard way

I’ve been managing chart colors in Excel VBA. I had finished most of it last year, but just today had to track down a bug in the coloring. Working with colors has been an adventure since I am using Office ’03. In ’07, Microsoft made a lot of significant changes to internal stuff that would have made this a lot nicer to work with, although a lot of idiosyncrasies are still there.

Here’s my understanding of how Excel ’03 manages its colors:

Excel has a color palette that holds 56 colors. Those are the only colors you can use in your spreadsheet. It appears that, internally, all objects’ colors are stored not as their RGB value but rather by the index of the color palette. In addition to being limited to using the palette, the palette itself is indexed in a very confusing way (default colors):

You may think that you can overcome the color restriction programmatically – I certainly thought I could. That is how I discovered a very strange idiosyncrasy with Excel’s color management.

Here we have a simple subroutine that changes the plotcolor on a chart:

Sub ColorPlot(myChart as Chart, plotColor As Long)
     myChart.PlotArea.Interior.color = plotColor
End Sub

If I pass the above chart and the color value 1390026 to ColorPlot, I get the following:

However, the color represented by the Long 1390026 is actually on the right:

Let’s double check the color values, just to see what comes up:

Private Sub CommandButton1_Click()
     With ActiveSheet.ChartObjects(1).Chart.PlotArea.Interior
          .Color = 1390026
          MsgBox ("Color I wanted: 1390026" & vbCrLf &  _
          "Color I got: " & .Color & vbCrLf & _
          "Index of plot's color in palette: " & .colorIndex)
     End With
End Sub

If you refer to the palette above, you’ll see that the default color in index 53 is our dark orange color.

It turns out that, should you set a color property to a value that isn’t in the current palette, Excel ‘03 will use a color from the palette that most resembles the color you want. (Interestingly, it only chooses from the 40 “standard” colors and ignores chart line and chart fill colors) It doesn’t throw an error; it doesn’t notify you; it doesn’t simply ignore the command to change color - instead it "guesses". That is a monster of an undocumented “feature” that takes a long time to find, since the initial assumption for why the colors are weird is “programmer error”. programmers don't want a computer to guess, programmers want a computer to do what it is told. The most frustrating thing is that the whole thing could had been avoided had they made the color property read-only but allowed the colorIndex property to be set. Instead, someone went through the trouble of actually writing the routines that match the desired color to one in the palette - this was done on purpose.
Luckily, this was fixed in ’07 as you can see in the example this site provides.

So, how do we get around this? Well, the only way in Excel is to change colors on the palette to the ones we want, retain the index of the new colors, and set the object’s color to the color at that index. Remember: should the color at that index change, the object’s color will change too.

A section of my application lets users choose colors for a specific plot as well as the series in that plot. When I went searching for documentation on Excel’s built-in color picker dialog, I found and now use this implementation and am very happy with it. (although, for some reason, the Show method for the colorpicker dialog will fail if anything but a cell is selected.) Great, I can now pick a color. To change the color of, for example, a plot, I can do:

Public Sub ColorPlot(myChart as Chart, plotColor As Long)
     Const colorindexlast As Long = 32
     ActiveWorkbook.Colors(colorindexlast) = plotColor
myChart.PlotArea.Interior.colorIndex = colorindexlast
End With

The constant I declared is the index of the last color slot, located in the bottom right of the palette:

There. Now we can change plot colors properly!

So now that I could be basic manipulation, I wanted to try something fun. I wanted the gridlines to change color automatically when the user selects a color for the background. I wanted to always make the gridlines lighter than the plot background. I was aware of HLS (hue, luminosity, saturation) format for colors and figured I could convert RBG to HLS, alter the luminosity and then convert back. Luckily for me, there are library functions to handle the conversion! I ended up with something like the following:

'---This module provides some basic color manipulation functions and declarations
'---for the library HLS and RGB conversions

Option Explicit
'---These are library functions for converting between the color types (RGB and HSL)
Private Declare Function ColorRGBToHLS Lib "shlwapi.dll" _
     (ByVal clrRGB As Long, _
     pwHue As Long, _
     pwLuminance As Long, _
     pwSaturation As Long) As Long

Private Declare Function ColorHLSToRGB Lib "shlwapi.dll" _
     (ByVal wHue As Long, _
     ByVal wLuminance As Long, _
     ByVal wSaturation As Long) As Long

'---Lightens a given RGB color, and returns the RGB value of the lightened color.
'---LightMultAmount is the percent by which to modify the luminance. Default is 1.5 times.

'---Lightaddamount is if you want to just add a fixed value to the Lum; default is 0.
'---To just add but not multiply, set lightAddAmout to whatever value you want, and set lightMultAmount to 1.

Public Function LightenColor(rgbColor As Long, Optional lightAddAmount As Long = 0, Optional lightMultAmount As Double = 1.5) As Long
     Dim pHue As Long
     Dim pSat As Long
     Dim pLum As Long

     ColorRGBToHLS rgbColor, pHue, pLum, pSat
     pLum = (pLum + lightAddAmount) * lightMultAmount
     If pLum > 240 Then pLum = 240 'For whatever reason, VBA's Lum only goes to 240 instead of the usual 255.
     LightenColor = ColorHLSToRGB(pHue, pLum, pSat)
End Function

And in use:

Private Sub CommandButton1_Click()
     Dim myChart As Chart
     Dim lPlotColor As Long
     Dim lGridColor As Long

     Set myChart = ActiveSheet.ChartObjects(1).Chart
     lPlotColor = PickNewColor(0)
     lGridColor = LightenColor(lPlotColor, 30, 1)
     Call ColorPlot(myChart, lGridColor, lPlotColor)
End Sub

Sub ColorPlot(myChart As Chart, gridColor As Long, plotColor As Long)
Const colorindexlast As Long = 32
     Dim myAxis As Axis
     Dim myorgcolor As Double

     With myChart
          ActiveWorkbook.Colors(colorindexlast) = plotColor
          .PlotArea.Interior.colorIndex = colorindexlast
          ActiveWorkbook.Colors(colorindexlast - 1) = gridColor

          For Each myAxis In .Axes
               myAxis.HasMajorGridlines = True
               myAxis.MajorGridlines.Border.colorIndex = colorindexlast - 1
     End With
End Sub

All that work for so little gained. Except for experience, of course.

0 things about

Coloring Excel charts the hard way

Post a Comment

Copyright 2012 Phile not Found. See About
Powered by Blogger

"Whenever you find that you are on the side of the majority, it is time to pause and reflect."