VBA get max date from the format YYYY-DD-MM (like 2023-10-02)

Question

In VBA I want to extract the max date (or max value) from a range, but I don't want to change the format of the range. Now it looks like below:

Date:
2023-10-02
2023-10-03
2023-10-04
2023-10-05
2023-10-06
2023-10-07

But the normal max function is not working in VBA...

There are also empty cells in this column, so it would be good to not consider these ones.

Thank you in advance and best regards!

Answer 1

This function can be used both in a worksheet and in VBA

Option Explicit

Function GetMaxDate(rng As Range) As Variant
    Dim cur_date As Date, arr As Variant, d As Variant
    arr = rng ' get all the data into an array to improve performance (one operation of reading data from the sheet)
    For Each d In arr
        If IsDate(d) Then ' we check whether the next value can be a date
            cur_date = CDate(d) '
            If GetMaxDate < cur_date Then GetMaxDate = cur_date ' select max date
        End If
    Next
    If Not IsEmpty(GetMaxDate) Then
        GetMaxDate = Format(GetMaxDate, "yyyy-mm-dd")
    Else
        GetMaxDate = "#NODATE"
    End If
End Function