CODEWITHSUNDEEP
vbams-access
Adrian Slater
Adrian Slater

Reputation: 36

Format a date in VBA using a different language

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?

Upvotes: 1

Views: 4791

Answers (2)

Rafiki
Rafiki

Reputation: 644

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)

Upvotes: 3

Gustav
Gustav

Reputation: 55841

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

Upvotes: 0

Related Questions