Unable to get the VLookup property - worksheet.function error

[wal]

I'm trying to create, in VBA, a function using worksheet functions to
avoid an overly complex formula directly in worksheet cells. But I
keep getting the error "Unable to get the VLookup property of the
WorksheetFunction class"

As a simple example, here is the function if placed in the worksheet
itself (in C10):

=IF(ISNA(VLOOKUP(A10+B10,HolidayList,
1,FALSE)=FALSE),A10+B10,A10+B10+1)

The range "HolidayList" lists dates of holidays. Cell A10 is the
start date; cell B10 is the number of days from the start date; cell
C10 is the deadline I want to find. Basically: If the deadline falls
on a date in "HolidayList" add an extra day.

I converted the formula to VBA, as follows:

***
Function DeadlineDate(StartDate, NumberOfDays%)

Application.Volatile

If WorksheetFunction.IsNA(WorksheetFunction.VLookup(CLng(StartDate) +
_ NumberOfDays, Range("HolidayList"), 1, False)) = True Then
DeadlineDate = CLng(StartDate) + NumberOfDays
Else: DeadlineDate = CLng(StartDate) + NumberOfDays + 1
End If

End Function
***

In C10, the formula is now simply: =deadlinedate(A10,B10)

The problem: Whatever values are in A10 and B10, the Watch I created
for WorksheetFunction.VLookup(StartDate + NumberOfDays,
Range("HolidayList"), 2, False) shows the error value "Unable to get
the VLookup property of the WorksheetFunction class".

Interestingly, if A10 and B10 add up to a date that is found in
HolidayList, the correct deadline date appears in C10, even though the
"Unable to get . . ." error appears in the Watch.

However, if A10 and B10 add up to a date not found on the list, the
value in C10 is #VALUE! (and the "Unable to get . . ." error appears
in the Watch).

Any ideas how to correct the code to make VLookup work in all cases?
Thanks.

 

[Dave Peterson]

If you use worksheetfunction.vlookup() then VBA will cause a runtime error. But
if you use application.vlookup(), then you'll get a result that you can test to
see if it's an error.

Option Explicit
Function DeadlineDate(StartDate, NumberOfDays%)

Application.Volatile

If IsError(Application.VLookup(CLng(StartDate) + NumberOfDays, _
Range("HolidayList"), 1, False)) = True Then
DeadlineDate = CLng(StartDate) + NumberOfDays
Else
DeadlineDate = CLng(StartDate) + NumberOfDays + 1
End If

End Function

=====
Just some suggestions...

If you pass the holidaylist range to your function, then you could remove the
application.volatile line. And your function won't recalc each time excel
recalcs.

And by not passing the holidaylist range, then any change you make to that
holidaylist won't cause excel to recalc. So your function could be returning
the wrong result until excel recalcs.

And since you're really just looking for a match, you could use
application.match() instead:

Option Explicit
Function DeadlineDate(StartDate, NumberOfDays%)

Application.Volatile

If IsError(Application.Match(CLng(StartDate) + NumberOfDays, _
Range("HolidayList"), 0)) = True Then
DeadlineDate = CLng(StartDate) + NumberOfDays
Else
DeadlineDate = CLng(StartDate) + NumberOfDays + 1
End If

End Function

=========
I'd use:

Option Explicit
Function DeadlineDate(StartDate As Date, NumberOfDays As Long, _
MyHolidayList As Range)

If IsError(Application.Match(CLng(StartDate) + NumberOfDays, _
MyHolidayList, 0)) = True Then
DeadlineDate = CLng(StartDate) + NumberOfDays
Else
DeadlineDate = CLng(StartDate) + NumberOfDays + 1
End If

End Function

And call it from a cell like:
=deadlinedate(date(2008,8,24),3,HolidayList)

 

[wal]

Thanks. This is helpful. I'll try your refinements.

Although I didn't ask about it the first time around, I'm still
confused about what the volatile line is supposed to do when it's true
vs. when it's false. (The Help file is kind of cryptic.)

On my sheet, I've tried it both ways. True seems better. I'd prefer
the function (deadline dates) to recalculate when I alter the dates in
the HolidayList, and true causes that to happen. (It doesn't matter
that much, because the worksheet is intended to provide a quick
calculation of ad hoc deadline dates for the user; the data cells will
clear upon close.)

Sometimes, whether volatile is set to true or false, a #Value! error
appears in the result (deadline) cell, and F9 doesn't resolve it. I
have to enter fresh (or the same) data manually to force a
recalculation.

Thanks again.