If you need to handle multiple languages, get some specific regional settings, or do things like implement dynamic runtime language switching in your Microsoft Office solutions, you can leverage some additional help using the national language support Win32 API in VBA.
Do you know why you have to use the US English date format when you assign a date literal to a variable in VBA or in a SQL statement, surrounding it with “#” characters (like in: MyDate = #1/6/2020# for the 6 jan. 2020) ?
That’s because VBA (and the Office Object Model) is internally language locked on the locale which language ID is #1033.
What’s a locale and a language ID ? That’s how Windows manages multi language support.
There’s a lot of information about that on MSDN and on the web. Too much in fact to get started quickly.
This post will try to help you spare some time, make that journey quick and easy and jump into action now.
Here you’ll find :
-
A short review of the key concepts regarding national language support in Windows. Just what you need to know. Like what that means, and what language IDs and locales are.
-
A VBA standard module wrapping some of the most useful NLS Windows API immediately actionable.
-
A Microsoft Access database demo application using the module.
See the short demo video:
National language support with the Windows API
From MSDN:
The National Language Support (NLS) functions help applications support the different language- and locale-specific needs of users around the world.
The National Language Support (NLS) functions permit applications to:
- Set the locale for the user
- Identify the language in which the user works
- Retrieve strings representing times, dates, and other information formatted correctly for the specified language and locale
Locales and Languages
Here’s the short version, while the full version of this topic is on MSDN.
-
In Windows, each language is identified by a 32bits Long value, the LANGUAGE ID, which has two parts:
- The lower 16bits are the PRIMARY LANGUAGE ID
- The higher 16bits are the SUB LANGUAGE ID
Each language id can be translated to a string. For example, 1033 is “en_US” and 4108 is “fr_CH”. “en” is for the PRIMARY LANGUAGE ID, and “US” for the region, is the SUB LANGUAGE ID.
-
Each LANGUAGE ID has an associated LOCALE
A “locale” is a collection of language-related user preference information represented as a list of values
-
Each value in the locale list, is indexed by (ie accessible via) a 32bits Long value, the LOCALE ID.
-
There can be multiple locales associated with one (primary) language (“en_US”, “en_GB”, etc…).
-
By using a valid LANGUAGE ID, applications can get localized strings and regional settings, like the full or abbreviated names of days and months, the region currency name and symbol, the various date and time formats, etc…
-
There is a preinstalled set of languages (and their locale settings) that ships with Windows.
- LANG_SYSTEM_DEFAULT is a predefined constant for the language ID used to install WIndows. SUBLANG_SYS_DEFAULT represents its sub language ID.
- LOCALE_USER_DEFAULT is a predefined constant for the language ID set for the current user.
- There’s a predefined set of constants (LANG_*) for PRIMARY LANGUAGE IDs.
- There’s a predefined set of constants (SUBLANG_*) for SUB LANGUAGE IDs.
Microsoft Office Application object language settings
Each VBA project in an Office application has access to a global Application object that has a:
-
LanguageSettings member that will give us a set of language IDs (MsoAppLanguageID enumeration), among which we can distinguish:
- The language ID used to install Office (msoLanguageIDInstall),
- The language ID in use for the current application (msoLanguageIDUI)
-
LanguagePreferredForEditing() function, that returns True, if a given language ID is used for editing (however that applies to the application).
Example (demo database)
Localization_win32.accdb, in this github repository, is an Access Database that illustrates how we can retrieve these language and regional settings from VBA.
It contains the MIntl.bas standard Visual Basic module, that wraps the necessary Win32 API functions and declarations. It also provides shortcut functions for some frequently used settings.
More on globalization
Globalization Documentation portal on MSDN.
Downloads
The files of this project are in the github repository, including the demo database (MIT license).
Recent Comments