The RGB color system combines various levels of three colors: red, green, and blue. Each of these component colors can range from 0 through 255. Therefore, the total number of possible colors is 256 × 256 × 256 = 16, 777, 216. When all three color components are 0, the color is pure black. When all three components are 255, the color is pure white. When all three are 128 (the half–way point), the color is medium gray. The remaining 16, 777, 213 possible combinations of these three values represent other colors.

To specify a color using the RGB system in VBA, use the RGB function. This function accepts three arguments that represent the red, green, and blue components of a color. The function returns a decimal color value.

The statement that follows uses the RGB function to assign a color that's exactly the same as the one assigned in the preceding section (that dark maroon, 5911168):

ActiveCell.Interior.Color = RGB(128, 50, 90)

Table 16-1 shows the RGB components and the decimal color code of some common colors.

If you select the More Colors option when choosing a color in Excel, you'll see the Colors dialog box. Click the Custom tab, and you can choose from two color models to specify your color: RGB and HSL. Figure 16-1 shows the Colors dialog box with the HSL color model selected.

In the HSL color system, colors are specified using three parameters: hue, saturation, and luminance. As with RGB colors, each of these parameters can range from 0 to 255. Each RGB color has an equivalent HSL color, and each HSL color has an equivalent RGB color value. In other words, any of the 16, 777, 216 colors can be specified by using either the HSL or RGB color system.

Figure 16-1. Choosing a color using the HSL color system.

If you know the three color component values, converting an RGB color to a decimal color value is easy. Just use VBA's RGB function. Assume three variables (r, g, and b), each of which represents a color component value between 0 and 255. To calculate the equivalent decimal color value, use a statement like this:

DecimalColor = RGB(r, g, b)

To perform this conversion in a worksheet formula, create this simple VBA function in a VBA module:

Function RGB2DECIMAL(R, G, B)
,    Converts from RGB to decimal color
     RGB2DECIMAL = RGB(R, G, B)
End Function

The following example worksheet formula assumes that the three color values are in A1:C1:

=RGB2DECIMAL(A1, B1,C1)

Converting a decimal color to its red, green, and blue components is not so simple. Here's a function that returns a three–element array:

Function DECIMAL2RGB(ColorVal) As Variant
'   Converts a color value to an RGB triplet
'   Returns a 3–element variant array
    DECIMAL2RGB = Array(ColorVal  256 ^ 0 And 255, _

ColorVal  256 ^ 1 And 255, ColorVal  256 ^ 2 And 255)
End Function

To use the DECIMAL2RGB function in a worksheet formula, the formula must be entered as a three–cell array formula. For example, assume that cell A1 contains a decimal color value. To convert that color value to its RGB components, select a three–cell horizontal range and then enter the following formula. Press Ctrl+Shift+Enter to make it an array formula, and don't enter the braces.

{=DECIMAL2RGB(A1)}

If the three–cell range is vertical, you need to transpose the array, as follows:

{=TRANSPOSE(DECIMAL2RGB(A1))}

Figure 16-2 shows the DECIMAL2RGB function in use in a worksheet.

Figure 16-2. The DECIMAL2RGB function converts a decimal color value to its red, green, and blue components.

One approach to grayscale conversion is to simply average the red, green, and blue components of a color and use that single value for the red, green, and blue components of its grayscale equivalent. That approach, however, does not take into account the fact that different colors are perceived as varying levels of brightness. For example, green is perceived to be brighter than red, and red is perceived to be brighter than blue.

Perceptual experiments have arrived at the following "recipe" to convert an RGB color value to a grayscale value:

For example, consider color value 16751001, a shade of violet that corresponds to RGB(153, 153, 255). Applying the factors listed above, the RGB values are as follows

The sum of these values is 163. Therefore, the corresponding grayscale RGB value for color value 16751001 is RGB(163, 163, 163).

Following is a VBA function that accepts a decimal color value as its argument, and returns the corresponding grayscale decimal value:

Function Grayscale(color)
    Dim r As Long, g As Long, b As Long
    r = (color  256 ^ 0 And 255) * 0.287
    g = (color  256 ^ 1 And 255) * 0.589
    b = (color  256 ^ 2 And 255) * 0.114
    Grayscale = RGB(r + g + b, r + g + b, r + g + b)
End Function

Here's a technique that can let you see how an embedded chart looks when converted to grayscale:

These steps are automated in the macro that follows. The ShowChartAsGrayScale procedure copies the active chart as a picture and converts the picture to grayscale. After you've determined whether the colors are satisfactory, you can delete the picture.

Sub ShowChartAsGrayScale()
'   Copies the active chart as a grayscale picture
'   Embedded charts only
    If ActiveChart Is Nothing Then
       MsgBox "Select a chart."
       Exit Sub
   End If
   ActiveChart.Parent.CopyPicture

ActiveChart.Parent.TopLeftCell.Select
   ActiveSheet.Pictures.Paste
   ActiveSheet.Pictures(ActiveSheet.Pictures.Count). _
     ShapeRange.PictureFormat.ColorType = msoPictureGrayscale
End Sub

Microsoft Office 2007 ships with 20 document themes, and additional themes can be added. The user interface Ribbon includes several style galleries (for example, the Chart Styles gallery). The styles available in these galleries vary depending on which theme is assigned to the document. And, if you apply a different theme to the document, the document changes to reflect the new theme's colors, fonts, and effects.

Note

If you haven't explored document themes, open the workbook named document theme demo.xlsx found on the companion CD–ROM. This workbook contains a range that shows each theme color, two shapes, text (using the headings and body fonts), and a chart. Choose Page Layout

You can also mix and match theme elements. For example, you can use the colors from one theme, the fonts from another theme, and the effects from yet a different theme. In addition, the user can create a new color set or a new font set. These customized themes can be saved and then applied to other workbooks.

When you apply a color to a cell or object, the color is selected from a control like the one shown in Figure 16-4. The control displays the 60 theme colors (10 columns by 6 rows) plus 10 additional "standard" colors. Clicking the More Colors option displays the Color dialog box, in which the user can specify any of the 16, 777, 216 available colors.

Figure 16-4. A color–selection control.

The 60 theme colors are identified by pop–up ToolTips. For example, using the default Office theme, the color in the second row of the sixth column is known as Red, Accent 2, Lighter 80%. If you're using a different theme, the "Red" Part of the ToolTip is replaced by the actual color for that theme.

Table 16-2 shows all 60 theme colors. The first row shows the color name, and the other rows show the light/dark variation of that color. Examine the table, and you won't see much of a pattern — although the accent colors are consistent in terms of the dark/light variations.

Keep in mind that these color names remain the same, even if a different document theme is applied. The document theme colors actually consist of the 10 colors displayed in the top row, and each of these 10 colors has five dark/light variations.

Note

If you select Page Layout

You may find it enlightening to record a macro while you change the fill color and text color of a range. Unfortunately, recording your actions while you change the color of a chart element does not generate any code.

Following is a macro that I recorded when a range was selected. For the fill color, I chose "Background 2, Darker 50%," and for the text color, I chose "Text 2, Lighter 80%."

Sub Macro1()
    Range("A1:D13").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = −0.499984740745262
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.799981688894314
    End With
End Sub

First of all, you can safely ignore the three pattern–related properties (Pattern, PatternColorIndex, and PatternTintAndShade) in the recorded macro. These properties refer to the ugly, old–fashioned (but still supported) cell patterns, which you can specify on the Fill tab of the Format Cells dialog box. These properties simply maintain any existing pattern that may exist in the range.

The recorded macro, after I delete the three pattern–related properties, is as follows:

Sub Macro1()
    Range("A1:D13").Select
    With Selection.Interior
         .ThemeColor = xlThemeColorDark2
         .TintAndShade = −0.499984740745262
    End With
    With Selection.Font
         .ThemeColor = xlThemeColorLight2
         .TintAndShade = 0.799981688894314
     End With
End Sub

As you can see, each color is specified in terms of a Theme Color property and a TintAndShade property. The ThemeColor property uses built–in constants. The value for these constants is simply the column number of the 10–×–6 theme color table. For example, xlThemeColorDark2 has a value of 3 because that color is in the third column of the theme color table. But what about the TintAndShade property?

The TintAndShade property can have a value between −1 and +1. A value of −1 results in black, and a value of +1 results in white. A TintAndShade property value of 0 gives the "pure" color. In other words, as the TintAndShade value goes negative, the color gets increasingly darker until it's pure black. As the TintAndShade value goes positive, the color gets increasingly lighter until it's pure white. Notice that the TintAndShade values in the recorded macro include many extra decimal places. The TintAndShade value of −0.499984740745262, for example, is equivalent to "Darker 50%."

To arrive at the TintAndShade property value that corresponds to a particular theme color variation, look back at Table 16-2.

Figure 16-5. This workbook demonstrates how the TintAndShade property affects a color.

Using the information in Table 16-2, I wrote the following macro, which displays all 60 theme color variations in a range of cells:

Sub ShowThemeColors()
 Dim r As Long, c As Long
 For r = 1 To 6
   For c = 1 To 10
      With Cells(r, c).Interior
      .ThemeColor = c
      Select Case c
          Case 1 'Background 1
          Select Case r
              Case 1: .TintAndShade = 0
              Case 2: .TintAndShade = −0.05
              Case 3: .TintAndShade = −0.15
              Case 4: .TintAndShade = −0.25
              Case 5: .TintAndShade = −0.35
              Case 6: .TintAndShade = −0.5

End Select
      Case 2 'Text 1
          Select Case r
              Case 1: .TintAndShade = 0
              Case 2: .TintAndShade = 0.5
              Case 3: .TintAndShade = 0.35
              Case 4: .TintAndShade = 0.25
              Case 5: .TintAndShade = 0.15
              Case 6: .TintAndShade = 0.05
          End Select
      Case 3 'Background 2
          Select Case r
              Case 1: .TintAndShade = 0
              Case 2: .TintAndShade = −0.1
              Case 3: .TintAndShade = −0.25
              Case 4: .TintAndShade = −0.5
              Case 5: .TintAndShade = −0.75
              Case 6: .TintAndShade = −0.9
          End Select
      Case Else 'Text 2, and Accent 1-6
          Select Case r
              Case 1: .TintAndShade = 0
              Case 2: .TintAndShade = 0.8
              Case 3: .TintAndShade = 0.6
              Case 4: .TintAndShade = 0.4
              Case 5: .TintAndShade = −0.25
              Case 6: .TintAndShade = −0.5
          End Select
         End Select
        Cells(r, c) = .TintAndShade
         End With
     Next c
   Next r
End Sub

Figure 16-6 shows the result of executing the ShowThemeColors procedure. (It looks better in color.) If you change to a different document theme, the colors will be updated to reflect those in the new theme.

Figure 16-6. A VBA macro generated these theme colors.

So far in this chapter, I've described how to change the fill color of a range by setting the Color property of the Interior object. As I noted, using the VBA RGB function makes this easier. These two statements demonstrate how to change the fill color of a range (they both have the same result):

Range("A1:F24").Interior.Color = 5913728
Range("A1:F24").Interior.Color = RGB(128, 60, 90)

What if you'd like your code to change the background color of a range to a specific theme color, such as the color in the third row of the sixth column (the color identified as Accent 2, Lighter 60%)?

The Excel 2007 designers seemed to have forgotten to include a direct way to specify a theme color using this type of indexing. You might think that the ColorIndex property would do the job, but it doesn't. The ColorIndex property refers to colors in the (pre–Excel 2007) 56–color palette.

In actual practice, this omission is not a serious problem. When setting a color, the important property is the ThemeColor property, which ranges from 1 to 10. Your code can assign a value to the TintAndShade property to vary that color (a negative value for a darker variation and a positive value for a lighter variation). If the user applies a different document theme, the color will still change in a relative manner.

The background color of a Shape object is determined by the RGB property. So, to get the decimal color value of this shape, use a statement like this:

MsgBox ActiveSheet.Shapes("Right Arrow 1").Fill.ForeColor.RGB

This statement may be a bit confusing, so I'll break it down. The Fill property of the Shape object returns a FillFormat object. The ForeColor property of the FillFormat object returns a ColorFormat object. So the RGB property actually applies to the ColorFormat object, and this property contains the decimal color value.

When working with Shape objects, you almost always want your code to perform multiple actions. Therefore, it's efficient to create an object variable. The code that follows creates an object variable named Shp:

Dim Shp As Shape
Set Shp = ActiveSheet.Shapes("Right Arrow 1")
MsgBox Shp.Fill.ForeColor.RGB

Figure 16-8. Typing a statement with the assistance of the Auto List Members feature.

If you'll be working only with the shape's colors, you can create an object variable for the shape's ColorFormat object, like this:

Dim ShpCF As ColorFormat
Set ShpCF = ActiveSheet.Shapes("Right Arrow 1").Fill.ForeColor
MsgBox ShpCF.RGB

The RGB property of the ColorFormat object controls the color of the shape. Following are some additional properties. If you're not familiar with document theme colors, see the "Understanding Document Themes" section, earlier in this chapter.

To set the color of a shape to a theme color, use code like the following:

Dim Shp As Shape
Set Shp = ActiveSheet.Shapes("Right Arrow 1")
Shp.Fill.ForeColor.ObjectThemeColor = 3
Shp.Fill.ForeColor.TintAndShade = .25

Changing the fill color of a shape does not affect the shape's outline color. To modify the color of a shape's outline, access the ColorFormat object of the shape's LineFormat object. The following statements set a shape's fill color and outline to red:

Dim Shp As Shape
Set Shp = ActiveSheet.Shapes("Right Arrow 1")
Shp.Fill.ForeColor.RGB = RGB(255, 0, 0)
Shp.Line.ForeColor.RGB = RGB(255, 0, 0)

Here's an alternate way to accomplish the same effect, using object variables:

Dim Shp As Shape
Dim FillCF As ColorFormat
Dim LineCF As ColorFormat
Set Shp = ActiveSheet.Shapes("Right Arrow 1")
Set FillCF = Shp.Fill.ForeColor
Set LineCF = Shp.Line.ForeColor
FillCF.RGB = RGB(255, 0, 0)
LineCF.RGB = RGB(255, 0, 0)

Shapes can also display other types of fills, such as gradients, pictures, and textures. The examples in this section demonstrate how to apply these other types of fills to a Shape object.

The following code creates a rectangle, hides its border, and applies a two–color gradient. One of the colors is set specifically; the other color is one of the document theme colors. Figure 16-9 shows the result of running this macro.

Figure 16-9. A Shape object with a two–color gradient fill.

Sub MakeShapeWithGradient()
    Dim Shp As Shape

'   Create the shape
    Set Shp = ActiveSheet.Shapes.AddShape( _
        Type:=msoShapeRectangle, _
        Left:=100, _
        Top:=10, _
        Width:=200, _
        Height:=100)

'   Hide the border
    Shp.Line.Visible = False

'   Add 2–color gradient
    With Shp.Fill
        .TwoColorGradient _
           Style:=msoGradientVertical, Variant:=2
        .ForeColor.RGB = RGB(255, 255, 255) 'white
        .BackColor.ObjectThemeColor = msoThemeColorAccent4
    End With
End Sub

The example on the CD–ROM includes additional code that creates the shapes shown in Figure 16-10: a shape with a pattern, a shape with a picture background (and a reflection), a shape with texture (and 3–D effects), and a shape that contains text.

Figure 16-10. These shapes were created with VBA macros.

The information in this section barely scratches the surface when it comes to working with Shape objects. Programming shapes with VBA could easily serve as the subject matter for a complete book.

To find out more about Shape objects, use the Object Browser (press F2 in the VBE), the Help system, the macro recorder (which is of limited value), and the Internet. And don't forget the best tool of them all: experimentation.