Knowledge Base

Value Returned by Format Function May be Different

Article ID: 173247

Article Last Modified on 8/17/2005

Microsoft Excel 97 Standard Edition
Microsoft Excel 95 Standard Edition
Microsoft Excel 5.0 Standard Edition

This article was previously published under Q173247

SYMPTOMS

When you run a Visual Basic for Applications code that uses the Format function to return a value using a specific format, the function may return a slightly different value, depending on what version of Microsoft Excel you are using.

CAUSE

This problem may occur when the following conditions are true:

The format expression specified in the Format function uses fewer decimal places than the value being passed to the function. As a result, the value is rounded by the Format function. -and-
The value being passed to the Format function ends in the digit 5. For example:
1.15
0.075
8.885

WORKAROUND

There are two methods you can use to prevent this problem from occurring:

Use the Round method of the Application object to round the value to the same number of decimal places as the format expression used by the Format function.

For example, instead of this
```
      X = Format(0.075, "$#.##")
						
```
use this:
```
      X = Format(Application.Round(0.075, 2), "$#.##")
						
```
The Round method accepts two arguments; the value to be rounded (in this case, 0.075), and the number of decimal places to which you want the value to be rounded (in this case, 2).
Increase the number of decimal places used by the Format function. For example:
```
      X = Format(0.075, "$#.###")
						
```
Since the value and the format expression both use three decimal places, the problem will not occur.

MORE INFORMATION

You can demonstrate this problem by running Visual Basic code that includes the following line:

   MsgBox Format(0.075, "$#.##")

In Microsoft Excel 5.x, the message box displays the value $.08. In Microsoft Excel 7.x and Microsoft Excel 97, the message box displays the value $.07.

Note that the format expression ("$#.##") in this example uses two decimal places, and that the value being passed to the function (0.075) uses three decimal places. Because of this, the Format function rounds the value to two decimal places before applying the format to the value, and the problem may occur.

If you change the format expression to "$#.###", or if you first round the value (0.075) to two decimal places, the message box displays the same value in all versions of Microsoft Excel. The problem only occurs when the value uses more decimal places than the format expression.

Additional query words: XL97 XL7 XL5 IEEE XL

Keywords: kbprb KB173247