Temporarily modify international options

[Pierre Archambault]

Hi,

Is there anybody that could help me with this issue ?

I am in Montreal (Quebec) and I always use the period (.) as decimal symbol
and the comma (,) as thousands delimiter.

I have to develop Excel applications for any kind of environment, and I
often have to make them change their regional settings to let the program
run correctly. Some are like me, american style but others use the french
options (comma for decimals and space for thousands).

How can I access (in VBA) the user's regional settings, read them, change
them for the time the program is running and return them to their original
state when the application ends ?

My environment (and client's) :
Windows XP and Office 2003

Thank you very much.

Pierre

 

[Greg Wilson]

To query the regional settings, look up XlApplicationInternational in the
Object Browser. For example:
MsgBox Application.International(xlDecimalSeparator)

I can't say for sure and haven't ever done it, but IMO to programmatically
change regional settings would likley require writing to the registry. For a
list of API candidates see http://www.allapi.net/apilist/r.shtml.
Suggest you look at:
RegOpenKey
RegCloseKey
RegSetValueEx
RegCreateKey
RegQuerryValue

The path to the regional settings appears to be:
HKEY_CURRENT_USER > Control Panel > International

* AS YOU ARE AWARE MESSING WITH THE REGISTRY CAN BE VERY RISKY! *

Do you really need to change the settings instead of structuring your code
to handle it? Perhaps setting variables to return values instead of hard
coding them:

s = Application.International(xlListSeparator)

Regards,
Greg

 

[NickHK]

Pierre,
Check the recent posts in this NG from "Alex St-Pierre". He's involved in
something similar.

NickHK

 

[Tom Ogilvy]

Sounds like everyone wants to help you to do this - however, I can't think
of a single application I have ever used that changes my regional settings
and if I ran across one, I doubt I would run it more than once. Excel vba
is pretty much designed to work in an international environment. Stephen
Bullen's offers some hints and tips here:

http://www.oaltd.co.uk/ExcelProgRef/ch22/