Simple Excel Macro throwing Run-time Error '6' Overflow

I am building an Excel Macro that will do a number of manipulations based on the oldest of three dates. For now, I only have the shell of the Macro which will only show a MsgBox.

Sub HighlightNextEDM()

Dim LastDate1 As Date
Dim LastDate2 As Date
Dim LastDate3 As Date
Dim OldestDate As Date

LastDate1 = Sheet1.Range("F11")
LastDate2 = Sheet1.Range("F12")
LastDate3 = Sheet1.Range("F13")
OldestDate = Application.WorksheetFunction.Min(LastDate1, LastDate2, LastDate3)

MsgBox "LastDate1 = " & LastDate1 & vbNewLine & _
        "LastDate2 = " & LastDate2 & vbNewLine & _
        "LastDate3 = " & LastDate3 & vbNewLine & _
        "OldestDate= " & OldestDate

If OldestDate = LastDate1 Then
    MsgBox "LastDate1 is the oldest!"
ElseIf OldestDate = LastDate2 Then
    MsgBox "LastDate2 is the oldest!"
ElseIf OldestDate = LastDate3 Then
    MsgBox "LastDate3 is the oldest!"
End If

End Sub

The value in F11 is 1/1/2025 12:25:00 PM The value in F12 is 12/15/2024 8:35:00 AM The value in F13 is 3/1/2025 9:02:00 AM

When I run the macro the first MsgBox showing all values works. However when the Macro reaches the line If OldestDate = LastDate1 Then I receive the Run-time Error '6' Overflow error.

I've tried changing the data types to Double and using the .Value option when Assigning LastDate1,2,3 and also tried to converting the values to strings but I keep getting the same error.

Any help is greatly appreciated!

Solution

Changing your variables to Double should do the trick:

Sub HighlightNextEDM()

    Dim ws As Worksheet
    Dim d1 As Double, d2 As Double, d3 As Double, minVal As Double

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' Get the raw date serials (Value2) and coerce to Double
    d1 = CDbl(ws.Range("F11").Value2)
    d2 = CDbl(ws.Range("F12").Value2)
    d3 = CDbl(ws.Range("F13").Value2)

    ' If any cell is blank or not a date, bail out early
    If d1 = 0 Or d2 = 0 Or d3 = 0 Then
        MsgBox "One or more cells do not contain a valid date/time."
        Exit Sub
    End If

    ' Use Application.Min (tolerant) instead of WorksheetFunction.Min
    minVal = Application.Min(d1, d2, d3)

    MsgBox "LastDate1 = " & CDate(d1) & vbCrLf & _
           "LastDate2 = " & CDate(d2) & vbCrLf & _
           "LastDate3 = " & CDate(d3) & vbCrLf & _
           "OldestDate= " & CDate(minVal)

    Select Case minVal
        Case d1: MsgBox "LastDate1 is the oldest!"
        Case d2: MsgBox "LastDate2 is the oldest!"
        Case d3: MsgBox "LastDate3 is the oldest!"
    End Select

End Sub