Application.Match is not working correctly with dates in a VBA array

I had an issue when using Application.Match with dates in a VBA array.

Here's the code that demonstrate the problem:

    Dim MyDate As Date
    MyDate = #12/15/2024#
    
    Dim SomeDates(1 To 3) As Date
    SomeDates(1) = #12/6/2024#
    SomeDates(2) = #12/15/2024#
    SomeDates(3) = #12/22/2024#
    
    Debug.Print Application.Match(MyDate, SomeDates, 0) 'Should return 2, but returns Error 2042

This error won't occur on all computers since it will depend on how your language and region are configured on the computer that is running the code. If your short date format is set to mm/dd/yyyy which is the default in VBA, you won't have any problem, but if you have something else like in the example below, your dates won't be matching properly since Application.Match is performing string conversion under the hood for the lookup value using the computer's local configurations.

This means that for it to work properly you need to use the Cstr function to convert the date to string before passing it to the Match function.

    Dim MyDate As Date
    MyDate = #12/15/2024#
    
    Dim SomeDates(1 To 3) As Date
    SomeDates(1) = #12/6/2024#
    SomeDates(2) = #12/15/2024#
    SomeDates(3) = #12/22/2024#
    
    Debug.Print Application.Match(Cstr(MyDate), SomeDates, 0)  'Converting the date using the default VBA conversion function will fix the issue.

Thanks to Tim Williams for the help in figuring out this issue in the comments under this question.