Format a date in VBA using a different language

Question

When I use the Format statement in VBA, I often use Excel to build the format element of the command. This usual works fine. However I want to output a date in Italian. In a spreadsheet the formula "=TEXT(NOW(), "[$-410]d mmmm yyyy")" retuns "12 aprile 2018". But if I write the following in VBA

MsgBox Format(Now(), "[$-410]d mmmm yyyy")

I get "12 April 2018" and not the Italian version that I was hoping for. My reason for asking is that I would like to create an Italian date in VBA in Access so don't want to use Excel to do it.

I guess the problem is that the format statement does not like the "[$-410]" element. Is there something else I can put in the format string to get an Italian date? Can I change a system setting in the VBA to output Italian dates and change it back again in the macro?

Rafiki · Accepted Answer · 2021-04-23 14:39:08Z

3

There is actually a solution to this, it's to use the worksheet function, as that one can be forced to use a specific language. And it is accessible in VBA through Application.WorksheetFunction :

Application.WorksheetFunction.Text(Date,  "[$-410]d mmmm yyyy")

then you can use any language settings (listed here for example)

answered Apr 23, 2021 at 14:39

Rafiki

6546 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gustav · Accepted Answer · 2018-04-12 13:51:42Z

0

VBA only "knows" the local month names. So, you may have to run your own function similar to this for the English names:

' Returns the English month name for the passed month number.
' Accepted numbers are 1 to 12. 
' If Abbreviate is True, the returned name is abbreviated.
'
' 2015-11-25. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function MonthNameInvariant( _
    ByVal Month As Integer, _
    Optional ByVal Abbreviate As Boolean) _
    As String

    Const AbbreviatedLength As Integer = 3

    Dim MonthName( _
        MinMonthValue To _
        MaxMonthValue)      As String
    Dim Name                As String

    ' Non-localized (invariant) month names.
    MonthName(1) = "January"
    MonthName(2) = "February"
    MonthName(3) = "March"
    MonthName(4) = "April"
    MonthName(5) = "May"
    MonthName(6) = "June"
    MonthName(7) = "July"
    MonthName(8) = "August"
    MonthName(9) = "September"
    MonthName(10) = "October"
    MonthName(11) = "November"
    MonthName(12) = "December"

    If Abbreviate = True Then
        Name = Left(MonthName(Month), AbbreviatedLength)
    Else
        Name = MonthName(Month)
    End If

    MonthNameInvariant = Name

End Function

answered Apr 12, 2018 at 13:51

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

2 Comments

Adrian Slater Over a year ago

Thanks - I was hoping to avoid this, but did not realise this VBA limitation. I will probably put the months in a table as this means they can be edited later for a different country.

Gustav Over a year ago

Good idea. Then add a field with language or country code to the table and filter on that.

Collectives™ on Stack Overflow

Format a date in VBA using a different language

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related