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?

have you placed a break point in the function and then stepped through it to see what the error cause may be? if not, this will probably help you answer your own question. — Scott Holtzman, Jul 18 '16 at 13:49
What is happening that isn't correct, any errors? Also, have you thought of using a dictionary and the .exists function instead of vlookup. — Nathan_Sav, Jul 18 '16 at 13:55
it just return a '#VALUE!" error, and in vba (immediate window) I tried with this line `? GetLastDate("53421",AF1:AF1290,L1:L1290)` but then say " Expected list or separator ) " — Jey, Jul 18 '16 at 13:57
You need to use `Range("AF1:AF1290")` or `[AF1:AF1290]` when calling the function from the immediate window. — arcadeprecinct, Jul 18 '16 at 14:27
@arcadeprecinct it was true, I did as you said, and I received an error "Unable to get Vlookup property from Worksheetfunction class,. I going to work on this error — Jey, Jul 18 '16 at 14:37

score 0 · Accepted Answer · answered Jul 18 '16 at 14:41

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

the first one UDF was what I wanted to achieve, thank you very much — Jey, Jul 18 '16 at 14:47

user defined function doesn't work vba

1 Answers1