Using VBA to change regional settings
Øystein Aa Myklebust
I have tried to manually change the setting HKEY_CURRENT_USER\Control
Panel\International in registry and the WIN.INI setting, but the only way
for the change to take immideate effect is to change it through the CONTROL
PANEL, but I need to do it using VBA.
Regards
Jon Christopher Knudsen
Tom Ogilvy
obstacle you are trying to overcome and see if someone has a better approach
than changing regional settings. You may find that changing them does you
no good anyway. Make sure you state what version of Excel. Plus I was
under the impression that Excel had to be restared to implement a manual
change in the settings - you seem to say not.
Regards,
Tom Ogilvy
MVP Excel
Řystein Aa Myklebust wrote in message ...
Bill Manville
wrote:
> Is there a way to change the decimal setting in Regional Settings using VBA?
>
Here is some code I use for changing the currency symbol in an Access project
- may give you some pointers; I don't have immediate access to the data that
would tell you how to adapt it for decimal settings:
Public Const LOCALE_SYSTEM_DEFAULT = &H800
Public Const LOCALE_USER_DEFAULT = &H400
Public Const LOCALE_SCURRENCY = &H14 ' local monetary symbol
Declare Function GetLocaleInfo Lib "Kernel32" Alias "GetLocaleInfoA" (ByVal
Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData
As Long) As Long
Declare Function SetLocaleInfo Lib "Kernel32" Alias "SetLocaleInfoA" (ByVal
Locale As Long, ByVal LCType As Long, ByVal lpLCData As String) As Long
Declare Function SendMessageString Lib "user32" Alias "SendMessageA" _
(ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam
As String) As Long
Const WM_SETTINGCHANGE = &H1A
Const HWND_BROADCAST = &HFFFF
Type Str10
Buffer As String * 10
End Type
Sub InitAPI()
' this is to tell Windows to tell the application if the setting is changed!
SendMessageString HWND_BROADCAST, WM_SETTINGCHANGE, 0, vbNullString
End Sub
Function GetCurrencySymbol() As String
Dim Buffer As String * 10
Dim L As Long
L = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SCURRENCY, Buffer, 9)
GetCurrencySymbol = Left(Buffer, L - 1)
End Function
Sub SetCurrencySymbol(ByVal cChar As String)
Dim sB As Str10
Dim L As Long
Dim lKey As Long
Dim lReserved As Long
Dim lHandle As Long
Dim lResult As Long
Dim lLeng As Long
sB.Buffer = cChar & String(10 - Len(cChar), 0)
SetLocaleInfo LOCALE_USER_DEFAULT, LOCALE_SCURRENCY, sB.Buffer
End Sub
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply to newsgroup
Tim Ferguson
<my...@nhhs.nhh.no> wrote:
>Is there a way to change the decimal setting in Regional Settings using VBA?
I would be deeply pissed off if an application started playing around
with my regional settings. Word like "Trojan" spring to mind...
If you want to format dates or currencies in your app, use Format
strings. There's no need to go killing the users' other database,
mail, wordprocessing, file managers etc. etc. etc.
Tuppence worth...
Tim F
--
<!--- tim...@ferguson.netkonect.co.uk --->
Øystein Aa Myklebust
(hex) for LOCAL_SDECIMAL constant.
> Public Const LOCALE_SCURRENCY = &H14 ' local monetary symbol
Is there a list of these numbers somewhere?
Jon Christopher Knudsen
Bill Manville <Bill-M...@msn.com> wrote in message
news:VA.0000021...@msn.com...
> In article <uu1#U9AX$GA....@cppssbbsa02.microsoft.com>, Řystein Aa
Myklebust
> wrote:
> > Is there a way to change the decimal setting in Regional Settings using
VBA?
> >
Bill Manville
> Is there a list of these numbers somewhere?
>
Dan Appelman's book on VB5.0 programmers guide to the Win32 API has a
CD-ROM and on it is a file API32.TXT in which appear the following [I
know you won't need them all, but X's law says if I give you the ones I
think you need you'll be back tomorrow asking for more<g>:
Public Const LOCALE_ILANGUAGE = &H1 ' language id
Public Const LOCALE_SLANGUAGE = &H2 ' localized name of
language
Public Const LOCALE_SENGLANGUAGE = &H1001 ' English name of
language
Public Const LOCALE_SABBREVLANGNAME = &H3 ' abbreviated
language name
Public Const LOCALE_SNATIVELANGNAME = &H4 ' native name of
language
Public Const LOCALE_ICOUNTRY = &H5 ' country code
Public Const LOCALE_SCOUNTRY = &H6 ' localized name of country
Public Const LOCALE_SENGCOUNTRY = &H1002 ' English name of
country
Public Const LOCALE_SABBREVCTRYNAME = &H7 ' abbreviated
country name
Public Const LOCALE_SNATIVECTRYNAME = &H8 ' native name of
country
Public Const LOCALE_IDEFAULTLANGUAGE = &H9 ' default language
id
Public Const LOCALE_IDEFAULTCOUNTRY = &HA ' default country
code
Public Const LOCALE_IDEFAULTCODEPAGE = &HB ' default code page
Public Const LOCALE_SLIST = &HC ' list item separator
Public Const LOCALE_IMEASURE = &HD ' 0 = metric, 1 = US
Public Const LOCALE_SDECIMAL = &HE ' decimal separator
Public Const LOCALE_STHOUSAND = &HF ' thousand separator
Public Const LOCALE_SGROUPING = &H10 ' digit grouping
Public Const LOCALE_IDIGITS = &H11 ' number of fractional
digits
Public Const LOCALE_ILZERO = &H12 ' leading zeros for decimal
Public Const LOCALE_SNATIVEDIGITS = &H13 ' native ascii 0-9
Public Const LOCALE_SCURRENCY = &H14 ' local monetary symbol
Public Const LOCALE_SINTLSYMBOL = &H15 ' intl monetary symbol
Public Const LOCALE_SMONDECIMALSEP = &H16 ' monetary decimal
separator
Public Const LOCALE_SMONTHOUSANDSEP = &H17 ' monetary thousand
separator
Public Const LOCALE_SMONGROUPING = &H18 ' monetary grouping
Public Const LOCALE_ICURRDIGITS = &H19 ' # local monetary
digits
Public Const LOCALE_IINTLCURRDIGITS = &H1A ' # intl monetary
digits
Public Const LOCALE_ICURRENCY = &H1B ' positive currency mode
Public Const LOCALE_INEGCURR = &H1C ' negative currency mode
Public Const LOCALE_SDATE = &H1D ' date separator
Public Const LOCALE_STIME = &H1E ' time separator
Public Const LOCALE_SSHORTDATE = &H1F ' short date format
string
Public Const LOCALE_SLONGDATE = &H20 ' long date format string
Public Const LOCALE_STIMEFORMAT = &H1003 ' time format string
Public Const LOCALE_IDATE = &H21 ' short date format ordering
Public Const LOCALE_ILDATE = &H22 ' long date format ordering
Public Const LOCALE_ITIME = &H23 ' time format specifier
Public Const LOCALE_ICENTURY = &H24 ' century format specifier
Public Const LOCALE_ITLZERO = &H25 ' leading zeros in time
field
Public Const LOCALE_IDAYLZERO = &H26 ' leading zeros in day
field
Public Const LOCALE_IMONLZERO = &H27 ' leading zeros in month
field
Public Const LOCALE_S1159 = &H28 ' AM designator
Public Const LOCALE_S2359 = &H29 ' PM designator
Public Const LOCALE_SDAYNAME1 = &H2A ' long name for Monday
Public Const LOCALE_SDAYNAME2 = &H2B ' long name for Tuesday
Public Const LOCALE_SDAYNAME3 = &H2C ' long name for Wednesday
Public Const LOCALE_SDAYNAME4 = &H2D ' long name for Thursday
Public Const LOCALE_SDAYNAME5 = &H2E ' long name for Friday
Public Const LOCALE_SDAYNAME6 = &H2F ' long name for Saturday
Public Const LOCALE_SDAYNAME7 = &H30 ' long name for Sunday
Public Const LOCALE_SABBREVDAYNAME1 = &H31 ' abbreviated name
for Monday
Public Const LOCALE_SABBREVDAYNAME2 = &H32 ' abbreviated name
for Tuesday
Public Const LOCALE_SABBREVDAYNAME3 = &H33 ' abbreviated name
for Wednesday
Public Const LOCALE_SABBREVDAYNAME4 = &H34 ' abbreviated name
for Thursday
Public Const LOCALE_SABBREVDAYNAME5 = &H35 ' abbreviated name
for Friday
Public Const LOCALE_SABBREVDAYNAME6 = &H36 ' abbreviated name
for Saturday
Public Const LOCALE_SABBREVDAYNAME7 = &H37 ' abbreviated name
for Sunday
Public Const LOCALE_SMONTHNAME1 = &H38 ' long name for January
Public Const LOCALE_SMONTHNAME2 = &H39 ' long name for February
Public Const LOCALE_SMONTHNAME3 = &H3A ' long name for March
Public Const LOCALE_SMONTHNAME4 = &H3B ' long name for April
Public Const LOCALE_SMONTHNAME5 = &H3C ' long name for May
Public Const LOCALE_SMONTHNAME6 = &H3D ' long name for June
Public Const LOCALE_SMONTHNAME7 = &H3E ' long name for July
Public Const LOCALE_SMONTHNAME8 = &H3F ' long name for August
Public Const LOCALE_SMONTHNAME9 = &H40 ' long name for
September
Public Const LOCALE_SMONTHNAME10 = &H41 ' long name for October
Public Const LOCALE_SMONTHNAME11 = &H42 ' long name for
November
Public Const LOCALE_SMONTHNAME12 = &H43 ' long name for
December
Public Const LOCALE_SABBREVMONTHNAME1 = &H44 ' abbreviated name
for January
Public Const LOCALE_SABBREVMONTHNAME2 = &H45 ' abbreviated name
for February
Public Const LOCALE_SABBREVMONTHNAME3 = &H46 ' abbreviated name
for March
Public Const LOCALE_SABBREVMONTHNAME4 = &H47 ' abbreviated name
for April
Public Const LOCALE_SABBREVMONTHNAME5 = &H48 ' abbreviated name
for May
Public Const LOCALE_SABBREVMONTHNAME6 = &H49 ' abbreviated name
for June
Public Const LOCALE_SABBREVMONTHNAME7 = &H4A ' abbreviated name
for July
Public Const LOCALE_SABBREVMONTHNAME8 = &H4B ' abbreviated name
for August
Public Const LOCALE_SABBREVMONTHNAME9 = &H4C ' abbreviated name
for September
Public Const LOCALE_SABBREVMONTHNAME10 = &H4D ' abbreviated
name for October
Public Const LOCALE_SABBREVMONTHNAME11 = &H4E ' abbreviated
name for November
Public Const LOCALE_SABBREVMONTHNAME12 = &H4F ' abbreviated
name for December
Public Const LOCALE_SABBREVMONTHNAME13 = &H100F
Public Const LOCALE_SPOSITIVESIGN = &H50 ' positive sign
Public Const LOCALE_SNEGATIVESIGN = &H51 ' negative sign
Public Const LOCALE_IPOSSIGNPOSN = &H52 ' positive sign
position
Public Const LOCALE_INEGSIGNPOSN = &H53 ' negative sign
position
Public Const LOCALE_IPOSSYMPRECEDES = &H54 ' mon sym precedes
pos amt
Public Const LOCALE_IPOSSEPBYSPACE = &H55 ' mon sym sep by
space from pos amt
Public Const LOCALE_INEGSYMPRECEDES = &H56 ' mon sym precedes
neg amt
Public Const LOCALE_INEGSEPBYSPACE = &H57 ' mon sym sep by
space from neg amt