How to get International formats?

[Paul_Hossler]

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

[Simon Lloyd]

Can you work with the language id?
Application.LanguageSettings.LanguageID(msoLanguageIDUI)or work with the country code?Application.International(xlCountryCode)

[Simon Lloyd]

Just had a little "Google" and found that Ron de Bruin has already addressed these things here http://www.rondebruin.nl/international.htm :)

[Paul_Hossler]

Hi Simon -- thanks for the reply(s)

I can find the Country using .International (the code is usually the international telephone code; 1 = US, 44 = UK), but that doesn't really get the Format( ) formatting string. The details are all there in .International, but I don't want to build my own strings if it's already done else where

I have Ron de Bruin bookmarked since it's an invaluable resource, but he only gives a few examples of using return values from .International

If you look at my #1, I was hoping to find an API or registry entry that would return (for WRS = UK)


sFormatCurrency = some magic call

so that


sFormatCurrency = "£#,##0.00;-£#,##0.00;"




Paul

[Simon Lloyd]

Maybe this suits your nees then http://www.mrexcel.com/forum/showthread.php?t=41533

[Artik]

Paul,
If you want to enter values in the cells, you must specify the value in code using the conversion functions, such as CDbl, CCur, etc., or functions that forcing data format (Now, DateSerial, TimeSerial)

Format function (see the vba help) "returns a Variant (String) containing an expression formatted according to instructions contained in a format expression", so you receive text value in the cells.
Look at this code: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 = Worksheets.Add
ws.Name = "##defaults##"

With ws
With .Cells(1, 1)
' .Value = Format(-123456.789123, "currency")
.Value = CCur(-123456.789123)
sFormatCurrency = .NumberFormatLocal
' sFormatCurrencyNoDecimals = sFormatCurrency
.Offset(1).Value = sFormatCurrency
End With

With .Cells(1, 2)
' .Value = Format(Now, "general date")
.Value = Now()
sFormatDateTime = .NumberFormatLocal
.Offset(1).Value = sFormatDateTime
End With

With .Cells(1, 3)
' .Value = Format(DateSerial(2010, 12, 25), "short date")
.Value = DateSerial(2010, 12, 25)
sFormatDate = .NumberFormatLocal
.Offset(1).Value = sFormatDate
End With


With .Cells(1, 4)
' .Value = Format(TimeSerial(2, 12, 25), "short time")
.Value = TimeSerial(2, 12, 25)
sFormatTime = .NumberFormatLocal
.Offset(1).Value = sFormatTime
End With

With .Cells(1, 5)
' .Value = Format(-1234.567, "standard")
.Value = -1234.567
sFormatFixed = .NumberFormatLocal
' sFormatFixedNoDecimals = sFormatFixed
.Offset(1).Value = sFormatFixed
End With


.UsedRange.Columns.AutoFit
End With

Stop
ws.Delete

ActiveWorkbook.Saved = bSaved
End Sub I lack words to describe everything (I use a translator), but generally saying, WRS is sculpting in clay.:)

About registry and API, look also at it:
http://www.mrexcel.com/forum/showthread.php?t=51271

http://books.google.pl/books?id=guRpLndAuu8C&pg=PT220&lpg=PT220&dq=vba+International+formats+registry&source=bl&ots=pPsYqK4v1Z&sig=iIbIruEdDn69pe73Tk5hNGtbik4&hl=pl&ei=5Mk0Ta6uDtGSswaDpZ2yCg&sa=X&oi=book_result&ct=result&resnum=3&ved=0CCoQ6AEwAg#v=onepage&q&f=false

Artik

[kvnfrnk]

I haven't run your code, just scanned it...
First potential Problem: You are checking UseSystemSeparators and then setting the DecimalSeparaor and ThousandsSeparator.
if (app.UseSystemSeparators == false)
{
app.DecimalSeparator = ci.NumberFormat.NumberDecimalSeparator;
app.ThousandsSeparator = ci.NumberFormat.NumberGroupSeparator;
}
Then right after, you are turning on SystemSeparators, so the code above is not doing anything since you are turning the system seperators on.
app.UseSystemSeparators = true;
Second potential problem/suggestion: When setting the cell value, set it as a double value instead of string, let Excel format the number for you.

[Paul_Hossler]

Artik

1. Good point about Format returning a string. I was trying to have Excel provide the string for NumberFormatLocal, but I think I see why that won't do what I want.

2. Your CCur ( ) was a good one. So far, that seems the most reliable way. It seems to work a little cleaner if I Dim the variable as a Currency to start with.

3. Problem is if I use VBA to assign A1.Value = MyCurrVar, and then change the WRS, the previous currency format for A1 remains, although newly entered data in B1 is formatted in the new WRS selection

4. Applying the Currency Style to the data works, reliably except

a. The Currency style can be deleted by the user (but I can detect that and re-create it, but ...)

b. In order to re-create the style I need to know the NumberFormatLocal

Paul

[Artik]

Sorry, but I'm not able to explain the intricacies of WRS (missing me words in the translator) :dunno

Localization problems are fairly well described in this book (http://www.amazon.com/gp/product/0470046430/ref=pd_lpo_k2_dp_sr_3?pf_rd_p=1278548962&pf_rd_s=lpo-top-stripe-1&pf_rd_t=201&pf_rd_i=0764543717&pf_rd_m=ATVPDKIKX0DER&pf_rd_r=1Q58605M9QZTG5E14Q37) (Chapter 25).

Artik

[Paul_Hossler]

Artik -- thanks for pointing me to additional resources

Paul