user defined function doesn't work vba

Question

i have this udf and basically what I want to get is the latest date from a vector (column) that match with other data in other column, here's the code:

Option Explicit
Public Function GetLastDate(Carrier As String, CarrierVector As Range, DateVector As Range) As Variant
    Dim TempRange(1 To 10) As Variant
    Dim i, j As Integer
    For i = 1 To DateVector.Rows.Count
        With Application.WorksheetFunction
            If .Text(CarrierVector.Item(i), "#") = Carrier And .IsError(.VLookup(DateVector.Item(i), TempRange, 1, False)) Then
                j = j + 1
                TempRange(j) = DateVector.Item(i)
            End If
        End With
    Next i
    GetLastDate = Application.WorksheetFunction.Max(TempRange)
End Function

in this case, if the Carrier variable is found on CarrierVector, the Date corresponding to that carrier will be storage on TempRange array, (this if it's not repeated) and at the end, it will return the lattest Date, but this doesn't work, it just return an error on the cell from where the function is called, could you please help me?

Answer 1

The problem seems to be with the VLookUp call. I don't know why it fails, but I also think it is not recommendable to use worksheet functions inside VBA, unless there is no good alternative.

But in this case, you could use a Collection to keep track of a unique set of dates you already had:

Public Function GetLastDate(Carrier As String, CarrierVector As Range, 
                            DateVector As Range) As Variant
    Dim TempRange As New Collection
    Dim i As Integer
    Dim found As Boolean
    Dim max As Date
    For i = 1 To DateVector.Rows.Count
        With Application.WorksheetFunction
            If .Text(CarrierVector.Item(i), "#") = Carrier Then
                On Error Resume Next
                    found = TempRange.Item(DateVector.Item(i) & "")
                On Error GoTo 0
                If Not found Then
                    TempRange.Add DateVector.Item(i), DateVector.Item(i) & ""
                    If DateVector.Item(i) > max Then max = DateVector.Item(i)
                End If
            End If
        End With
    Next i
    GetLastDate = max
End Function

Still, I am not sure why you need to use this TempRange at all, since you are not using it for anything else. You might as well completely drop it:

Public Function GetLastDate(Carrier As String, CarrierVector As Range,
                            DateVector As Range) As Variant
    Dim i As Integer
    Dim found As Boolean
    Dim max As Date
    For i = 1 To DateVector.Rows.Count
        With Application.WorksheetFunction
            If .Text(CarrierVector.Item(i), "#") = Carrier Then
                If DateVector.Item(i) > max Then max = DateVector.Item(i)
            End If
        End With
    Next i
    GetLastDate = max
End Function