Windows Region settings control how your computer handles data, including the default language. For example, English is spoken in several countries. In code, it appears as “en.” To account for regional differences, an identifier is added to represent the geographical location. English in the United States is “en-US” (lowercase language and uppercase region). English in the United Kingdom is “en-UK,” while “en” by itself is neutral. The combination of codes for language and region (or country) make up the locale.
Figure 1 shows some of the information that changes its value or display depending on locale. Table 1 lists how the values in Figure 1 are calculated. Sometimes format is used, and sometimes international properties are looked up. When the form loads, VBA runs to fill some information, while an expression in the control source is used for other information.
If you open the download file, you will see a temporary form pop up with a “Please wait” message until the international settings are read. Since Access opens an instance of Excel to get some of the information, this might take noticeable time. Both applications have a LanguageSettings property that specifies a LanguageID, given an index where install language is 1, user interface language is 2, help language is 3, execution mode language is 4, and so on. This is called a locale identifier, or LCID.
In VBA, we can use LanguageSettings.LanguageID(2), which is an application property, to get the current LCID for the user interface. Sometimes the install language is English while the user interface language is something else. When building applications, we often need to know the user interface because that’s how data will be entered.
Once the numeric LCID is known, text for the locale can be looked up in the LCIDz table, which includes LCID, LangCtry (language and country), and LangCtry_Local (the localized version of language and country).
The form in Figure 1 has a control that identifies the LCID. A function called Get_LanguageCountry opens a DAO record set for the specified LCID. (An alternative would be to use a combo box with a row source on the LCIDz table.) When it runs, it returns text for the numeric LCID. It also sets a variable so the localized version (if different) can be retrieved. For instance, “Danish (Denmark)” is localized to “dansk (Danmark).”
The LCIDz table in the download database is still in progress. Data has been compiled from a number of sources and is still being corrected and filled. Some of the records, even for generic languages, have been fill with country code (Ctry) for a general reference, not because it’s official. For instance, KG (Kazakhstan), is listed for the Kazakh (variation of Turkish) language, although usage may instead be AZ (Azerbaijan) or something else.
Values colored green in the form are calculated with named formats. Current Date displays the results of the Date() function for both the Long Date and Short Date formats. By using named formats instead of specifying a format code such as mm/dd/yy, the data will display based on the language and location specified in the Windows Region settings. So if the locale changes, the format of that data will adjust accordingly.
Current Time uses the Time() function and is displayed in Long Time format. The AM/PM indicator, which is visible for a 12-hour clock, is a calculated control with the control source set to =Format(Now(),"AM/PM"). Theoretically, you should also be able to get the time and date separators this way, but I didn’t try it for different locales.
There are two image controls that indicate the part of day. A sunshine picture shows when time is between midnight (12 a.m.) and noon (12 p.m.), while a starry sky and moon image displays during the afternoon and evening.
Values colored dark red are generated by code. Excel can be used to read some of the Windows settings that Access can’t. The oExcel reference in Table 1 refers to the Excel application object. Excel has application properties, like LanguageID, and more. One of these is called International, with information returned depending on the index. In code, oExcel.International(#) is used to read a setting where # is the index, for example, use 17 for the date separator, 18 for the time separator, 33 for true for 24-hour clock, 32 for date order, 3 for decimal separator, 4 for thousands separator, 5 for list separator, 25 for currency symbol, 37 is true for CurrencyBefore, and 35 is true if Measurement System is metric. This list of index constants in Excel is enumerated under XlApplicationInternational.
When building international applications, you may need to determine local values for international settings when constructing or separating information. Sometimes what’s returned using the International property is exactly what you want, such as the currency symbol or a separator string. Other times, what’s returned needs to be defined further. Date Order in the U.S. is MDY (Month-Day-Year), and it’s stored as 0. DMY, or Day-Month-Year, is 1 and is most common. YMD, or Year-Month-Day, is 2. This is a good format to use because it’s unambiguous.
Thanks to Access experts Gustav Brock in Denmark and Daniel Pineault in Canada, both of whom build international applications, for their valuable contributions to this article.
Download this month’s database: SF1809_Localized-International-Information.
SF SAYS
Named formats such as Long Date and Short Date will display information in your localized pattern.
September 2018
