| Downloads |
|---|
| 47800.zip |
Little in this world is black and white, so why should your VBScript output reports be? Until recently, almost all my VBScript scripts created reports that were the standard black text on a white background. Then I learned how easy it is to spice up these reports with a little color. Now, I'll show you how.
Before I start, though, let me say that there's a practical side to coloring your VBScript world. Let's say you're writing a script to produce a spreadsheet that exposes Administrator accounts with non-expiring passwords or you're comparing this week's administrator IDs in the Domain Admins group with those in last week's report. In these cases, you'd probably want to highlight the data that needs immediate attention. Or suppose you're using a VBScript script to produce an inventory report that upper management has requested about specific servers. Adding a colored title will make that report more pronounced.
Figure 1 shows an HTML Application (HTA) that lets you click buttons to run demos showing how you can add color to not only VBScript reports but also HTA reports. This HTA also provides a way for you to enter color values and see the results. You'll find the code for the HTA in the AddColor.hta file, which you can download from the Windows Scripting Solutions Web site. Go to http://www.windowsitpro.com/windowsscripting, enter 47800.zip in the InstantDoc ID text box, then click the 47800.zip hotlink.
In this article, I won't go into the specifics about how to use or create HTAs for your VBScript scripts. I've already covered that subject in the articles "Hooked on HTAs" (August 2005, InstantDoc ID 46795), "Handy HTAs: Set and Display User Quotas" (September 2005, InstantDoc ID 47101), and "Use an HTA as a UI for Your VBScript Scripts" (October 2005, InstantDoc ID 47533). Instead, I'll show you how to use VBScript's RGB function to produce color in VBScript reports. This function returns a whole number representing a red-green-blue (RGB) color combination. Although you can use hexadecimal values or Microsoft predefined constants to add color to HTA reports, you must use the RGB function in VBScript code.
The RGB function requires three parameters, each of which must be a number between 0 and 255. The function's syntax is
RGB(r,g,b)
where r represents the red value, g represents the green value, and b represents the blue value. For example, pure red would be RGB(255,0,0) and pure blue would be RGB(0,0,255). Green, however, isn't RGB(0,255,0); it's RGB(0,128,0). RGB(0,255,0) is lime.
At the two ends of the spectrum are black and white. Black translates to RGB(0,0,0). You can think of it as the absence of color and therefore all zeros. White is the complete opposite and is represented as RGB(255,255,255).
You might think you can logically mix two or more colors to get the color you want, but the mixture might not turn out as you expected. For example, if you mix pure red and pure blue—or RGB(255,0,255)—you don't get purple. You get magenta instead. Purple is a combination of a lighter red and blue: RGB(128,0,128). The primary color yellow is RGB(255,255,0)—a combination of pure red and pure green. Orange is RGB(255,165,0), and brown is RGB(165,42,42).
As you can see, you'll probably want to use some type of chart or reference guide that provides the RGB values rather than trying to guess at the color combinations. The HTA in Figure 1 provides you with six tools for this purpose: the Rainbow Chart, Row Color Demo, Interior Color Demo, Show Color In Background demo, RGB2Hex demo, and Hex2RGB demo.
The Rainbow Chart
The first tool in the HTA in Figure 1 uses Microsoft Word to produce a rainbow chart for you to use. This chart consists of different colored cells. Inside each cell is the RGB value that creates the color.
Listing 1 shows the rgbchart subroutine that creates the rainbow chart. This subroutine uses the Word.Application object to launch Word, then uses the Add method of the Documents collection object to create a new document. In that document, the rgbchart subroutine creates a table. As the code at callout A in Listing 1 shows, the subroutine uses a For...Next statement with the Step clause to write an RGB value in each table cell and the BackgroundPatternColor property to color that cell.
In the Step clause, I use the step value of -51 because it's divisible into 255, which is the maximum value for color codes. Using this step value produces six color bands per line, with various color combinations of 0, 51, 102,153, 204, and 255. I experimented with a lot of step increments and this increment produced the nicest sample. You can, however, change the step value to any value you like, but you probably don't want to make it too small. The demo would take too long to run and the color shades wouldn't be pronounced.
If you're unfamiliar with how to automate Word, see the article "Creating Word Documents Programmatically" (December 2002, InstantDoc ID 26972) or the Microsoft article "Automating Word Tables for Data Insertion and Extraction" (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_wd2003_ta/html/officewordautomatingtablesdata.asp). As the Microsoft article notes, the table functionality in Word has changed in each version since Word 97. I wrote this HTA for use with Word 2003; it might not work properly with other Word versions because of the differences in functionality.
The Row Color and Interior Color Demos
The Row Color Demo illustrates how you can change the color of a row's contents in a Microsoft Excel spreadsheet. The rowcolor subroutine provides this functionality. I often use this subroutine to add color to the title of an Excel report or to highlight important data.
The rowcolor subroutine begins by using the Excel.Application object to launch Excel and the Workbooks collection object's Add method to create a new spreadsheet. In the spreadsheet, the subroutine writes the string Change Row Font Color to Red in cell A1 and the code statement XL.Rows("1:1").Font.Color = RGB(255,0,0) in cell A2. Then, the subroutine executes that code statement to change row 1's font color to red. The subroutine uses the Font object's Color property to change the color of the row's contents. This process repeats twice to make row 2's font color green and row 3's font color blue.
The Interior Color Demo is similar to the Row Color Demo, except that the Interior Color Demo also changes the background color of cells in an Excel spreadsheet. The interiorcolor subroutine provides this functionality, which you'd probably use for something you want to really stick out, such as headers. To change the background color, the subroutine uses the Color property of the Interior object.
If you're unfamiliar with how to automate Excel, check out "Produce Pivot Tables Programmatically" (April 2005, InstantDoc ID 45502) and "Excel and Outlook Automation Resources" (September 2001, InstantDoc ID 22145). Note that I wrote the Row Color and Interior Color Demos for use with Excel 2003.
Show Color In Background Demo
The Show Color In Background demo provides you with a means to test various RGB combinations to see what color they produce. Be advised that if you enter the color combination for a very dark color, such as black, you won't be able to see the RGB value you typed after you click the Show Color In Background button. However, if you hold down the left mouse button and sweep over that value as if you were highlighting it for copying purposes, the value will appear.
The changecolor subroutine contains the code for the Show Color In Background demo. This subroutine changes the background color by setting the BackgroundColor property of the ColorBackground.Style object equal to the RGB color entered in the RGB or Hex Value text box. In the code, ColorBackground.Value represents this value.
The RGB2Hex and Hex2RGB Demos
The RGB2Hex demo converts an RGB value to a hexadecimal value. The rgb2hex subroutine contains the code for this demo. This code uses VBScript's CLng function to convert a value that's a Decimal data type to a value that's a Long data type. Then, the code uses VBScript's Hex function to convert the Long value into a value that's a Hex data type.
The Hex2RGB demo does the opposite of the RGB2Hex demo. The Hex2RGB demo converts a hex value to an RGB value.
An Important Note
There is an important caveat I need to share with you about adding color to your VBScript reports. I found you can pretty much use any RGB value in Word documents, but in spreadsheets, Excel can display only 46 colors. I've listed these colors in ExcelRGB.xls, which you can find in the 47800.zip file. If you specify a color that Excel doesn't recognize, Excel will simply produce another color. In other words, your VBScript code won't err out if a particular color can't be produced.
In 47800.zip, you'll also find RGB-Hex-Color-Codes.txt. This file lists the RGB values, hex values, and Microsoft constant names of commonly used colors. Remember that you can use hex values and constant names in the HTML code in your HTAs but not in the VBScript code that creates Excel or Word reports. With that said, I hope you have fun coloring your HTAs and VBScript reports.
