Originally Posted by
snb
Why do you rebuild built-in Excel options ?
It all can be done with 0 lines of VBA-code.
Not sure about the 0 lines, but there is a lot of information available using the APIs
For example, the test sub below retrieves the days of the week for several languages. You have to use a userform control since there are the foreign (to us US-ers) and don't show in Msgbox
I have an InitLocale sub that populates these public variables based on the Locale ID (LCID)
Using the locale arrays to format a date or something else is left as a homework assignment
'Public Month, DOW, and Day strings ----------------------------------------------------------------------------
'NOTE -- if 13 month calendar then these are ReDim Preserve to (1 to 13) so ALWAYS use LBound to UBound
'
'Public LocaleMonths() As String 'Array of months in local language
'Public LocaleMonthsAbbr() As String 'Array of month abbrevations in local language
' 'returns the standalone, or nominative, form of the month name
'Public LocaleMonthsGenitive() As String 'Array of genitive months abbrevations in local language (
' 'Genitive names exist because some languages use a different case of nouns to express dates
' '(genitive instead of nominative).
' 'in Polish nominative for January is "styczen" but to express a date 2 January
' 'you need to use genitive "2 stycznia".
'
'Public LocaleDays(1 To 7) As String 'Array of days of week in local language
'Public LocaleDaysAbbr(1 To 7) As String 'Array of days of week abbrevation in local language
'Public LocaleDaysShort(1 To 7) As String 'Array of 2 char days of the week in local language for calendar day titles
'Public LocaleDayOfWeek(1 To 7) As Long 'Array maps WeekDay function (1 to 7) to LocaleDays(x), etc.
' 'LocaleDays(LocaleDayOfWeek(WeekDay(Now)))
'Public LocaleStartingDayOfWeek As VBA.VbDayOfWeek
'Public LocaleStartingWeekOfYear As VBA.VbFirstWeekOfYear
'
'Public LocaleLongDateFormat As String 'dddd, MMMM d, yyyy
'Public LocaleShortDateFormat As String 'M/d/yyyy
Simple sub to display DOW
Option Explicit
Sub test()
Load UserForm1
'user default
Call InitLocale
ShowDOW
'spanish
Call InitLocale("es")
ShowDOW
'german
Call InitLocale("de")
ShowDOW
'arabic
Call InitLocale("ar")
ShowDOW
'russian
Call InitLocale("ru")
ShowDOW
End Sub
Sub ShowDOW()
With UserForm1
.Label1.Caption = LocaleDays(1)
.Label2.Caption = LocaleDays(2)
.Label3.Caption = LocaleDays(3)
.Label4.Caption = LocaleDays(4)
.Label5.Caption = LocaleDays(5)
.Label6.Caption = LocaleDays(6)
.Label7.Caption = LocaleDays(7)
.Show
End With
End Sub
Locale and Language ID's are in a worksheet in the attachment as is LocaleInit()
I like to use (re-use) modules so this is one from my 'toolbox'
'----------------------------------------------------------------------------------------
' sys_Locale - utilities to work with international data
' ver 05 2/4/2021
' fixed typos
' ver 04 11/17/2020
' add Excel uses an (undocumented) escape sequence for "system default"
' This is
' [$-F800] for date, long system default
' [$-F400] for time, system default".
'
' ver 03 9/17/2020
' tweaks to work with CalendarForm better
' ver 02 10/13/2017
' changed names localeInfo and localeDate
' change localeInfo to use LOCALE_NAME_USER_DEFAULT (= "") for User Default location
' ver 01 10/13/2014
' initial
'----------------------------------------------------------------------------------------
Option Explicit
Option Private Module
Private Declare PtrSafe Function GetLocaleInfoEx Lib "kernel32" (ByVal lpLocaleName As LongPtr, ByVal LCType As Long, ByVal lpLCData As LongPtr, ByVal cchData As Long) As Long
'https://docs.microsoft.com/en-us/windows/win32/intl/national-language-support-reference
'---------------------------------------------------------------------------------------- type
Public Const LOCALE_NAME_USER_DEFAULT = vbNullString
Public Const LOCALE_USER_DEFAULT As Long = &H400
'---------------------------------------------------------------------------------------- locale
Public Const LOCALE_SNAME As Long = &H5C 'en-US
'---------------------------------------------------------------------------------------- native
Public Const LOCALE_SNATIVECOUNTRYNAME As Long = &H8 'native name of country = United States
Public Const LOCALE_SNATIVELANGUAGENAME As Long = &H4 'native name of language = English
Public Const LOCALE_SNATIVEDIGITS As Long = &H13 'native digits = 0123456789
Public Const LOCALE_SNATIVECURRNAME As Long = &H1008 'native name of currency = US Dollar
'---------------------------------------------------------------------------------------- language
Public Const LOCALE_SENGLANGUAGE As Long = &H1001 'English name of language
Public Const LOCALE_ILANGUAGE As Long = &H1 '0409
Public Const LOCALE_SLANGUAGE As Long = &H2 'English (United States)
Public Const LOCALE_SABBREVLANGNAME As Long = &H3 'ENU
Public Const LOCALE_SPARENT As Long = &H6D 'en
Public Const LOCALE_SISO639LANGNAME As Long = &H59 'en
Public Const LOCALE_SISO639LANGNAME2 As Long = &H67 'eng
'---------------------------------------------------------------------------------------- country
Public Const LOCALE_SENGCOUNTRY As Long = &H1002 'English name of country
Public Const LOCALE_ICOUNTRY As Long = &H5 '1
Public Const LOCALE_SCOUNTRY As Long = &H6 'United States
Public Const LOCALE_SABBREVCTRYNAME As Long = &H7 'USA
Public Const LOCALE_IDEFAULTLANGUAGE As Long = &H9
Public Const LOCALE_IDEFAULTCOUNTRY As Long = &HA
Public Const LOCALE_IDEFAULTCODEPAGE As Long = &HB
Public Const LOCALE_SISO3166CTRYNAME As Long = &H5A 'US
Public Const LOCALE_SISO3166CTRYNAME2 As Long = &H68 'USA
'---------------------------------------------------------------------------------------- numbers
Public Const LOCALE_SLIST = &HC
Public Const LOCALE_IMEASURE As Long = &HD
Public Const LOCALE_SDECIMAL As Long = &HE
Public Const LOCALE_STHOUSAND As Long = &HF
Public Const LOCALE_SGROUPING As Long = &H10
Public Const LOCALE_IDIGITS As Long = &H11
Public Const LOCALE_ILZERO As Long = &H12
Public Const LOCALE_SINTLSYMBOL As Long = &H15 'intl symbol
Public Const LOCALE_SMONDECIMALSEP As Long = &H16 'decimal separator
Public Const LOCALE_SMONTHOUSANDSEP As Long = &H17 'thousand separator
Public Const LOCALE_SMONGROUPING As Long = &H18 'grouping
Public Const LOCALE_ICENTURY As Long = &H24
Public Const LOCALE_ITLZERO As Long = &H25
Public Const LOCALE_SPOSITIVESIGN As Long = &H50
Public Const LOCALE_SNEGATIVESIGN As Long = &H51
Public Const LOCALE_IPOSSIGNPOSN As Long = &H52 'pos sign position
Public Const LOCALE_INEGSIGNPOSN As Long = &H53 'neg sign position
Public Const LOCALE_INEGATIVEPERCENT As Long = &H74
Public Const LOCALE_IPOSITIVEPERCENT As Long = &H75
Public Const LOCALE_SPERCENT As Long = &H76
Public Const LOCALE_SPERMILLE As Long = &H77
Public Const LOCALE_SPOSINFINITY As Long = &H6A
Public Const LOCALE_SNEGINFINITY As Long = &H6B
Public Const LOCALE_INEGNUMBER As Long = &H1010
'---------------------------------------------------------------------------------------- currency
Public Const LOCALE_IINTLCURRDIGITS As Long = &H1A '# intl digits
Public Const LOCALE_SCURRENCY As Long = &H14 'local currency symbol
Public Const LOCALE_ICURRENCY As Long = &H1B 'pos currency mode
Public Const LOCALE_INEGCURR As Long = &H1C 'neg currency mode
Public Const LOCALE_ICURRDIGITS As Long = &H19 '# local digits
Public Const LOCALE_IPOSSYMPRECEDES As Long = &H54 'mon sym precedes pos amt
Public Const LOCALE_IPOSSEPBYSPACE As Long = &H55 'mon sym sep by space from pos amt
Public Const LOCALE_INEGSYMPRECEDES As Long = &H56 'mon sym precedes neg amt
Public Const LOCALE_INEGSEPBYSPACE As Long = &H57 'mon sym sep by space from neg amt
Public Const LOCALE_SENGCURRNAME As Long = &H1007 'english name of currency
'---------------------------------------------------------------------------------------- formats
Public Const LOCALE_SSHORTDATE As Long = &H1F 'M/d/yyyy
Public Const LOCALE_SLONGDATE As Long = &H20 'dddd, MMMM d, yyyy
Public Const LOCALE_SMONTHDAY As Long = &H78 'MMMM d
Public Const LOCALE_SSHORTTIME As Long = &H79 'h:mm tt
Public Const LOCALE_STIMEFORMAT As Long = &H1003 'h:mm:ss tt
Public Const LOCALE_SYEARMONTH As Long = &H1006 'MMMM yyyy
'---------------------------------------------------------------------------------------- time
Public Const LOCALE_S1159 As Long = &H28 'AM
Public Const LOCALE_S2359 As Long = &H29 'PM
Public Const LOCALE_STIME As Long = &H1E 'time seperator ":"
Public Const LOCALE_ITIME As Long = &H23
Public Const LOCALE_SDURATION As Long = &H5D 'h:mm:ss
'---------------------------------------------------------------------------------------- dates
Public Const LOCALE_SDATE As Long = &H1D 'date seperator "/"
Public Const LOCALE_IDATE As Long = &H21
Public Const LOCALE_ILDATE As Long = &H22
Public Const LOCALE_IDAYLZERO As Long = &H26
Public Const LOCALE_IMONLZERO As Long = &H27
Public Const LOCALE_IFIRSTDAYOFWEEK As Long = &H100C
'0 LOCALE_SDAYNAME1 (Monday)
'1 LOCALE_SDAYNAME2 (Tuesday)
'2 LOCALE_SDAYNAME3 (Wednesday)
'3 LOCALE_SDAYNAME4 (Thursday)
'4 LOCALE_SDAYNAME5 (Friday)
'5 LOCALE_SDAYNAME6 (Saturday)
'6 LOCALE_SDAYNAME7 (Sunday)
Public Const LOCALE_IFIRSTWEEKOFYEAR As Long = &H100D
'vbUseSystem as VbFirstWeekOfYear
' vbFirstFourDays, vbFirstFullWeek, vbFirstJan1
'---------------------------------------------------------------------------------------- days
Public Const LOCALE_SDAYNAME1 As Long = &H2A 'long name for Monday
Public Const LOCALE_SDAYNAME2 As Long = &H2B 'long name for Tuesday
Public Const LOCALE_SDAYNAME3 As Long = &H2C 'long name for Wednesday
Public Const LOCALE_SDAYNAME4 As Long = &H2D 'long name for Thursday
Public Const LOCALE_SDAYNAME5 As Long = &H2E 'long name for Friday
Public Const LOCALE_SDAYNAME6 As Long = &H2F 'long name for Saturday
Public Const LOCALE_SDAYNAME7 As Long = &H30 'long name for Sunday
Public Const LOCALE_SABBREVDAYNAME1 As Long = &H31 'short name for Mon
Public Const LOCALE_SABBREVDAYNAME2 As Long = &H32 'short name for Tue
Public Const LOCALE_SABBREVDAYNAME3 As Long = &H33 'short name for Wed
Public Const LOCALE_SABBREVDAYNAME4 As Long = &H34 'short name for Thu
Public Const LOCALE_SABBREVDAYNAME5 As Long = &H35 'short name for Fri
Public Const LOCALE_SABBREVDAYNAME6 As Long = &H36 'short name for Sat
Public Const LOCALE_SABBREVDAYNAME7 As Long = &H37 'short name for Sun
Public Const LOCALE_SSHORTESTDAYNAME1 As Long = &H60 '2 char version Mo
Public Const LOCALE_SSHORTESTDAYNAME2 As Long = &H61
Public Const LOCALE_SSHORTESTDAYNAME3 As Long = &H62
Public Const LOCALE_SSHORTESTDAYNAME4 As Long = &H63
Public Const LOCALE_SSHORTESTDAYNAME5 As Long = &H64
Public Const LOCALE_SSHORTESTDAYNAME6 As Long = &H65
Public Const LOCALE_SSHORTESTDAYNAME7 As Long = &H66
'---------------------------------------------------------------------------------------- months
Public Const LOCALE_SMONTHNAME1 As Long = &H38 'January
Public Const LOCALE_SMONTHNAME2 As Long = &H39
Public Const LOCALE_SMONTHNAME3 As Long = &H3A
Public Const LOCALE_SMONTHNAME4 As Long = &H3B
Public Const LOCALE_SMONTHNAME5 As Long = &H3C
Public Const LOCALE_SMONTHNAME6 As Long = &H3D
Public Const LOCALE_SMONTHNAME7 As Long = &H3E
Public Const LOCALE_SMONTHNAME8 As Long = &H3F
Public Const LOCALE_SMONTHNAME9 As Long = &H40
Public Const LOCALE_SMONTHNAME10 As Long = &H41
Public Const LOCALE_SMONTHNAME11 As Long = &H42
Public Const LOCALE_SMONTHNAME12 As Long = &H43 'December
Public Const LOCALE_SABBREVMONTHNAME1 As Long = &H44 'Jan
Public Const LOCALE_SABBREVMONTHNAME2 As Long = &H45
Public Const LOCALE_SABBREVMONTHNAME3 As Long = &H46
Public Const LOCALE_SABBREVMONTHNAME4 As Long = &H47
Public Const LOCALE_SABBREVMONTHNAME5 As Long = &H48
Public Const LOCALE_SABBREVMONTHNAME6 As Long = &H49
Public Const LOCALE_SABBREVMONTHNAME7 As Long = &H4A
Public Const LOCALE_SABBREVMONTHNAME8 As Long = &H4B
Public Const LOCALE_SABBREVMONTHNAME9 As Long = &H4C
Public Const LOCALE_SABBREVMONTHNAME10 As Long = &H4D
Public Const LOCALE_SABBREVMONTHNAME11 As Long = &H4E
Public Const LOCALE_SABBREVMONTHNAME12 As Long = &H4F 'Dec
Public Const LOCALE_SMONTHNAME13 As Long = &H100E
Public Const LOCALE_SABBREVMONTHNAME13 As Long = &H100F
'------------------------------------------------------------------------------------misc
Public Const LOCALE_FONTSIGNATURE As Long = &H58
Public Const LOCALE_IGEOID As Long = &H5B '244
Public Const LOCALE_SKEYBOARDSTOINSTALL = &H5E
Public Const LOCALE_SNAN As Long = &H69
Public Const LOCALE_SSCRIPTS As Long = &H6C 'Latn;
Public Const LOCALE_SCONSOLEFALLBACKNAME As Long = &H6E
Public Const LOCALE_IREADINGLAYOUT As Long = &H70
Public Const LOCALE_INEUTRAL As Long = &H71
Public Const LOCALE_SOPENTYPELANGUAGETAG As Long = &H7A
Public Const LOCALE_SSORTLOCALE As Long = &H7B
Public Const LOCALE_IDEFAULTANSICODEPAGE As Long = &H1004
Public Const LOCALE_ITIMEMARKPOSN As Long = &H1005
Public Const LOCALE_ICALENDARTYPE As Long = &H1009
Public Const LOCALE_IPAPERSIZE As Long = &H100A
Public Const LOCALE_IOPTIONALCALENDAR As Long = &H100B
Public Const LOCALE_IDEFAULTMACCODEPAGE As Long = &H1011
Public Const LOCALE_IDEFAULTEBCDICCODEPAGE As Long = &H1012
Public Const LOCALE_SSORTNAME As Long = &H1013 'Default
Public Const LOCALE_IDIGITSUBSTITUTION As Long = &H1014
'Public Month, DOW, and Day strings ----------------------------------------------------------------------------
'NOTE -- if 13 month calendar then these are ReDim Preserve to (1 to 13) so ALWAYS use LBound to UBound
Public LocaleMonths() As String 'Array of months in local language
Public LocaleMonthsAbbr() As String 'Array of month abbrevations in local language
'returns the standalone, or nominative, form of the month name
Public LocaleMonthsGenitive() As String 'Array of genitive months abbrevations in local language (
'Genitive names exist because some languages use a different case of nouns to express dates
'(genitive instead of nominative).
'in Polish nominative for January is "styczen" but to express a date 2 January
'you need to use genitive "2 stycznia".
Public LocaleDays(1 To 7) As String 'Array of days of week in local language
Public LocaleDaysAbbr(1 To 7) As String 'Array of days of week abbrevation in local language
Public LocaleDaysShort(1 To 7) As String 'Array of 2 char days of the week in local language for calendar day titles
Public LocaleDayOfWeek(1 To 7) As Long 'Array maps WeekDay function (1 to 7) to LocaleDays(x), etc.
'LocaleDays(LocaleDayOfWeek(WeekDay(Now)))
Public LocaleStartingDayOfWeek As VBA.VbDayOfWeek
Public LocaleStartingWeekOfYear As VBA.VbFirstWeekOfYear
Public LocaleLongDateFormat As String 'dddd, MMMM d, yyyy
Public LocaleShortDateFormat As String 'M/d/yyyy
Sub InitLocale(Optional LCID As String = LOCALE_NAME_USER_DEFAULT)
Dim i As Long
Dim aryFull(1 To 14) As String, aryAbbr(1 To 14) As String, aryShort(1 To 14) As String
Dim s As String
'The first day of the calendar week.
'0 = vbMonday, ... , 6 = vbSunday
LocaleStartingDayOfWeek = localeInfo(LOCALE_IFIRSTDAYOFWEEK, LCID) + 2
If LocaleStartingDayOfWeek > vbSaturday Then LocaleStartingDayOfWeek = LocaleStartingDayOfWeek - vbSaturday
'Starting week number = 1 of year
LocaleStartingWeekOfYear = localeInfo(LOCALE_IFIRSTWEEKOFYEAR, LCID)
'fill months arrays
If Len(localeInfo(LOCALE_SMONTHNAME13, LCID)) > 0 Then ' 13 month calendr
ReDim LocaleMonths(1 To 13)
ReDim LocaleMonthsAbbr(1 To 13)
ReDim LocaleMonthsGenitive(1 To 13)
LocaleMonths(13) = localeInfo(LOCALE_SMONTHNAME13, LCID)
LocaleMonthsAbbr(13) = localeInfo(LOCALE_SABBREVMONTHNAME13, LCID)
s = Format(DateSerial(Year(Now), 13, 1), "ddMMMM")
LocaleMonthsGenitive(13) = Right(s, Len(s) - 2)
Else ' 12 month calendar
ReDim LocaleMonths(1 To 12)
ReDim LocaleMonthsAbbr(1 To 12)
ReDim LocaleMonthsGenitive(1 To 12)
End If
For i = 1 To 12 'January, February ... -- Note (13) set abouve if 13 month calendar
LocaleMonths(i) = localeInfo(LOCALE_SMONTHNAME1 + i - 1, LCID)
LocaleMonthsAbbr(i) = localeInfo(LOCALE_SABBREVMONTHNAME1 + i - 1, LCID)
s = Format(DateSerial(Year(Now), i, 1), "ddMMMM")
LocaleMonthsGenitive(i) = Right(s, Len(s) - 2)
Next i
'fill day arrays
For i = 1 To 7
'1=Monday 2=Tuesday 3=Wednesday .....
aryFull(i) = localeInfo(LOCALE_SDAYNAME1 + i - 1, LCID)
aryFull(7 + i) = aryFull(i)
'1=Mon 2=Tue 3=Wed .....
aryAbbr(i) = localeInfo(LOCALE_SABBREVDAYNAME1 + i - 1, LCID)
aryAbbr(7 + i) = aryAbbr(i)
'1=Mo 2=Tu 3=We 4=Th 5=Fr 6=Sa 7=Su, 8=Mo, etc.
aryShort(i) = localeInfo(LOCALE_SSHORTESTDAYNAME1 + i - 1, LCID)
aryShort(7 + i) = aryShort(i)
Next i
'fill day of week arrays
Select Case LocaleStartingDayOfWeek
Case vbSunday ' 1
For i = vbSunday To vbSaturday
LocaleDays(i) = aryFull(6 + i)
LocaleDaysAbbr(i) = aryAbbr(6 + i)
LocaleDaysShort(i) = aryShort(6 + i)
LocaleDayOfWeek(i) = i
Next i
Case vbMonday To vbSaturday ' 2 to 7
For i = vbSunday To vbSaturday
LocaleDays(i) = aryFull(LocaleStartingDayOfWeek + i - 2)
LocaleDaysAbbr(i) = aryAbbr(LocaleStartingDayOfWeek + i - 2)
LocaleDaysShort(i) = aryShort(LocaleStartingDayOfWeek + i - 2)
Next i
For i = vbSunday To vbSaturday
LocaleDayOfWeek(i) = LocaleStartingDayOfWeek + i - 3
Next i
If LocaleDayOfWeek(vbSunday) < vbSunday Then LocaleDayOfWeek(vbSunday) = LocaleDayOfWeek(vbSunday) + vbSaturday
End Select
'Excel uses an (undocumented) escape sequence for "system default"
'This is
' [$-F800] for date, long system default
' [$-F400] for time, system default".
LocaleLongDateFormat = "[$-F800]" & localeInfo(LOCALE_SLONGDATE, LCID)
LocaleShortDateFormat = localeInfo(LOCALE_SSHORTDATE, LCID)
End Sub
'http://www.science.co.il/language/locale-codes.asp
Function localeInfo(ByVal lInfo As Long, Optional LCID As String = LOCALE_NAME_USER_DEFAULT) As String
'http://msdn.microsoft.com/en-us/library/ee825488(v=cs.20).aspx
Dim sLocaleName As String
Dim sRetBuffer As String
Dim nCharsRet As Long
If Len(LCID) > 0 Then
sLocaleName = LCID & Chr$(0)
End If
nCharsRet = GetLocaleInfoEx(StrPtr(sLocaleName), lInfo, StrPtr(sRetBuffer), 0)
If nCharsRet > 0 Then
sRetBuffer = String(nCharsRet, Chr$(0))
nCharsRet = GetLocaleInfoEx(StrPtr(sLocaleName), lInfo, StrPtr(sRetBuffer), nCharsRet)
localeInfo = Left(sRetBuffer, Len(sRetBuffer) - 1) 'Added GKM - printed result was displaying and odd AscW(0) character
End If
End Function
Reply With Quote