VBA MsgBox reformats dd/mm/yyyy Format output to dd-mm-yyyy

Question

Very new to VBA. I guess MsgBox debugging is the VBA equivalent to print statement debugging in other languages?

If I use Format to turn a date into a string using dd/mm/yyyy format, and then print that string with a MsgBox, it instead prints in dd-mm-yyyy format:

Function MsgBoxFormat()
    Const dt As Date = "2022-04-01"
    MsgBox (Format(dt, "dd/mm/yyyy"))
End Function

It only seems to do this for the results of the Format function and only if it uses dd/mm/yyyy formatting. Other formats and literal strings are not affected:

Function MsgBoxFormat()
    Const dt As Date = "2022-04-01"
    MsgBox ( _
        Format(dt, "dd/mm/yyyy") & vbNewLine & _
        Format(dt, "dd-mm-yyyy") & vbNewLine & _
        Format(dt, "yyyy-mm-dd") & vbNewLine & _
        Format(dt, "dd mmm yyyy") & vbNewLine & _
        "01/04/2022" _
    )
End Function

I'm a bit worried I've fundamentally misunderstood something about VBA here. Or could this be something obscure relating to my PC's locale settings?

This is VBA 7.1 running on an Access database.

Eh... `Debug.Print` is equal to other languages print statement debugging — Erik A, May 05 '22 at 11:53
Shouldn't be using parentheses after `MsgBox` here: it should be `MsgBox Format(dt, "dd\/mm\/yyyy")`. — BigBen, May 05 '22 at 13:01

score 2 · Accepted Answer · answered May 05 '22 at 11:05

2

That's because "/" is not a slash but the local date separator. Use force:

MsgBox (Format(dt, "dd\/mm\/yyyy"))

answered May 05 '22 at 11:05

Gustav

53,498
7
29
55

VBA MsgBox reformats dd/mm/yyyy Format output to dd-mm-yyyy

1 Answers1