7.6.3 Formatting values | Microsoft Office Automation with Visual FoxPro

Formatting values

Excel offers a feature to format numbers that acts like FoxPro s InputMask property on steroids. It s found on the Format Cells dialog, pictured in Figure 7. There are 12 categories, with each category having as many as 31 predefined codes. Plus, if you can t find one that meets your needs, you can define your own.

You re probably expecting to see a long list of properties with a long list of constants representing each number format. Nope, not this time. There s only one property, and that s the Range s NumberFormat property. It gets set to a string representing the format, much like the way VFP s InputMask property works. You can see some sample strings if you select the Custom category on the Number tab in the Format Cells dialog box (see Figure 7).

The most commonly used codes for format strings are shown in Table 2. As in VFP s InputMask codes, the pound sign (#) displays a number, and a comma puts in a thousands separator. Many symbols, such as %, $, and (the Euro symbol), are literals; you still need to put in placeholders to display the numbers. Codes that pertain to the same data type can be mixed and matched to precisely set the formatting of a cell.

Figure 7. The Number tab on the Format Cells dialog in Excel. Like FoxPro s InputMask property on steroids, Excel offers seemingly unlimited ways to format the way the data looks.

Table 2. Commonly used codes for formatting numbers. There are many more available; see the Help topic "About number formats" in the regular Excel Help file.

Code	Description	Example value	Example string	Example output
"General"	Resets to the default format.	1234.5	"General"	1234.5
#	Displays a number (blank if a leading or trailing 0).	1234.5	"#####.#"	1234.5
0	Displays a number, including leading or trailing 0 s.	1234.5	"00000.00"	01234.50
,	Adds a Thousands separator.	1234.5	"##,###.##"	1,234.5
%	Displays numbers as a percentage.	.08	"##%"	8%
$	Inserts the dollar sign.	1.25	"$##.00"	$1.25
	Inserts the Euro symbol.	3.00	CHR(128) + "##.00"	3.00
M	Displays the month as a number from 1 12.	10/22/99	"M"	10
Mmm	Displays the month as a three-character abbreviation.	10/22/99	"Mmm"	Oct
D	Displays the day as a number from 1 31.	10/22/99	"D"	22
Ddd	Displays the day as a three-character day of week.	10/22/99	"Ddd"	Fri
Yy	Displays a two-digit year.	10/22/99	"Yy"	99

Let s look at some numeric examples. These examples assume you have a blank sheet, referenced by oSheet. The results of all the numeric code examples are shown in Figure 8, at the end of the numeric examples. For our first example, let s format 1234.5 to appear as 1,234.50:

oSheet.Range("A1").Value = "Format a Cell"

oSheet.Columns[1].ColumnWidth = 15

oSheet.Range("A2").Value = 1234.5

oSheet.Range("A2").NumberFormat = "##,###.#0"

If it should be currency, with a thousands separator like $1,234.50 try the following NumberFormat:

oSheet.Range("A3").Value = 1234.5

oSheet.Range("A3").NumberFormat = "$##,###.#0"

For those of you who use different currencies, use the ASCII code representing your currency symbol: is CHR(128), is CHR(163), and is CHR(162).

Excel allows four sections of codes, sort of like an inline case statement, which are: positive numbers, negative numbers, zero values, and text. Specify a string for each section, separated by a semi-colon. Here s an example of how to format negative numbers with parentheses in lieu of a minus sign, using column B:

oSheet.Range("B1").Value = "Format +/-"

oSheet.Columns[2].ColumnWidth = 15

oSheet.Range("B2").Value = 1234.5

oSheet.Range("B3").Value = 0

oSheet.Range("B4").Value = -1234.5

oSheet.Range("B2:B4").NumberFormat = "##,###.#0;(##,###.#0)"

Cell B2 displays 1,234.50, and B4 displays (1,234.50). One of the issues we ve encountered a lot is how to handle zeroes in this case, the zero displays as ".0," but in many cases, we want the cell left blank. Using the same sequence of numbers, this time in column C, try this:

oSheet.Range("C1").Value = "Format +/-/0"

oSheet.Columns[3].ColumnWidth = 15

oSheet.Range("C2").Value = 1234.5

oSheet.Range("C3").Value = 0

oSheet.Range("C4").Value = -1234.5

oSheet.Range("C2:C4").NumberFormat = "##,###.#0;(##,###.#0);;"

If you look at Figure 8, you ll see the results of leaving the third section of the NumberFormat string blank: nothing displays for zeroes. What if you want to set a zero-value cell to show "N/A"? Not hard at all. Change the format string to this:

[##,###.#0;(##,###.#0); "N/A";]

Beware: Excel needs literal strings delimited in double quotes only, so be sure to delimit the whole FoxPro string with square brackets or single quotes. Should you forget, you ll get an error indicating that Excel is "unable to set the NumberFormat property."

The fourth section of NumberFormat allows you to format text cells. Text formats a bit differently; the entire value of the cell s text string is denoted by the @ sign, and you can place literal text (delimited by double quotes only) on either side of the @ sign. Why would you want to add to text? Perhaps your data holds only the client name, and it should say "Client Totals." Here s an example that includes formatting the text (see Figure 8 for the results):

* Enter some data

oSheet.Range("A6").Value = "January"

oSheet.Range("A7").Value = 102

oSheet.Range("B7").Value = "Widget"

oSheet.Range("A8").Value = -24

oSheet.Range("B8").Value = "Whatzit"

oSheet.Range("A9").Value = 0

oSheet.Range("B9").Value = "Whatnot"

oSheet.Range("A10").Value = "February"

oSheet.Range("A11").Value = 123

oSheet.Range("B11").Value = "Widget"

oSheet.Range("A12").Value = 0

oSheet.Range("B12").Value = "Whatzit"

oSheet.Range("A13").Value = 332

oSheet.Range("B13").Value = "Whatnot"

* Format the first column, using all four sections.

oSheet.Range("A6:A13").NumberFormat = [$###;($###);"No"; @" Totals"]

* Format the second column for text only. Literal

* strings are placed on both sides of the cell's actual value.

oSheet.Range("B6:B13").NumberFormat = ["Total "@" Sales"]

Figure 8. The combined results of the numeric formatting examples. Cell A2 shows basic formatting; A3 shows currency; B2:B4 format positive and negative numbers differently; and C2:C4 differentiate positive, zero, and negative values. A6:B13 show the use of all four formatting sections in column A, and column B s text is formatted, too.

Dates also have a number of formats. By using "D" for day, "M" for month, and "Yy" for year, along with any literals, you can conjure up nearly any format. When D and M are used, the day and month are displayed as a number, without a leading zero. Use Dd or Mm to provide a leading zero. Three characters, as in Ddd or Mmm, provide a three-character abbreviation, as in "Fri" or "Jan." Four characters, as in Dddd or Mmmm, display the full name, as in "Friday" or "January." A string of Yyyy gives a Y2K-compliant, four-digit year. If you want a military format date, try the following:

oSheet.Range("A1").Value = "01/01/2000"

oSheet.Range("A1").NumberFormat = "Dd-Mmm-Yyyy"

The cell displays "01-Jan-2000." What if you wanted it to read, "Saturday, January 1, 2000"? Change the format string to "Dddd, Mmmm D, Yyyy" to display the desired format.

There are many combinations and permutations for formatting cells. Rather than boring you for the next several pages by attempting to explain every detail, we ll just point you to the Excel Help file (the regular Excel Help, not the VBA Help), which covers this in detail. Search for the topic "About number formats," which will explain everything you ve ever wanted to know about NumberFormat strings.

Borders

The Borders collection contains Border objects, with each object representing the eight borders of the cell. This collection wouldn t be complete without a series of constants describing the eight different borders: xlDiagonalDown (5), xlDiagonalUp (6), xlEdgeBottom (9), xlEdgeLeft (7), xlEdgeRight (10), xlEdgeTop (8), xlInsideHorizontal (12), and xlInsideVertical (11). The xlEdge borders represent the outer perimeter of a range of cells. The xlInside borders are those that are on the interior of a multi-cell range. If the range is a single cell, the xlInside borders are ignored.

Table 3 shows the properties for each border. The LineStyle and Weight properties are set through constant values.

Table 3. Border properties. Borders can be placed around any range, and their appearance can be altered with these properties.

Property	Type	Description
Color	RGB Color	The color of the border.
LineStyle	Numeric	The numeric value corresponding to a preset line style. xlContinuous 1 xlDot -4118 xlDash -4115 xlDouble -4119 xlDashDot 4 xlLineStyleNone -4142 xlDashDotDot 5 xlSlantDashDot 13
Weight	Numeric	The width of the line. This uses constants, not points. xlHairline 1 xlMedium -4138 xlThin 2 xlThick 4

If you want to put a dotted border on the bottom of a range of cells, use this code:

#DEFINE xlEdgeBottom 9

#DEFINE xlDot -4118

oSheet.Range("A2:C2").Borders(xlEdgeBottom).LineStyle = xlDot

There are a few situations we ve found with this process. For the most part, you can set any LineStyle you want. When you set the width, the results become unpredictable, usually resulting in a continuous line, and occasionally in a random color. For example, the following code produces a thick solid line, rather than a thick dash-dot line:

#DEFINE xlEdgeBottom 9

#DEFINE xlDashDot 4

#DEFINE xlThick 4

oSheet.Range("A2:C2").Borders(xlEdgeBottom).LineStyle = xlDashDot

oSheet.Range("A2:C2").Borders(xlEdgeBottom).Weight = xlThick

What seems to work is any Weight with a continuous line, or any LineStyle without setting the Weight property. In VFP, setting the BorderWidth property to anything greater than 1 implies a single-line border. What we ve found with Excel s borders may parallel VFP s design. We ve not found any documentation one way or the other on Excel s design.