Please Help me fix a ‘hopefully’ simple ‘Type Mismatch’

[Barkworth]

I have a simple macro which essentially checks if the below specified date is the is older than the last modified date of a master file saved in a shared area.

But for some reason when I run it, it get “type mismatch”… but why

Might be worth noting I have the exact same code in the ‘This Workbook’ section of the add-in, which runs on ‘Workbook_Open’. Is that an issue?

Public Sub UpdateCheck(control As IRibbonControl)

 

Dim CurrentVersionDate As Date

CurrentVersionDate = ("18/11/2021") '<---- MUST UPDATE ON DAY OF NEW VERSION RELEASE IN MM/DD/YYY FORMAT

 

Dim LastUpdateDate As Date

LastUpdateDate = Int(FileDateTime("P:\SCG\Macros\CommoditiesSuite.xlam"))

 



If LastUpdateDate > CurrentVersionDate Then

    If MsgBox("Last Updated on: " & CurrentVersionDate & vbNewLine & "Latest Available Version: " & LastUpdateDate & vbNewLine & vbNewLine & "Your CommoditiesSuite version is out of date..." & vbNewLine & "Do you want to update?", vbYesNo, "Version Check") = vbYes Then

        Workbooks.Open ("P:\SCG\Macros\INSTALL.xlsm")

        Else

        Exit Sub

        End If

        

Else

Exit Sub

End If

 

[GWteB]

CurrentVersionDate = ("18/11/2021") '<---- MUST UPDATE ON DAY OF NEW VERSION RELEASE IN MM/DD/YYY FORMAT

You cannot force VBA to accept a certain date format. A date in Excel is actually a number. The format of a date is just for displaying purposes. VBA has a CDate function to convert a text string like "11/18/2021" into a date number but that sometimes goes wrong dependent on regional settings. Users sometimes get confused at times they intend to convert a string like "11/4/2021" to the date of November 4th but end up with a date of April 11th.
Would recommend to use the DateSerial function or. That way days and months never get switched unexpectedly.

CurrentVersionDate = VBA.DateSerial(2021, 11, 18)    ' << fixed order: year, month, day

 

[dmt32]

try using either of the VBA functions

DateValue(Date String) - where Date String is a string that represents a date such as "18/11/2021"

or

DateSerial(Year, Month, Day)

Dim CurrentVersionDate As Date

CurrentVersionDate = DateValue("18/11/2021")

CurrentVersionDate = DateSerial(2021, 11, 18)

Both functions should return the corresponding Date number

Dave