Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q214372

↑ Back to the top


Symptoms

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.

↑ Back to the top


Cause

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

↑ Back to the top


Workaround

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.

↑ Back to the top


More information

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:
Sub Test()
MsgBox Format(0.075, "$#.##")
End Sub
				
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.

↑ Back to the top


Keywords: KB214372, kbprb

↑ Back to the top

Article Info
Article ID : 214372
Revision : 8
Created on : 11/23/2006
Published on : 11/23/2006
Exists online : False
Views : 321