Microsoft KB Archive/173247
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
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.
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:
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, "$#.##")
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.
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