I need to change the region format to "French (Canada)" in order for datevalue
to accept french months (eg. 05 Mars, 12 Avril, etc) and then revert it back to "English (Canada)" within the VBA code.
I hope it's not as complex and that there is a write property that can modify this setting with VBA.
So far, I've found Application.International(xlCountrySetting) but it is only a read property.
This is the setting in question I wish to change:
Thanks
As pointed out in the comments, you can do this with a couple simple Windows API calls. Instead of assuming that the machine is currently set to "English (Canada)", I'd suggest testing the current setting with GetUserDefaultLCID
, and then setting it back to that when you're done.
#If VBA7 Then
Private Declare PtrSafe Function SetThreadLocale Lib "kernel32" _
(ByVal Locale As Long) As Boolean
Private Declare PtrSafe Function GetUserDefaultLCID Lib "kernel32" () As Long
Private Declare PtrSafe Function LocaleNameToLCID Lib "kernel32" _
(ByVal lpName As LongPtr, dwFlags As Long) As Long
#Else
Private Declare Function SetThreadLocale Lib "kernel32" (ByVal Locale As Long) As Boolean
Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long
Private Declare Function LocaleNameToLCID Lib "kernel32" _
(ByVal lpName As LongPtr, dwFlags As Long) As Long
#End If
Private Sub Test()
'Get the locale identifier for French (Canada)
Dim frCa As Long
frCa = LocaleNameToLCID(StrPtr("fr-CA"), 0)
'Make sure there function succeeded.
If result = 0 Then
'Cache the current locale
Dim userLocale As Long
userLocale = GetUserDefaultLCID
'Switch to French (Canada)
If SetThreadLocale(frCa) Then
'en français
'...
'switch back
SetThreadLocale userLocale
End If
End If
End Sub
Documentation links for the relevant functions are below: