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.


Users Locale

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-date 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.
xlYearCode -
xlMonthCode -
xlDayCode -


There is no constant to identify which language version of windows in installed (but you can use an API).


Excel Settings

On the Advanced tab under Editing Options you have the ability to override the decimal and thousand separators.
SS
These can also be modified in code using the following:

Application.ThousandSeparator 
Application.DecimalSeparator

Implicit Conversion

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

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)


#01/01/2016# 

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 LiteralDate Used
12/3/201612/3/2016 = 3 Dec 2016
13/3/20163/13/2016 = 13 Mar 2016
14/3/20163/14/2016 = 14 Mar 2016


Selection.Style = "stylenameinlocallanguage" 



© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext