动态返回母语星期几名称
您可以使用wday
下面的函数,调用例如法国工作日通过wday("fr")
得到“Lu”(= lundi)。功能上采用国际通用模式cPattern
.
VBA-主要功能
(1) 工作日
Function wday(ByVal d As Date, ByVal lang As String) As String
' Purpose: get weekday in "DDD" format
'// e.g. Application.Worksheetfunction.Text(date(),"[$-40e]ddd")
wday = Application.WorksheetFunction.Text(d, cPattern(lang) & "ddd")
End Function
(2个月
Function mon(ByVal d As Date, ByVal lang As String) As String
'// e.g. Application.Worksheetfunction.Text(date(),"[$-40e]mmm")
mon = Application.Text(d, cPattern(lang) & "mmm")
End Function
辅助功能
Function cPattern(ByVal ctry As String) As String
' Purpose: return country code pattern for functions mon() and wday()
' Codes: https://msdn.microsoft.com/en-us/library/dd318693(VS.85).aspx
ctry = Trim(LCase(Left(ctry & " ", 3)))
Select Case ctry
Case "de"
cPattern = "[$-C07]" ' German
Case "en"
cPattern = "[$-809]" ' English UK
Case "es"
cPattern = "[$-C0A]" ' Spanish
Case "fr", "fre"
cPattern = "[$-80C]" ' French
Case "us"
cPattern = "[$-409]" ' English US
' more ...
End Select
End Function
附录(评论后编辑)
您可以使用国际国家/地区代码作为默认值ctry
内的论证cPattern
函数并将其设置为可选(应该是变体才能使用IsMissing
):
Function cPattern(Optional ByVal ctry As Variant) As String ' << optional, variant
'
If IsMissing(ctry) Then ctry = Application.International(xlCountrySetting) & "" ' << ADD if no ctry Definition
If Len(ctry) = 0 Then ctry = Application.International(xlCountrySetting) & ""
ctry = Trim(LCase(Left(ctry & " ", 3)))
Select Case ctry
'
Case "43", "de" ' << add individual Country Codes
cPattern = "[$-C07]" ' German
' ...
End Select
End Function
以类似的方式,您应该更改中的第二个参数wday
功能可选和变体:
Function wday(ByVal d As Date, optional ByVal lang) As String
If IsMissing(lang) then lang = "" ' << if 2nd arg is missing then empty string
wday = Application.WorksheetFunction.Text(d, cPattern(lang) & "ddd")
End Function
2nd Edit
通常,空模式前缀会自动显示英文书写,但这在辅助函数中被重定向wday
通过定义其他国家/地区设置(请参阅cPattern
函数如上)。
您可以按如下方式更改主要函数以包含 DDD 格式:
'(1) weekdays
Function wday(ByVal d As Date, Optional ByVal lang) As String
' Purpose: get weekday in "DDD" format
' ----------------------------
' I. If 2nd argument is missing, then use local writing
' ----------------------------
If IsMissing(lang) Then ' missing 2nd argument
wday = Format(d, "ddd")
' ----------------------------
' II. If 2nd argument exists, then search language code prefix to get any defined language
' ----------------------------
Else ' 2nd argument exists
'// e.g. Application.Worksheetfunction.Text(date(),"[$-40e]ddd")
wday = Application.WorksheetFunction.Text(d, cPattern(lang) & "ddd")
End If
End Function