format function for Arithmetic Rounding(Non-Banker's Rounding)

April 2005

The Lesser Known Format Function

Rick Rothstein

VB's Format function doesn't tend to elicit images of speed, but it is a capable workhorse–and a surprisingly flexible one at that. In this article, Rick Rothstein explores some of the Format function's nooks and crannies.

Did you know that the Format function is capable of performing "normal" rounding? No, this isn't a trick question. VB6 uses something known as "Banker's Rounding" both in its Round function and in its various conversion routines. Banker's Rounding rounds numbers ending in five to the nearest even number when rounding to the previous decimal position. For example, 1.2345 rounded to three decimal places is 1.234 according to VB. Back in school, however, most of us learned that 1.2345 should round up to 1.235. I won't go into the theory behind Banker's Rounding; you can read all about it and the other rounding methods employed by Microsoft in its various products in Q196652. (You may be surprised at the lack of consistency.)

Suffice it to say that VB uses Banker's Rounding not only in the Round function itself, but also in the CLng function, CInt function, Mod operator, and just about any other function that handles automatic or coerced rounding. Any other function, that is, except the Format function. Simply feed the Format function your value to be rounded along with the desired format specification string and it will perform normal rounding for you. For example, if you execute the following code:

Value = 1.2345
Print Format(Value, "0.000")

the value 1.235 will print out. The generalized statement for this would be:

Print Format(Value, "0." & String$(Places, "0"))

where Places is the number of decimal places you want to round to. If you want to generalize this all the way, you'll need to add logic to handle the dangling decimal point that occurs when Places equals zero.

Conditionally formatted output

So what else can the Format function do for us? Well, how about returning conditionally formatted values when the first argument is numeric? Most programmers don't realize that they can specify up to four separate sections in the format specification string for numerical value formatting. Let's examine an example with all four sections specified (section delimiters are semicolons) and discuss what's happening. Consider this statement:

Print Format(Value, "+0.00;(0.00);<0>;\N\A\D\A")

If Value is NULL, then the fourth section will be used to format the result; in this case, the string NADA will be printed out. If Value equals zero, then the third section will be used to format the result and, in this case, a zero encased in angle brackets will be printed out. If Value is negative, then the second section's format will be used to format the result and, for this case, the value surrounded by parentheses will be printed out. And, finally, if Value is positive, then the first section will be used to format the result and, in this case, the value will be printed with a leading plus sign.

Table 1 illustrates how VB's Help files document the sections.

Table 1. Deciphering the Format function's sections.

If you use	The result is
One section	The format expression applies to all values.
Two sections	The first section applies to positive values and zeros, the second to negative values.
Three sections	The first section applies to positive values, the second to negative values, and the third to zeros.
Four sections	The first section applies to positive values, the second to negative values, the third to zeros, and the fourth to Null values.

Notice the syntax: Semicolons are used to separate the various sections, and quote marks go around the four sections as a single entity. You shouldn't skip any internal sections, nor should you use spaces to "tidy up" the look of the format specification string, such as around those semicolons. Indeed, if you put a space in the format specification string, it will show up in the resulting return value. A bit of "been there, done that" advice about the "no internal sections should be skipped" rule: If you skip a section (by using two adjacent semicolons), then the above rules will be applied only for that missing section as if no other sections followed it. For example, if we modify our earlier example statement to remove section two:

Print Format(Value, "+0.00;;<0>;\N\U\L\L")

all output will be the same as previously discussed–except if Value is negative. In that case, the first section will be used to format it. That means the numerical value will be printed out with, as it turns out, a -+ in front of it (the plus sign from the formatting of the governing first section with a minus sign placed in front of that because the value is negative). So, be aware of possible side-effects like this when choosing to omit sections.

There's a parallel functionality for String arguments, but it's more limited. A maximum of only two sections can be specified; the first is the format specification string to apply to non-NULL string values and the second is, of course, the return value to use if the value to be formatted is NULL (for example, a non-initialized Variant or a NULL field from a database). "I know, I know," I hear some of you muttering. "String argument, what's he talking about?" Well, that's another little-known functionality of the Format function–it can perform limited string formatting, too.

Two of the formats it can perform have been "replaced" (maybe "complemented" is a better term) by past function additions to the VB language, but, for the sake of completeness, I'll mention them anyway. The Format function can change all characters in a text string to either uppercase or lowercase (this is, of course, what the UCase and LCase functions do for us now) by using either the "greater than" or "less than" symbol as the format specification string. For example, this:

TextString = "abc"
Print Format(TextString, ">")

will print out ABC as its output; and, in a similar fashion, this:

TextString = "ABC"
Print Format(TextString, "<")

will print abc as its output.

Fixed-size fields

The Format function can also return fixed-size fields of data–well, sort of. The implementation of this feature seems somewhat screwy to me under certain circumstances, but if used correctly, it can be useful. Let's take a look at what I'm talking about here. Consider the following piece of code:

TextString = "ABCD"
Print "<" & Right$(Space$(10) & TextString, 10) & ">"

It will output the following:

<>

In other words, it's a method of printing out a right-justified field of data that is, in this example, 10 characters wide (the angle brackets have been included so that you can see the limits of the printed field). Well, as it turns out, Format can (actually, has always been able to) perform this same functionality:

TextString = "ABCD"
Print "<" & Format(TextString, "@@@@@@@@@@") & ">"

The "at" sign (@), when included in a format specification string, supplies positional spaces in the output in the same way the 0 supplies positional zeros in the output for numeric values. So, you might be asking, is there a way to left justify the output in the same way the following code does?

TextString = "ABCD"
Print "<" & Left$(TextString & Space$(10), 10) & ">"

Of course there is; we just need to place an exclamation mark (!) at the beginning of the format specification string in order to produce left-justified text.

TextString = "ABCD"
Print "<" & Format(TextString, "!@@@@@@@@@@") & ">"

If we do this, the following will be printed out:

We can use the String function to generalize this as follows:

TextString = "ABCD"
Places = 10
Print "<" & Format(TextString, _
     String$(Places, "@")) & ">"
Print "<" & Format(TextString, "!" & _
     String$(Places, "@")) & ">"

"Okay," you might be saying to yourself, "this all looks normal enough. Where does the screwiness come in?" If the string to be formatted is longer than the field allocated for it, the two forms of this format specification string (with and without the exclamation mark) act differently. For example, this:

TextString = "ABCDEFGHIJ"
Print "<" & Format(TextString, "@@@@") & ">"

prints out:

as if no format function were being used; that is, the original string of text is returned untouched. However, this example code:

TextString = "ABCDEFGHIJ"
Print "<" & Format(TextString, "!@@@@") & ">"

will truncate the string argument from the right side and print out the following instead!

The lesson to be taken away from this is that if you use this form of the string formatting capabilities of the Format function, make sure your field size is large enough to contain the text you wish to place in it.

Ah, yes

One last item: You can use ampersand characters (&) in place of the "at" signs, but if you do, positional spaces won't be returned. That means the field size will only be preserved when the string argument is longer than the number of ampersand characters and the format specification string contains the exclamation mark at the beginning (which is identical to this same case when "at" signs are used); otherwise, the entire string will be returned untouched. I'm not sure I see a lot of usefulness for this particular option, so I'll place it in the "somewhat screwy" category that I mentioned earlier.

Here are some additional resources that you may find useful:

• Q196652 How To Implement Custom Rounding Procedures
• Q170550 INFO: Format Function and International Settings
• Q217012 How To Format Strings to Right-Justify When Printing
• Q279755 INFO: Visual Basic and Arithmetic Precision
• Q189847 describes new VB6 formatting functions FormatCurrency, FormatDateTime, FormatNumber, and FormatPercent, and also describes the "new" Round function as using "business" rather than arithmetic rounding rules.
• Q28855 CINT/CLNG Integer Assignment Round x.5 to Nearest Even Integer
• Formatting in VB.NET–http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctformat.as