VBA Format Date Run-time 438: Object doesn't support this property or method-CodePudding

I encountered a Run-time error 438: Object doesn't support this property or method when formatting a date. It doesn't really make sense to me since the code is very straight forward.

Sub test()
    
    Dim timeStamp As String
    
    timeStamp = Format(DateTime.Now, "Long Date")
    
    MsgBox timeStamp
    
End Sub

The Format caused the error but I just don't understand why.

I tried this code in a different workbook and it works perfectly fine.

What could be the reason of this error?

CodePudding user response：

As you discovered this is a side effect of a Naming confict. In this case

Fixes are

Choose a different name for the sheet (as you have done)
Be more specific with you function calls

Sub test()
    Dim timeStamp As String
    timeStamp = VBA.Format(VBA.DateTime.Now, "Long Date")
    VBA.MsgBox timeStamp
End Sub

CodePudding user response：

So I have decided to retransfer all of my codes into a different workbook and from there I found out what truly causes of the error.

It was because the code above was put inside a sheet named "Format" (I named it Format since the purpose of the tool is to format multiple shapes).

I renamed my sheet, I know this is so stupid of me and also my negligence.