Note: this sheet is available in the Startup File in our store.
Color Selection
Aren’t colors wonderful? They are simple and obvious, yet complex and mysterious. Most users interact with them visually with no thought to the numbers behind them, and most only ever use a few, or several colors, picked for contrast or emphasis. Excel can display over 16 million colors! This needs some breakdown and some VBA examples on different ways to set colors.
The user interface simplifies color options, stepping in with four stages of increasing complexity. Here I have ordered them and given each a catchy name.
VBA Color Numbers
Excel colors are visual, VBA is not. Color numbers are used in VBA to read and write Excel colors. There is a decimal value of each color, from 0 to 16777215, that is only used in VBA. But who wants to derive or interpret 16 million color numbers? Like Excel, VBA has simpler ways to select colors and know what you’re getting.
When you record a macro while setting a color, you get the property statement like .Color = 256, or, if you select from the theme color palette, something like .ThemeColor = xlThemeColorAccent1. Within VBA there are other ways to select color that are not recorded. This means you have to know what they are, but once you do they are a great convenience.
The simplest selection, like the crayon box, is a set of eight color constants: vbBlack, vbBlue, vbGreen, vbCyan, vbRed, vbMagenta, vbYellow, vbWhite. If your needs are that simple, there’s little to memorize. In the VBA Object Browser (F2) you can see the list and their decimal and hex values.
Next up the scale is .ColorIndex, with 56 values. This is a default palette that can be customized at workbook level. One way to see them is to create a list or grid from 1 to 56 and write a macro to set .Interior.ColorIndex = .Value.
Finally, the RGB function can express any color as a combination of red, green and blue. It’s a lot easier to envision a scale of 256 values for each of three colors than a scale of 16 million. You’ll learn that RGB(255,0,0) is red, RGB(0,0,255) is blue, and RGB with three equal numbers is a shade of gray. RGB values are visible in the Excel custom color picker, but are not recorded.
You will also see or read about a hex value. Since hex is base 16, a two digit hex number, 00 to FF, can represent decimal numbers from 0 to 255 (=16^2). Thus an RGB color value can be expressed as a concatenation of three two-digit hex numbers, like # RRGGBB, from # 000000 for black to # FFFFFF for white.
Curiously, the decimal value used in VBA is in BGR order of significance, while the hex value is in RGB significance order. In Excel and VBA, you do not need to be concerned with hex values. In Excel you select visually, or by entering RGB values in the custom color dialog. In VBA you record and set the decimal, or use one of the methods above to select by name, index or RGB. Hex codes are used in web design and may come into play in other programs.
If you’re uneasy about the reversed significance, do an exercise to clarify this for yourself. Record a macro while setting three cells to true red, green and blue. Then, open the custom color picker on each cell, and compare the hex value there to the recorded decimal value in VBA.
You would expect that the color value would be the same number, whether it’s expressed in base 10 or base 16, but when the decimals are converted to hex they are the reverse of the hex color value.
This odd logic is a feature. Just know that if you ever have need to use hex color codes, they are in RGB order. The VBA decimal is the one that’s out of order. This is not a concern because the decimal is not meaningful in itself, we use the methods noted to give it meaning.
Sheet Interface
The VBA methods to select colors by name or number help in development, but don’t give you any visual preview of the colors. This needs to be laid out and exposed. I want a reference showing the various color selection methods for both user and programmer. I want to see fill and font colors together, for best readability. And, I want some code samples that I can copy to other projects as needed.
To keep it handy and compact within Startup, I’ll use column groups on a single sheet with tab-like controls to simulate sheets-within-a-sheet. This is another prototype technique as an example of how user interface can be managed.
Column A is a menu of sections. The entire column is shaded darker than the rest of the sheet. When a menu item is selected, it activates code via the Worksheet_SelectionChange event, which is limited only to the menu range. Each section is in a named column group, and the event will use a Select Case group on the menu items to do three things:
Highlight the selected cell, and reset the rest of the menu
Display the related column group, and hide the rest of the columns
Display any related controls and hide all others
In each step, the first action is to hide or reset everything, followed by the specific action to unhide or highlight the selected element. Thus, it appears that each menu item is opening a different page.
Sheet Sections
The menu has seven sections, each focused on a certain scope or technique to envision, maintain and program colors, as summarized in the table.
Each section has a helpful table with color name if available, decimal, hex and RGB values, and reference to other color selection methods. Those with controls have a copy of the VBA code on the sheet. The sheet is thus a comprehensive reference for color selection, color values, and color coding.
コメント