Region and Language
If your application is going to be used in more than one country or the interface has to be presented in more than one language, then you will need to become familiar with the Windows Regional Settings and the Language Packs.
Any bugs that are related to international issues may not appear on your development machine.
You always want to write locale-independent code if possible.
The combination of a user's Regional Settings and their Excel settings is called the user's locale.
Windows Regional Settings
Regional settings can be accessed from the Control Panel. This is often abbreviated to WRS
Windows 10 - Settings, Region
Windows 8.1 - Settings, Region
Windows 7 - Region and Language, Formats tab, Additional Settings
Windows XP - Regional and Language Options, Regional Options, Customize
What is defined on this dialog box?
Day names used for long-dat format displayed in Excel
Month names used for long-date format displayed in Excel
Day names returned from the VBA Format function
Month names returned from the VBA Format function
Month names that are recognised by the VBA CDate Function
Month names that are recognised when typing directly into Excel cells.
The number format returned when the Excel TEXT function is used
The language of any implicit boolean conversions to string (eg "=my value is " & true)
SS - Numbers
Decimal symbol - This is a full stop in most countries (UK, US) however there are some countries that use a comma. (France)
Digit grouping symbol - Commonly referred to as the thousand operator. This is a comma in most countries (UK, US) however there are some countries that use a full stop. (Greece)
SS - Date
Short Date - This often causes the most problems. UK is dd/mm/yyyy. US is M/d/yyyy
The separator can also be different and there are some countries that use a full stop instead of forward slash. (Norway)
Long Date -
All the settings from your Windows Regional Settings and Language options can be found in the Application.International property.
The following is a list of common xlApplicationInternational constants:
xlCountryCode - the language version of Office
xlCountrySettings - windows regional settings location
xlDateOrder - the choice of MDY, DMY or TMD order to display dates.
There is no constant to identify which language version of windows in installed (but you can use an API).
On the Advanced tab under Editing Options you have the ability to override the decimal and thousand seperators.
These can also be modified in code using the following:
When converting numbers to strings, VBA uses the number formatting according to the WRS
When converting dates to strings, VBA uses the WRS "ShortDate" format.
Your code cannot assume you always have a US or UK formatted string.
When formatting dates, times or numbers, VBA uses the WRS formatting
When checking for True or False, the text used corresponds to the WRS language
Caution must be taken if a function has the ability to return different data types
Dim dtMyDate As Date
dtMyDate = DateValue("Jan 1, 2006")
Call Msgbox("Date is : " & dtMyDate)
Date literals ignore the WRS and are always in US format (mm/dd/yyyy)
When any other format different to mm/dd/yyyy is entered Excel will automatically convert it to the #mm/dd/yyyy# format.
It is much safer to avoid using date literals altogether and use VBA functions instead (DateSerial, DateValue)
Dim dtMyDate As Date
dtMyDate = #02/03/2016#
3 Feb since the format used/assumed is always mm/dd/yyyy
If you enter the date in UK date format "dd/mm/yyyy" VBA will "sometimes" automatically swap the day and month around
|Date Literal||Date Used|
|12/3/2016||12/3/2016 = 3 Dec 2016|
|13/3/2016||3/13/2016 = 13 Mar 2016|
|14/3/2016||3/14/2016 = 14 Mar 2016|
Selection.Style = "stylenameinlocallanguage"
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext