Why are these colors the only ones with constants assigned to them? The reason lies in how colors are created on a computer. You probably encountered the color wheel sometime, probably in school, and you learned that you can combine red, blue, and yellow pigments to create any color you want. That's true for pigments (usually paint or ink), but it's not true when you're working with light. When you want to create colors using light, you operate with these three primary colors: red, green, and blue.

The difference between working with pigments and working with light is captured in the names of the two color systems: subtractive color , which refers to pigments, and additive color , which refers to light. In the subtractive color system, you begin with white (the absence of color ) and, through the use of pigments, 'subtract' colors by blocking them out with your paint. If you mix equal amounts of the primary colors (red, yellow, and blue) you'll get black because you've subtracted all of the colors. In the additive color system, by contrast, you start with black (the absence of light ) and add colors to the mix. In the additive color system, adding full-intensity red, green, and blue light, you get white light.

Just as you can mix differing amounts of paint to produce unique colors (for example, mixing equal amounts of red and yellow makes true orange, whereas putting in more red than yellow makes a red-orange), you can mix differing amounts of light to create distinct colors on your computer. The Microsoft Windows operating system recognizes 256 intensities for each primary color (red, green, and blue). An intensity of 0 means that none of that light is added to the color of a pixel (a dot on your monitor screen), and an intensity of 255 means that the maximum amount of that color is added.

To define a color using a mixture of red, green, and blue light, you use the Visual Basic RGB function, which has this syntax:

RGB(

red

,

green

,

blue

)

In this function, red is the amount of red light to be used, green is the amount of green light to be used, and blue is the amount of blue light to be used. (Any value over 255 is assumed to be 255.) Table 10-2 lists the RGB values for the eight colors assigned to the VBA color constants.

You can use the RGB function to apply a color directly to an element of your worksheet, but there is an important limitation you need to know about in Excel: the program can only display 56 colors at a time, and Excel keeps track of those 56 colors in the Excel color palette. If the color you attempt to assign to the element isn't in the color palette, Excel displays the closest color that is in the palette.

Table 10-3 lists the RGB values of the colors assigned to each of the entries in the standard Excel color palette, along with the name of that color.

Yes, once you get beyond the eight colors that are assigned to Visual Basic constants, the names of the colors reflect the color's position in the palette's index. That decision makes sense, though, once you realize that you as a programmer (or as a user of the main Excel program) can assign new RGB values to any of the slots in the palette.

You can assign a color to a worksheet element by referring to a position in the Excel color palette. To do so, you use the Workbook object's Colors property, which has this syntax:

Workbook.Colors (index)

In the preceding code, index is any number from 1 to 56. If you have a pie chart with divisions for all 100 products sold by your company, you'll get some repeat colors. That's not such a bad thing…the colors should be far enough apart on the chart so that your colleagues won't have any trouble distinguishing the divisions. As an alternative, you can always create a chart in which you display the proportion of sales attributed to the top 50 products (with a single wedge for the other 50 products), and then break out the sales of the remaining products on a separate chart.

Manipulating the Current Excel Color Palette

Realizing that you're limited to using the colors in the color palette for a given workbook, you'd probably be interested in a procedure that lets you display the colors in the current palette. The DisplayPalette procedure does just that.

Sub DisplayPalette()


Range("A1").Select


ActiveCell.Formula = "Color"


ActiveCell.Offset(0, 1).Formula = "Index"


ActiveCell.Offset(1, 0).Activate


For NumColor = 1 To 56


With ActiveCell.Interior


.ColorIndex = NumColor


.Pattern = xlSolid


.PatternColorIndex = xlAutomatic


End With


ActiveCell.Offset(0, 1).Formula = NumColor


ActiveCell.Offset(1, 0).Activate


Next NumColor


End Sub

But what if the colors in the palette aren't what you need for your designs? Many organizations have policies regarding the specific colors used in and appearance of their logos and official documents. Rather than be forced to work with colors that might not be exactly right, you can substitute a custom color for an existing color in the palette.

To assign a new color to a slot in the Excel color palette from within the Excel program, follow these steps:

1. Click Tools, Options, and then click the Color tab in the Options dialog box.

   

2. Click the square of the color you want to replace, and then click Modify. If you see the color you want in the Colors dialog box that appears, click it and then click OK. If you don't see the color you want, click the Custom tab.

3. Verify that RGB is selected in the Color Model list box.

4. Type the red, blue, and green components of your color in the appropriate boxes.

Assigning a color to a position in the color palette takes a single line of code in Excel VBA.

ActiveWorkbook.Colors (index) = RGB (red, green, blue)

But which colors should you substitute? Any you won't use, of course, but there are actually a number of duplicate colors in the standard palette. Why? No clue. But here are the repeats:

• Color 5 (Blue) is repeated by Color 32.

• Color 6 (Yellow) is repeated by Color 27.

• Color 7 (Magenta) is repeated by Color 26.

• Color 8 (Cyan) is repeated by Color 28.

• Color 9 is repeated by Color 30.

• Color 13 is repeated by Color 29.

• Color 14 is repeated by Color 31.

• Color 18 is repeated by Color 54.

• Color 20 is repeated by Color 34.

You should probably replace the higher-numbered color first, especially if you're replacing a color that is named by one of the VBA color constants. The following procedure adds a new set of colors to the active workbook's palette using colors 26, 27, 28, 29, and 30:

Sub CustomColors()


ActiveWorkbook.Colors(26) = RGB(240, 248, 255)


ActiveWorkbook.Colors(27) = RGB(138, 43, 226)


ActiveWorkbook.Colors(28) = RGB(165, 42, 42)


ActiveWorkbook.Colors(29) = RGB(255, 250, 205)


ActiveWorkbook.Colors(30) = RGB(199, 21, 133)


End Sub

Now when you run the DisplayPalette procedure listed earlier in this chapter, you will see your new colors in positions 26, 27, 28, 29, and 30.

Changing the color palette of a workbook is relatively straightforward, but doing it for every workbook you create can be a pain if you try to do it by hand. However, you can write a short macro that copies the color palette from another workbook to the active workbook. Aside from the standard Sub and End Sub statements, you use the Workbooks collection's Colors property to copy the color palette from a workbook to the active workbook. If the workbook with the desired palette were named OurColors.xls, you would use the following procedure to copy the color palette over:

Sub GetOurColors()


ActiveWorkbook.Colors = Workbooks("OurColors.xls").Colors


End Sub

To copy a color palette from another workbook using the Excel interface, follow these steps:

1. Open the workbook from which you want to copy the color palette.

2. In the workbook to which you want to copy the color palette, click Tools, Options.

3. Click the Color tab in the Options dialog box.

4. Click the Copy Colors From down arrow, and then click the name of the workbook from which you want to copy the color palette.

If you've changed the default Excel color palette, you can change it back to the default by calling the Workbook object's ResetColors method, as in the following procedure:

Sub NormalColors()


ActiveWorkbook.ResetColors


End Sub

To change the color palette back to the default using the Excel interface, follow these steps:

1. Click Tools, Options.

2. Click the Color tab in the Options dialog box.

3. Click Reset.

Using the Excel Color Palette on the Web

If you've used the Web a lot, you've probably found that some pages that appear perfectly normal in one browser are a mess in another browser. Whether the spacing is off, the text is larger in some paragraphs than in others, or the colors are different, you just don't get what you expect. Part of the difficulty can be traced to older Web browsers that don't understand the newer markup tags, but another reason is that some programs, such as Microsoft FrontPage, tend to create code that is interpreted perfectly by Microsoft Internet Explorer but less well by other browsers. Colors are no different from other HTML formatting instructions, so you need to know what can cause the errors and how to avoid them.

Beginning with Internet Explorer 4.0, there has been a list of named colors with associated RGB color values that the browser knows how to interpret. Not all browsers know how to interpret the names of the colors, but every major browser is able to interpret RGB color values. Table 10-4 lists the standard HTML colors and their associated RGB values so that you can add them to your Excel color palette if you want to create a workbook that looks good both in print and on the Web.

For more information on how these colors will look on your monitor, see the full-color table on MSDN, the Microsoft Developer Network site, at: http://msdn.microsoft.com/workshop/author/dhtml/reference/colors/colors.asp .

For more information on how to publish Excel worksheets and workbooks to the Web, see Chapter 25, 'Excel and the Web.'

Some of the work of matching colors from the Excel palette to the Web has been done for you. Table 10-5 lists the colors in the Excel palette that closely correspond to the standard HTML colors.