XL: Value Returned by Format Function May be Different

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c

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.

STATUS

This behavior is by design in Microsoft Excel.

MORE INFORMATION

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

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

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.