Paul_Hossler
01-16-2011, 12:26 PM
Is there a way to get the current Windows Regional Settings for currency, date, etc?
Application.International has the equivalent of the Registry settings for WRS, but I'd like to get the format strings to use directly in a Format statement
I did try the recommended method. While the format looks correct, I wanted to apply a Number Format, and not just get a formatted string ...
?format(123456.789, "currency")
$123,456.79
?format(-123456.789, "currency")
($123,456.79)
If I set WRS = English (UK)
?format(123456.789, "currency")
£123,456.79
?format(-123456.789, "currency")
-£123,456.79
I would really like to use Styles to format the worksheets. That way I can also read the Styles NumberFormatLocal when necessary
As a benefit, a workbook should work (at least as far as formatting is concerned) should work with any WRS.
But the Currency and Comma styles can be deleted or modified, so it seems I can't count on the Style method
I tried using a temporary worksheet and reading how Excel handles WRS for dates and currency. But with UK, the number format string came back as 'General'
Public sFormatCurrency As String
Public sFormatCurrencyNoDecimals As String
Public sFormatDate As String
Public sFormatTime As String
Public sFormatDateTime As String
Public sFormatFixed As String
Public sFormatFixedNoDecimals As String
Sub InitializeInternational()
Dim ws As Worksheet
Dim bSaved As Boolean
bSaved = ActiveWorkbook.Saved
Set ws = WS_Add("##defaults##")
With ws
With .Cells(1, 1)
.Value = Format(-123456.789123, "currency")
sFormatCurrency = .NumberFormatLocal
sFormatCurrencyNoDecimals = sFormatCurrency
End With
With .Cells(1, 2)
.Value = Format(Now, "general date")
sFormatDateTime = .NumberFormatLocal
End With
With .Cells(1, 3)
.Value = Format(DateSerial(2010, 12, 25), "short date")
sFormatDate = .NumberFormatLocal
End With
With .Cells(1, 4)
.Value = Format(TimeSerial(2, 12, 25), "short time")
sFormatTime = .NumberFormatLocal
End With
With .Cells(1, 5)
.Value = Format(-1234.567, "standard")
sFormatFixed = .NumberFormatLocal
sFormatFixedNoDecimals = sFormatFixed
End With
End With
Stop
Call WS_Delete("##defaults##")
ActiveWorkbook.Saved = bSaved
End Sub
So ... the best idea I could come with requires know the WRS strings.
I suppose I could use the Application.International () information to construct the strings, but that REALLY seems like re-inventing the rolling round thing
So .. is there a registry entry or API that will be a reliable of the formatting information??
Paul
Application.International has the equivalent of the Registry settings for WRS, but I'd like to get the format strings to use directly in a Format statement
I did try the recommended method. While the format looks correct, I wanted to apply a Number Format, and not just get a formatted string ...
?format(123456.789, "currency")
$123,456.79
?format(-123456.789, "currency")
($123,456.79)
If I set WRS = English (UK)
?format(123456.789, "currency")
£123,456.79
?format(-123456.789, "currency")
-£123,456.79
I would really like to use Styles to format the worksheets. That way I can also read the Styles NumberFormatLocal when necessary
As a benefit, a workbook should work (at least as far as formatting is concerned) should work with any WRS.
But the Currency and Comma styles can be deleted or modified, so it seems I can't count on the Style method
I tried using a temporary worksheet and reading how Excel handles WRS for dates and currency. But with UK, the number format string came back as 'General'
Public sFormatCurrency As String
Public sFormatCurrencyNoDecimals As String
Public sFormatDate As String
Public sFormatTime As String
Public sFormatDateTime As String
Public sFormatFixed As String
Public sFormatFixedNoDecimals As String
Sub InitializeInternational()
Dim ws As Worksheet
Dim bSaved As Boolean
bSaved = ActiveWorkbook.Saved
Set ws = WS_Add("##defaults##")
With ws
With .Cells(1, 1)
.Value = Format(-123456.789123, "currency")
sFormatCurrency = .NumberFormatLocal
sFormatCurrencyNoDecimals = sFormatCurrency
End With
With .Cells(1, 2)
.Value = Format(Now, "general date")
sFormatDateTime = .NumberFormatLocal
End With
With .Cells(1, 3)
.Value = Format(DateSerial(2010, 12, 25), "short date")
sFormatDate = .NumberFormatLocal
End With
With .Cells(1, 4)
.Value = Format(TimeSerial(2, 12, 25), "short time")
sFormatTime = .NumberFormatLocal
End With
With .Cells(1, 5)
.Value = Format(-1234.567, "standard")
sFormatFixed = .NumberFormatLocal
sFormatFixedNoDecimals = sFormatFixed
End With
End With
Stop
Call WS_Delete("##defaults##")
ActiveWorkbook.Saved = bSaved
End Sub
So ... the best idea I could come with requires know the WRS strings.
I suppose I could use the Application.International () information to construct the strings, but that REALLY seems like re-inventing the rolling round thing
So .. is there a registry entry or API that will be a reliable of the formatting information??
Paul