Comma & Dot Seperators

[andy3816]

I saw the post from Gratu and subsequent response from Hot Pepper on how to query the excel installed to see if the European or American format of comma for thousands seperator and dot for decimal seperator or European format dot for thousands seperator and comma for decimal point seperator are being used, this prompted me to post this question.

Can you use VB to force excel to switch, ie check to see if european is being used then switch to American if true, then run some code and switch back when done.

The reason I need this is that I share a very complex workbook globally and the only way our European friends can get some of code and functions to work is to force their compuers to think they are American!!! by altering their international settings.

Any help would sure help international diplomacy!!!

 

[Andrew Poulsom]

Excel has an International property that contains useful information about the user's country and international settings. Example:

Sub Test()
    Dim Msg As String
    Msg = "Decimal Separator: " & vbTab & vbTab & Application.International(xlDecimalSeparator) & vbCrLf
    Msg = Msg & "Thousands Separator: " & vbTab & Application.International(xlThousandsSeparator) & vbCrLf
    Msg = Msg & "List Separator: " & vbTab & vbTab & Application.International(xlListSeparator)
    MsgBox Msg
End Sub

You can use these in your code in place of hard coded characters, so that it is internationally transportable.

To my mind that's preferable to messing with the user's Control Panel settings.

 

[andy3816]

I can see how that would be preferable but I dont know exactly what it is in my spreadsheet that causes them a problem (and of course cant replicate on my machine) so I really wanted a quick and dirty fix to change their international settings temporarily....any ideas

 

[Andrew Poulsom]

I can see how that would be preferable but I dont know exactly what it is in my spreadsheet that causes them a problem (and of course cant replicate on my machine) so I really wanted a quick and dirty fix to change their international settings temporarily....any ideas

You should be able to replicate the problem on your machine by changing your Regional settings to theirs.

 

[andy3816]

Unfortunately that didnt do it, short of flying to their country and seeing it for myself I cant think of another solution other than my original idea.

I know the inetrnational settings is the problem as when they change it everything works fine, this is happenig in more than one country too

 

[Andrew Poulsom]

Unfortunately that didnt do it, short of flying to their country and seeing it for myself I cant think of another solution other than my original idea.

I know the inetrnational settings is the problem as when they change it everything works fine, this is happenig in more than one country too

If you don't know what international setting is causing the problem, how would you go about changing it, even if you could?

 

[andy3816]

I will just change it to American and then back to whatever it was before after the code has executed and the file closes

 

[Andrew Poulsom]

I will just change it to American and then back to whatever it was before after the code has executed and the file closes

Change what setting to American? If you can't replicate the problem by changing to European, how do you know what setting to change?

 

[andy3816]

im guessing that the problem is with the thousands and decimal point seperators but I dot know for sure, the user changes the country setting in control panel which I am sure changes many settings some of which I probably dont care about

 

[andy3816]

bump