XL: Value Returned by Format Function May be Different

ID: Q173247

The information in this article applies to:

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:

WORKAROUND

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

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

Keywords          : xlvbainfo 
Version           : WINDOWS:5.0,5.0c,7.0,7.0a,97
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: January 8, 1999