INFO: Format Function and International Settings

• Microsoft Visual Basic Control Creation, Learning, Professional, and Enterprise Editions for Windows, version 5.0 on the following platforms: NT, Win95
• Microsoft Visual Basic Standard, Professional, and Enterprise Editions, 16-bit and 32-bit, for Windows, version 4.0
• Microsoft Access versions 1.1, 2.0, 7.0, 97
• Microsoft Excel for Windows, version 5.0
• Microsoft Excel 97 for Windows
• Microsoft Word 97 for Windows
• Microsoft Excel for Windows 95, version 7.0

SUMMARY

The Format function does not always return the expected number format with certain international settings. This article explains this behavior and describes how the Format function interprets its parameters.

MORE INFORMATION

A common use of the Format function is to format numbers for a consistent display. To do this, two parameters are passed to Format. The first parameter is any valid numeric expression, such as 5555.5, "5555.5," etc. The second parameter is a format expression, such as "###,###.00."

The key point of emphasis is this: if the first parameter is a string representation of a number, such as "5555.5," Format expects it to be in the proper regional setting as portrayed in Control Panel / Regional Settings. However, Format always expects the second parameter to be in the "English (United States)" setting.

For example, if the regional setting is such that the decimal symbol is "," and the digit grouping symbol is ".", the following will occur:

   Format(5555.5, "###,###.00") returns "5.555,50"
   Format("5555,5", "###,###.00") returns "5.555,50"
   Format("5.555,5", "###,###.00") returns "5.555,50"

   Format("5555.5", "###,###.00") returns "55.555,00"
   Format("5,555.5", "###,###.00") returns "5,56"

Keywords          : VB4ALL VB4WIN vb5all VBKBVBA vbwin GnrlVb kbprg
Technology        : kbvba
Version           : WINDOWS:1.1 2.0 4.0 5.0 7.0 97
Platform          : NT Win95 WINDOWS
Issue type        : kbinfo