XL2000: Value Returned by Format Function May Vary Between Versions of Excel

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

This behavior 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
  

There are two methods that you can use to prevent this behavior 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 using this method
  X = Format(0.075, "$#.##")
  use this method:
  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, "$#.###")
  Because the value and the format expression both use three decimal places, the behavior does not occur.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: You can demonstrate this behavior by running Visual Basic code that includes the following line: In Microsoft Excel 2000 and Excel 5.x, the message box displays the value $.08. In Excel 7.x and 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 behavior 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 behavior occurs only when the value uses more decimal places than the format expression uses.