The RGB color system combines various levels of three colors: red, green, and blue. Each of these colors can range from 0 through 255. Therefore, the total number of possible colors is 256 x 256 x 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 middle 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, blue, and green 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 30-1 shows the RGB values and the decimal color code of some common colors:

If you select the More Colors option when choosing a color in Excel, you 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 30-1 shows the Colors dialog box with the HSL color model selected.

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

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 decimal color value. In other words, any of the 16,777,216 colors can be specified by using any of the three color systems: RGB, HSL, or decimal.

Although the Colors dialog box lets you specify a color using the HSL color model, this is actually the only area in which Excel supports the HSL color model. For example, when you specify a color using VBA, it must be decimal color value. You can, of course, use the RGB function to return a decimal color value. However, VBA does not have a corresponding HSL function.

If you know the three color component values, converting an RGB color to a decimal color 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 wrapper function:

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

The following example worksheet formula assumes 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 30-2 shows the DECIMAL2RGB function in use in a worksheet.

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

CD-ROM

The companion CD-ROM contains a workbook with the following color conversion functions: DECIMAL2RGB, DECIMAL2HSL, HSL2RGB, RGB2DECIMAL, RGB2HSL, and HSL2DECIMAL. The file is named color conversion functions.xlsm.

Sub GenerateColorValues()
    Dim Red As Long, Blue As Long, Green As Long
    Dim AllColors(0 To 16777215) As Long
    Dim ColorNum As Long
    ColorNum = 0
    For Blue = 0 To 255
       For Green = 0 To 255
          For Red = 0 To 255
             AllColors(ColorNum) = RGB(Red, Blue, Green)
             ColorNum = ColorNum + 1
          Next Red
       Next Green
    Next Blue

End Sub

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 previously, the RGB values are

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

One way to approximate how your colors will look when converted to grayscale is to use Excel’s Print Preview feature with the printer set to a non-color device (such as a fax).

Here’s a technique that lets you see how an embedded chart looks 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

CD-ROM

A workbook with this example is available on the companion CD-ROM. The filename is chart to grayscale picture.xlsm.

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.

CD-ROM

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 Themes Themes Gallery to see how the worksheet changes with each theme.

Users can also mix and match theme elements. For example, it’s possible to 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.

Note

The concept of document themes is based on the notion that users will apply little, if any, non-theme formatting to the document. If the user applies colors or fonts that aren’t part of the current theme, this formatting will not be modified if a new theme is applied to the document. Therefore, it’s still very easy to create an ugly document with mismatched colors and too many different fonts.

When a user applies a color to a cell or object, the color is selected from a control like the one shown in Figure 30-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 30-4. A color-selection control.

The 60 theme colors are identified by pop-up ToolTips. For example, the color in the second row of the sixth column is known as “Accent 2, Tint 20%.” Table 30-2 shows the names of all 60 theme colors. Examine the table, and you see that there isn’t much of a pattern — although the Accent colors are consistent in terms of the tint and shade variations. Also, note that some color variations are known as a tint and others are known as a shade.

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 (four text/background colors and six accent colors), and each of these 10 colors has five tint/shade variations.

Note

If you select Page Layout Themes Colors Create New Theme Colors, you see that a theme has two additional colors: Hyperlink and Followed Hyperlink. These are the colors applied when a hyperlink is created, and they are not shown in the color selector control.

You may find it enlightening to record a macro while you change the fill color and text color of a range. Following is a macro that I recorded when a range was selected. For the fill color, I chose “Accent 2, Shade 75%,” and for the text color, I chose “Text/Background 3, Shade 90%.”

Sub Macro1()
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 56
        .ThemeColor = 6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = 3
        .TintAndShade = -9.99786370433668E-02
    End With
End Sub

First of all, you can safely ignore the three pattern-related properties (Pattern, PatternColorIndex, and PatternTintAndShade). These properties refer to the ugly, old-fashioned (but still supported) cell patterns, which you can specify in 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

Sub Macro1()
    With Selection.Interior
        .ThemeColor = 6
        .TintAndShade = -0.249977111117893
    End With
    With Selection.Font
        .ThemeColor = 3
        .TintAndShade = -9.99786370433668E-02
    End With
End Sub

As you can see, each color is specified in terms of a ThemeColor property and a TintAndShade property. The ThemeColor property is easy enough to decipher. It’s simply the column number of the 10-x-6 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.

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

CD-ROM

For a demonstration of how the TintAndShade property changes a color, open the tintandshade demo.xlsm workbook on the companion CD-ROM (see Figure 30-5). Specify a starting color, and the macro displays that color with 50 levels of the TintAndShade property values, ranging from −1 to +1. It also displays the decimal color value and the red, green, and blue components of the color (which are displayed in a chart).

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

Using the information in Table 30-1, I wrote a macro that 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 'Text/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/Background 2
            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 'Text/Background 3
            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/Background 4, 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 30-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 30-6. A VBA macro generated these theme colors.

CD-ROM

This example, named generate theme colors.xlsm, is available on the companion CD-ROM.

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, Tint 40%”)?

Unfortunately, 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 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, a positive value for a lighter variation). If the user applies a different document theme, the color still changes 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.

Note

If you’re confused about the use of the ForeColor property in this example, you’re not alone. Most people, myself included, would expect to use the BackColor property of the FillFormat object to change the background color of an object. As it turns out, the BackColor property is used for the second color if the object is shaded or filled with a pattern. For an unfilled Shape with no pattern, the ForeColor property controls the background color.

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

Tip

An additional advantage to creating an object variable is that you can take advantage of the VBE’s Auto List Members feature, which displays the possible properties and objects as you type (see Figure 30-8). This is particularly helpful in the case of Shape objects because actions you take with Shapes are not recorded by Excel’s macro recorder.

Figure 30-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 “Understanding document theme colors,” earlier in this chapter.

Changing the background 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 background 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 alternative 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.

CD-ROM

All the examples in this section are available on the companion CD-ROM. The filename is shape object colors.xlsm.

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 30-9 shows the result of running this macro.

Figure 30-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 code that follows creates a Shape that uses pattern fill. See Figure 30-10.

Figure 30-10. This Shape uses a diamond-pattern fill.

Sub MakeShapeWithPattern()
    Dim Shp As Shape


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


'   Hide the border
    Shp.Line.Visible = False


'   Add a pattern
    With Shp.Fill
        .Patterned Pattern:=msoPatternSolidDiamond
        .ForeColor.RGB = RGB(0, 0, 0) 'black
        .BackColor.RGB = RGB(255, 255, 255) 'white
    End With
End Sub

The next procedure is similar, but it uses a picture for the Shape’s background. The graphic file is loaded from the disk. The code also adds a reflection to the Shape. Figure 30-11 shows the result.

Figure 30-11. A Shape that uses a picture.

Sub MakeShapeWithPicture()
    Dim Shp As Shape


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


'   Hide the border
    Shp.Line.Visible = False


'   Add a picture
    Shp.Fill.UserPicture _
        ThisWorkbook.Path & "weirddog.jpg"

'   Give it a reflection
    Shp.Reflection.Type = msoReflectionType5
End Sub

The next Shape object example creates a shape with a texture and applies some 3-D effects and a shadow. Figure 30-12 shows the result.

Figure 30-12. A shape with a texture fill and a few other accoutrements.

Sub MakeShapeWithTexture()
    Dim Shp As Shape
'   Create the shape
    Set Shp = ActiveSheet.Shapes.AddShape( _
        Type:=msoShapeCross, _
        Left:=100, _
        Top:=10, _
        Width:=200, _
        Height:=200)


'   Hide the border
    Shp.Line.Visible = False


'   Give it a texture
    Shp.Fill.PresetTextured _
       PresetTexture:=msoTextureBlueTissuePaper


'   Some 3D effects
    With Shp.ThreeD
        .Visible = True
        .Depth = 60
        .BevelTopDepth = 10
        .RotationY = 10
    End With

'   And a shadow
    With Shp.Shadow
        .Visible = True
        .Transparency = 0.8
        .OffsetX = 30
        .OffsetY = 30
        .Blur = 12
    End With
End Sub

The final Shape example involves text. The procedure adds a shape, sets the shape’s background, adjusts the border, adds text, and then formats the text — very similar to what happens when you insert a WordArt object. That’s because there’s really nothing special about WordArt. When you use the Insert Text WordArt command, you’re just inserting a shape that has text. Your code can manipulate WordArt just as it can manipulate Shape objects.

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

'   Shape's background color
    Shp.Fill.ForeColor.ObjectThemeColor = msoThemeColorLight2
    Shp.Shadow.Visible = True

'   Shape's border
    Shp.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent2
    Shp.Line.Weight = 6

'   Add text
    Shp.TextFrame2.TextRange.Text = "Microsoft Excel"

'   Format the text
    With Shp.TextFrame2.TextRange.Font
        .Size = 38
        .Bold = True
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2
        .Shadow.Visible = True
    End With
End Sub

Figure 30-13 shows the result of executing the MakeShapeWithText procedure.

Figure 30-13. A Shape object that contains text.

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 learn 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 learning tool of them all: experimentation.