Check if Range is Empty

[Bob Phillips]

Ah. I have a habit of using that, after too many times of trying things where it wouldn't work *without* it. Goofy Excel and it's coding pickiness...

It is not just coding pickiness, it is the result of evolution of the product and trying to maintain backwards compatibility.

Both methods (Application and WorksheetFunction) will work. Application was the pre Excel 97 way of doing it and is retained for compatibility, but
there are some nuances in the way they work.

There were problems with some functions in Excel97 and 2000 using the WorksheetFunction method, which did not surface using Application.

WorksheetFunction (and Application.WorksheetFunction) supports the "auto list members" option, whereas Application alone does not. In Excel 97 it was necessary to use Application.WorksheetFunction to get this auto list, but in later versions WorksheetFunction alone works.

Not all worksheet functions are exposed to VBA. Functions not exposed by the WorksheetFunction object usually have a VBA equivalent (e.g., Left, Year), but they do not necessarily work in exactly the same way.

Functions within Add-ins, such as the Analysis Toolpak, cannot be called with Application or WorksheetFunction. Errors are handled differently. When a function is called directly using Application (e.g., Application.VLookup) , the result is a variant containing an error. When called directly using WorksheetFunction (e.g., WorksheetFunction .*VLookup), the function will raise an error. Both can be managed, but in different ways

Dim ans As String
On Error Resume Next
ans = WorksheetFunction.VLookup("value", table_array, 2, False)
On Error GoTo 0
If ans = "" Then
    MsgBox "not found using WorksheetFunction"End If
If IsError(Application.VLookup("value", table_array, 2, False)) Then
    MsgBox "not found using Application"
End If

And, WorksheetFunction is faster, by an order of circa 20%.