Value Returned by Format Function May be Different (173247)
The information in this article applies to:
- Microsoft Excel 97 for Windows
- Microsoft Excel for Windows 95
- Microsoft Excel for Windows 5.0
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:
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.
Modification Type: | Minor | Last Reviewed: | 8/17/2005 |
---|
Keywords: | kbprb KB173247 |
---|
|