Excel does a very good job of displaying worksheets according to the user's selection of regional settings and language. When displaying data in UserForms or dialog sheets, however, you have to do all the formatting yourself.
As discussed previously, Excel converts numbers and dates to strings according to the WRS by default. This means that you can write code like the following and be safe in the knowledge that Excel will display it correctly:
tbNumber.Text = dNumber
There are two problems with this approach:
□ Dates will get the default ShortDate format, which may not include four digits for the year, and will not include a time component. To force a four-digit year and include a time, use the sFormatDate function shown later in this chapter. It may be better, though, to use a less ambiguous date format on UserForms, such as the "mmm dd, yyyy" format used throughout this book.
□ Versions of Excel prior to Excel 97 did not use the Windows Regional Settings for their default formats. If you are creating applications for use in older versions of Excel, you can't rely on the correct behavior.
The solution is simple—just use the Format function. This tells VBA to convert the number to a locally formatted string and works in all versions of Excel:
tbNumber.Text = Format(dNumber)
Was this article helpful?