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

Question

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!

This code is working for me without issue. Please add the version of your Excel to the question. FYI: Date type is an 8-byte(64 bit) stored type. — Black cat
– Black cat, Commented Aug 8 at 3:41

Michal · Accepted Answer · 2025-08-07 21:03:35Z

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

Jerry · Accepted Answer · 2025-08-07 21:01:07Z

Sub HighlightNextEDM()

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

    ' Force conversion to Date
    LastDate1 = CDate(Sheet1.Range("F11").Value)
    LastDate2 = CDate(Sheet1.Range("F12").Value)
    LastDate3 = CDate(Sheet1.Range("F13").Value)

    ' Find the oldest date
    OldestDate = Application.WorksheetFunction.Min(LastDate1, LastDate2, LastDate3)

    ' Show all values
    MsgBox "LastDate1 = " & LastDate1 & vbNewLine & _
           "LastDate2 = " & LastDate2 & vbNewLine & _
           "LastDate3 = " & LastDate3 & vbNewLine & _
           "OldestDate = " & OldestDate

    ' Compare using DateSerial and TimeSerial to avoid precision issues
    If LastDate1 = OldestDate Then
        MsgBox "LastDate1 is the oldest!"
    ElseIf LastDate2 = OldestDate Then
        MsgBox "LastDate2 is the oldest!"
    ElseIf LastDate3 = OldestDate Then
        MsgBox "LastDate3 is the oldest!"
    End If

End Sub

See if the above solves your problem. Let us know.

VBasic2008 · Accepted Answer · 2025-08-08 05:13:25Z

Locate Minimum Date in Range

Sub LocateMinimumDate()
    
    Const GET_LAST_MINIMUM_DATE As Boolean = False
    
    ' Either (continuous range) ...
    Dim rg As Range: Set rg = Sheet1.Range("F11:F13")
    ' ... or (discontinuous range, e.g., "A11,B15,C13") :
    'Dim rg As Range: Set rg = Sheet1.Range("F11,F12,F13")

    Dim cell As Range, mcell As Range, Value As Variant, MinDate As Date
    Dim WasFirstDateFound As Boolean, IsDateSmaller As Boolean
    
    For Each cell In rg.Cells
        Value = cell.Value
        IsDateSmaller = False ' reset on each iteration
        If IsDate(Value) Then
            If WasFirstDateFound Then
                If GET_LAST_MINIMUM_DATE Then
                    IsDateSmaller = (Value <= MinDate)
                Else
                    IsDateSmaller = (Value < MinDate)
                End If
            Else
                IsDateSmaller = True
                WasFirstDateFound = True ' never reset
            End If
        End If
        If IsDateSmaller Then
            Set mcell = cell
            MinDate = Value
        End If
    Next cell
    
    If Not WasFirstDateFound Then
        MsgBox "No dates found in ""'" & rg.Worksheet.Name & "'!" _
            & rg.Address(0, 0) & """!", vbExclamation
        Exit Sub
    End If
    
    MsgBox "The miniumum date """ & Format(MinDate, "mm/dd/yyyy hh:mm:ss") _
        & """ was found in cell ""'" & mcell.Worksheet.Name & "'!" _
        & mcell.Address(0, 0) & """.", vbInformation
    
End Sub

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Locate Minimum Date in Range

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Locate Minimum Date in Range

Comments

Your Answer

Sign up or log in

Post as a guest

Related