Application.WorksheetFunction.VLookup Error

Question

Below is a function that will execute whenever I mark a loan as repaid. Its purpose is to calculate the interest accumulated over the time period the loan was outstanding. My apologies, but it's been about 10 years since I used VBA. My function is listed below. It gets the loan date and the paid date and then tries to do the VLookup for the LIBOR daily rate for each day the loan was outstanding to calculate a total cost of the loan. I have several debug.print statements still in this version to help me debug. The line that throws an error every time is:

MyDailyRate = Application.WorksheetFunction.VLookup(LIBORDayToLookup, LiborRange, 5, False)

And the error is:

1004 - Unable to get the VLookup property of the WorksheetFunction class

This function is in a class that gets called by the Worksheet_Change event. I saw several answers to this years ago, but I tried all the suggestions I came across and I continue to get the same error. Any help would be greatly appreciated.

Public Function CalculateInterest() As Boolean
    'Function Purpose:  Looping through all the days from date money was borrowed until
    'it was paid back and getting the daily LIBOR value to calculate the overall interest costs.
    Dim BorrowedDate As Date, MyWbk As Workbook, MyWs As Worksheet, MyCell As Range, RowNbr As Integer, ColNbr As Integer
    Dim DaysOfInterest As Integer, CurrentDay As Integer, LIBORDayToLookup As Date
    Dim FirstRow As Long, LastRow As Long, MyDailyRate As Variant, LiborRange As Range

'    MoneyMgmtSheet = "MONEY MGMT"
'    OrderNumber = 1
'    WireTransferDate = 2
'    AmountOfTransfer = 3
'    RepaidDate = 4
'    DaysOutstanding = 5
'    AccruedInterest = 6
'    TotalPaybackAmount = 7

    On Error GoTo ErrorHandler

    Set MyWbk = ActiveWorkbook
    Set MyWs = MyWbk.Worksheets("Money Mgmt")

    'All the fields of this class should be populated except AccruedInterest and TotalPaybackAmount when we get here.

    If Not IsDate(WireTransferDate) Then
        MsgBox "The 'Wire Transfer Date' field is NOT a date.  Unable to calculate interests!", vbCritical, "Data Error"
        CalculateInterest = False
        GoTo ExitSub
    End If

    If Not IsDate(RePaidDate) Then
        MsgBox "The 'Repaid Date' field is NOT a date.  Unable to calculate interests!", vbCritical, "Data Error"
        CalculateInterest = False
        GoTo ExitSub
    End If

    'Retrieve Start and End Dates.
    'Start by calculating the total numbers of days that interest needs to be charged.
    DaysOfInterest = DateDiff("d", WireTransferDate, RePaidDate) + 1  'Add one extra day to cover the funds actually getting transferred.

    'Determine the first and last used row in my LIBOR table.
    Set MyWs = MyWbk.Worksheets("Daily LIBOR")
    FirstRow = 2
    LastRow = MyWs.Cells(Rows.Count, 1).End(xlUp).Row
    'Columns A to E are populated. A has the Effective Date, E has the Daily LIBOR rate.
    Set LiborRange = MyWs.Range("A" & CStr(FirstRow), "E" & CStr(LastRow))

    For CurrentDay = 0 To DaysOfInterest - 1
        'Determine the current date we are going to lookup.
        LIBORDayToLookup = DateAdd("d", CurrentDay, WireTransferDate)
        'Look up the LIBOR rate for each day.
        'MyDailyRate = Application.WorksheetFunction.VLookup(LIBORDayToLookup, LiborRange, 5, False)

        'This is what the data looks like on the Daily LIBOR sheet.
        'Start Date       Day of Week    Daily Libor Rate    Flat Bank Upcharge  My Total Daily Rate
        '3/27/2020          Friday         0.13625             1.75000             1.88625
        '3/28/2020          Saturday       0.13625             1.75000             1.88625
        '3/29/2020          Sunday         0.13625             1.75000             1.88625
        '3/30/2020          Monday         0.12088             1.75000             1.87088



        Debug.Print "LIBORDayToLookup - " & LIBORDayToLookup
        Debug.Print "LiborRange - " & LiborRange.Address
        Debug.Print "MyDailyRate = Application.WorksheetFunction.VLookup(LIBORDayToLookup, LiborRange, 5, False)"
        On Error Resume Next

        MyDailyRate = Application.WorksheetFunction.VLookup(LIBORDayToLookup, LiborRange, 5, False)

        'Application.WorksheetFunction.VLookup(LIBORDayToLookup, LiborRange, 5, False)

        'MyDailyRate = Application.WorksheetFunction.VLookup(CDate("4/2/2020"), Range(A2, E39), 5, False)

        If Err.Number > 0 Then
            Debug.Print Err.Number & ", " & Err.Description
        Else
            MsgBox "SUCCESS!!!!", vbOKOnly, "CalculateInterest"
        End If
        'End
        'MsgBox "My Daily Rate: " & CStr(MyDailyRate), vbCritical, "CalculateInterest"

    Next 'DayOfInterest

    'TODO
     'Update  the interest charged for the length of time money was borrowed.

    CalculateInterest = True
    GoTo ExitSub

ErrorHandler:
    MsgBox CStr(Err.Number) & " - " & Err.Description, vbCritical, "CalculateInterest"
    CalculateInterest = False
ExitSub:
    On Error Resume Next
    Set MyCell = Nothing
    Set MyWs = Nothing
    Set MyWbk = Nothing
End Function

Here are the debug.print statement results if they will help you help me!

LIBORDayToLookup - 4/2/2020
LiborRange - $A$2:$E$39
MyDailyRate = Application.WorksheetFunction.VLookup(LIBORDayToLookup, LiborRange, 5, False)
1004, Unable to get the VLookup property of the WorksheetFunction class
LIBORDayToLookup - 4/3/2020
LiborRange - $A$2:$E$39
MyDailyRate = Application.WorksheetFunction.VLookup(LIBORDayToLookup, LiborRange, 5, False)
1004, Unable to get the VLookup property of the WorksheetFunction class
LIBORDayToLookup - 4/4/2020
LiborRange - $A$2:$E$39
MyDailyRate = Application.WorksheetFunction.VLookup(LIBORDayToLookup, LiborRange, 5, False)
1004, Unable to get the VLookup property of the WorksheetFunction class
LIBORDayToLookup - 4/5/2020
LiborRange - $A$2:$E$39
MyDailyRate = Application.WorksheetFunction.VLookup(LIBORDayToLookup, LiborRange, 5, False)
1004, Unable to get the VLookup property of the WorksheetFunction class

Have you tried using Application.Vlookup or WorksheetFunction.Vlookup instead of Application.WorksheetFunction.Vlookup? — Chronocidal

Domenic Domenic · Accepted Answer · 2020-04-16T20:11:32

As Chronocidal has already mentioned, use Application.Vlookup instead. When there's no match, a non-breaking error occurs.

First delcare the return variable as Variant, since Application.Vlookup can return a value or error...

Dim MyDailyRate as Variant

Then, change your lookup to the following...

MyDailyRate = Application.VLookup(CLng(LIBORDayToLookup), LiborRange, 5, False)

Then, test for an error as follows...

If Not IsError(MyDailyRate) Then
    MsgBox "Success!", vbExclamation
Else
    MsgBox "Not found!", vbExclamation
End If

Application.WorksheetFunction.VLookup Error

1 Answers