Skip to main content

Using Regional settings in your script

I saw a posting at the IDEA web site in which the person was having problems because sometimes the equations used would take a "," and other times it would take a ";".  The reason for this is because of the regional settings. In Canada we deal with both English and French languages, in English the list separator is the "," and in French it is the ";".  So if you are creating an equation in the equation editor you would use the "," or ";" based on the language.  So an @left function would look like @left(FIELD, 2) in English and @left(FIELD; 2) in French.  This is well and good if you are working in that language and you are entering the function into the equation editor directly but what if you have an equation in a script?  If you post the script for general use it might be used by persons with different regional settings than you and they would have errors because of this difference.  Unfortunately IDEA doesn't seem to have a function (or none that I have found) to get around this, fortunately Excel does and as Excel uses the same language as IDEA you can tap into this.  So the example script below will create an excel object and then go get the International setting for the list separator (you can find a list of all the international option here), what you would do is create a variable to hold the separator and use that instead of the "," or the ";", now your script should work in any setting.  Love to hear any comments on this or anyone else that has had experience doing this.

Const xlListSeparator = 5

Dim sListSeparator As String

Sub Main
	Dim excel As Object

	Set excel = CreateObject("Excel.Application")
	
	MsgBox excel.International(xlListSeparator)
	
	sListSeparator = excel.International(xlListSeparator)
	
	Set db = Client.OpenDatabase("Your File.IMD")
	Set task = db.TableManagement
	Set table = db.TableDef
	Set field = table.NewField
	eqn = "@left(NAME" & sListSeparator & "  2)"
	field.Name = "TEST"
	field.Description = ""
	field.Type = WI_VIRT_CHAR
	field.Equation = eqn
	field.Length = 4
	task.AppendField field
	task.PerformTask
	Set task = Nothing
	Set db = Nothing
	Set table = Nothing
	Set field = Nothing

	Set excel = Nothing

End Sub

 

Brian Element Mon, 07/28/2014 - 12:28

I found and adapted the following.  This uses the windows kernal so you don't need excel to be installed.  It will return the following based on the computers regional settings so this is great for using for list separators which may be a "," in some languages while a ";" in others.

Public Const LOCALE_ILANGUAGE = &H1         '  language id
Public Const LOCALE_SLANGUAGE = &H2         '  localized name of language
Public Const LOCALE_SENGLANGUAGE = &H1001   '  English name of language
Public Const LOCALE_SABBREVLANGNAME = &H3   '  abbreviated language name
Public Const LOCALE_SNATIVELANGNAME = &H4   '  native name of language
Public Const LOCALE_ICOUNTRY = &H5          '  country code
Public Const LOCALE_SCOUNTRY = &H6          '  localized name of country
Public Const LOCALE_SENGCOUNTRY = &H1002    '  English name of country
Public Const LOCALE_SABBREVCTRYNAME = &H7   '  abbreviated country name
Public Const LOCALE_SNATIVECTRYNAME = &H8   '  native name of country
Public Const LOCALE_IDEFAULTLANGUAGE = &H9  '  default language id
Public Const LOCALE_IDEFAULTCOUNTRY = &HA   '  default country code
Public Const LOCALE_IDEFAULTCODEPAGE = &HB  '  default code page
Public Const LOCALE_SLIST = &HC             '  list item separator
Public Const LOCALE_IMEASURE = &HD          '  0 = metric, 1 = US
Public Const LOCALE_SDECIMAL = &HE          '  decimal separator
Public Const LOCALE_STHOUSAND = &HF         '  thousand separator
Public Const LOCALE_SGROUPING = &H10        '  digit grouping
Public Const LOCALE_IDIGITS = &H11          '  number of fractional digits
Public Const LOCALE_ILZERO = &H12           '  leading zeros for decimal
Public Const LOCALE_SNATIVEDIGITS = &H13    '  native ascii 0-9
Public Const LOCALE_SCURRENCY = &H14        '  local monetary symbol
Public Const LOCALE_SINTLSYMBOL = &H15      '  intl monetary symbol
Public Const LOCALE_SMONDECIMALSEP = &H16   '  monetary decimal separator
Public Const LOCALE_SMONTHOUSANDSEP = &H17  '  monetary thousand separator
Public Const LOCALE_SMONGROUPING = &H18     '  monetary grouping
Public Const LOCALE_ICURRDIGITS = &H19      '  # local monetary digits
Public Const LOCALE_IINTLCURRDIGITS = &H1A  '  # intl monetary digits
Public Const LOCALE_ICURRENCY = &H1B        '  positive currency mode
Public Const LOCALE_INEGCURR = &H1C         '  negative currency mode
Public Const LOCALE_SDATE = &H1D            '  date separator
Public Const LOCALE_STIME = &H1E            '  time separator
Public Const LOCALE_SSHORTDATE = &H1F       '  short date format string
Public Const LOCALE_SLONGDATE = &H20        '  long date format string
Public Const LOCALE_STIMEFORMAT = &H1003    '  time format string
Public Const LOCALE_IDATE = &H21            '  short date format ordering
Public Const LOCALE_ILDATE = &H22           '  long date format ordering
Public Const LOCALE_ITIME = &H23            '  time format specifier
Public Const LOCALE_ICENTURY = &H24         '  century format specifier
Public Const LOCALE_ITLZERO = &H25          '  leading zeros in time field
Public Const LOCALE_IDAYLZERO = &H26        '  leading zeros in day field
Public Const LOCALE_IMONLZERO = &H27        '  leading zeros in month field
Public Const LOCALE_S1159 = &H28            '  AM designator
Public Const LOCALE_S2359 = &H29            '  PM designator
Public Const LOCALE_SDAYNAME1 = &H2A        '  long name for Monday
Public Const LOCALE_SDAYNAME2 = &H2B        '  long name for Tuesday
Public Const LOCALE_SDAYNAME3 = &H2C        '  long name for Wednesday
Public Const LOCALE_SDAYNAME4 = &H2D        '  long name for Thursday
Public Const LOCALE_SDAYNAME5 = &H2E        '  long name for Friday
Public Const LOCALE_SDAYNAME6 = &H2F        '  long name for Saturday
Public Const LOCALE_SDAYNAME7 = &H30        '  long name for Sunday
Public Const LOCALE_SABBREVDAYNAME1 = &H31  '  abbreviated name for Monday
Public Const LOCALE_SABBREVDAYNAME2 = &H32  '  abbreviated name for Tuesday
Public Const LOCALE_SABBREVDAYNAME3 = &H33  '  abbreviated name for Wednesday
Public Const LOCALE_SABBREVDAYNAME4 = &H34  '  abbreviated name for Thursday
Public Const LOCALE_SABBREVDAYNAME5 = &H35  '  abbreviated name for Friday
Public Const LOCALE_SABBREVDAYNAME6 = &H36  '  abbreviated name for Saturday
Public Const LOCALE_SABBREVDAYNAME7 = &H37  '  abbreviated name for Sunday
Public Const LOCALE_SMONTHNAME1 = &H38      '  long name for January
Public Const LOCALE_SMONTHNAME2 = &H39      '  long name for February
Public Const LOCALE_SMONTHNAME3 = &H3A      '  long name for March
Public Const LOCALE_SMONTHNAME4 = &H3B      '  long name for April
Public Const LOCALE_SMONTHNAME5 = &H3C      '  long name for May
Public Const LOCALE_SMONTHNAME6 = &H3D      '  long name for June
Public Const LOCALE_SMONTHNAME7 = &H3E      '  long name for July
Public Const LOCALE_SMONTHNAME8 = &H3F      '  long name for August
Public Const LOCALE_SMONTHNAME9 = &H40      '  long name for September
Public Const LOCALE_SMONTHNAME10 = &H41     '  long name for October
Public Const LOCALE_SMONTHNAME11 = &H42     '  long name for November
Public Const LOCALE_SMONTHNAME12 = &H43     '  long name for December
Public Const LOCALE_SABBREVMONTHNAME1 = &H44 '  abbreviated name for January
Public Const LOCALE_SABBREVMONTHNAME2 = &H45 '  abbreviated name for February
Public Const LOCALE_SABBREVMONTHNAME3 = &H46 '  abbreviated name for March
Public Const LOCALE_SABBREVMONTHNAME4 = &H47 '  abbreviated name for April
Public Const LOCALE_SABBREVMONTHNAME5 = &H48 '  abbreviated name for May
Public Const LOCALE_SABBREVMONTHNAME6 = &H49 '  abbreviated name for June
Public Const LOCALE_SABBREVMONTHNAME7 = &H4A '  abbreviated name for July
Public Const LOCALE_SABBREVMONTHNAME8 = &H4B '  abbreviated name for August
Public Const LOCALE_SABBREVMONTHNAME9 = &H4C '  abbreviated name for September
Public Const LOCALE_SABBREVMONTHNAME10 = &H4D '  abbreviated name for October
Public Const LOCALE_SABBREVMONTHNAME11 = &H4E '  abbreviated name for November
Public Const LOCALE_SABBREVMONTHNAME12 = &H4F '  abbreviated name for December
Public Const LOCALE_SABBREVMONTHNAME13 = &H100F

Public Const LOCALE_SYSTEM_DEFAULT& = &H800
Public Const LOCALE_USER_DEFAULT& = &H400

Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
Sub Main
	MsgBox ("UserDefault: " & ReadLocaleInfo(LOCALE_SLIST ))

End Sub

'*****************************************************************************************************************
'* VB function found at http://www.xtremevbtalk.com/showthread.php?t=162703
'* will return the regional setting based on the constant sent to it.
'*****************************************************************************************************************

Public Function ReadLocaleInfo(ByVal lInfo As Long) As String
    
	    Dim sBuffer As String
	    Dim rv As Long
	    
	    sBuffer = String$(256, 0)
	    rv = GetLocaleInfo(LOCALE_USER_DEFAULT, lInfo, sBuffer, Len(sBuffer))
	    
	    If rv > 0 Then
	        ReadLocaleInfo = Left$(sBuffer, rv - 1)
	    Else
	        ReadLocaleInfo = ""
	    End If
End Function