Using Excel worksheet functions taps into the native calculation engine: using Excel’s very own MATCH
function instead of writing a lookup loop or otherwise reinventing that wheel every time makes a lot of sense if your project is hosted in Excel in the first place, or if you’re otherwise referencing the Excel type library.
You may have seen it look like this:
Dim result As Variant
result = Application.WorksheetFunction.Match(...)
Or like this:
Dim result As Variant
result = Application.Match(...)
You’ve tested both, confirmed they both work, and might be using them interchangeably in code, and all is well… until it isn’t anymore and you’re facing a cryptic run-time error:

What could this nonsense possibly mean? First, we need to understand that we’re looking at a templated error message where “property” has to have been mistakenly made part of the templated string – because we’re really looking at a function member here, but even reading the message with the correct kind of member makes no sense… until we read it as simply “the worksheet function returned a worksheet error value“: if we typed that exact same invocation in an actual worksheet cell formula, Excel’s own error-handling would do the same, and the cell would contain an #N/A
error:

MATCH
or VLOOKUP
fails in a cell, that cell’s error value propagates to any caller/cell that references it. When you invoke these functions from VBA code, it’s into your VBA code that these errors propagate now.Given bad arguments or a failed lookup, Application.WorksheetFunction.Match
and Application.Match
will behave very differently. Let us understand why and how. Note I’m going to be using a VLookup
function here, but Index
or Match
wouldn’t be any different, and everything here holds true for any other worksheet function, from the simplest Sum
to the most obscure financial function nobody ever used.
The two forms are not interchangeable, and it’s important to understand the difference!
Early Bound: Errors are Raised
When you invoke WorksheetFunction
members, errors are raised as VBA run-time errors. This means a failed lookup can be caught with an On Error
statement, as would any other run-time error.
On Error GoTo LookupFailed
Debug.Print Application.WorksheetFunction.VLookup(...)
Exit Sub
LookupFailed:
Debug.Print "..."
Resume Next
When you type these member calls, you know you’re typing early-bound code because IntelliSense (its ancestor, anyway) is listing that member in an inline dropdown:

VLookup
is a member of the object returned by the WorksheetFunction
property of the Application
object.The implication is that the function is assumed to “just work”: if using that same function with these same parameter values in an actual worksheet formula results in a #REF!
, #VALUE!
, #N/A
, #NAME?
, or any other Variant/Error
value… then the early-bound WorksheetFunction
equivalent raises run-time error 1004.
This VBA-like behavior is very useful when any failure of the worksheet function needs to be treated as a run-time error, for example when we are expecting the function to succeed every time and it failing would be a bug: throwing an error puts us on an early path to recovery.
Sometimes though, we don’t know what to expect, and a worksheet function returning an error is just one of the possible outcomes – using error handling in such cases would amount to using error handling for control flow, and that is a design smell: we should be using runtime errors for exceptional things that we’re not expecting. When a worksheet function can fail as part of normal execution, we have other options.
Late Bound: Errors are Values
When you invoke worksheet functions using late-bound member calls against an Excel.Application
object, when a function fails, it returns an error code.
Dim result As Variant
result = Application.VLookup(...)
It’s important to understand that the Variant
type means nothing in particular until it gets a subtype at runtime; result
is a Variant/Empty
until the assignment succeeds – when it does result
might be a Variant/Double
if the value is numeric; if the lookup failed, instead of raising a run-time error result
will now be a Variant/Error
value.
Operations Involving Variant/Error: Removing Assumptions
Because a failed late-bound WorksheetFunction
returns an error value, it’s easy to forget the data type of the result might not be convertible to the declared type, so the first opportunity for things to go wrong materializes if we simply assume a non-error result by declaring a non-Variant
data type for the variable that is being assigned with the function’s result:
Dim result As Long 'assumes a successful lookup...
result = Application.VLookup(...) 'runtime error 13 when lookup fails!
So we soon start systematically assigning these results to a Variant
:
Dim result As Variant
result = Application.VLookup(...)
…only to find that all we did was moving the type mismatch error further down, here:
If result > 0 Then 'runtime error 13 when result is Variant/Error!
The first thing we should do with a Variant
, is to remove any assumptions about its content. The VBA.Information.IsError
function returns True
given a Variant/Error
, and we must use it to correctly remove assumptions about what’s in this result
variable:
Dim result As Variant
result = Application.VLookup(...)
If IsError(result) Then
'lookup failed
Else
'lookup succeeded
End If
Inside the lookup failed
conditional block, result
is a Variant/Error
value that can only be compared against another Variant/Error
value – involving result
in an operation with any other runtime type will throw a type mismatch error.
Using the VBA.Conversion.CVErr
function, we can convert a Long
integer into a Variant/Error
value; the Excel object model library includes named constants for each type of worksheet error, so we can use them with the CVErr
function to refine our knowledge of what’s in result
, if we need anything more detailed than “something went wrong”:
Dim result As Variant
result = Application.VLookup(...)
If IsError(result) Then
'lookup failed
Select Case result
Case CVErr(xlErrNA)
'result is a #N/A error: value wasn't found in the lookup range
Case CVErr(xlErrRef)
'result is a #REF! error: is the lookup range badly defined?
Case Else
'result is another type of error value
End Select
Else
'lookup succeeded
End If
By systematically treating the result of a late-bound Application.{WorksheetFunction}
call as a potential Variant/Error
value, we avoid assuming success and handle a bad result without exposing our “happy path” to type mismatch errors; we then use If...Else...Then
standard control flow statements to branch execution differently depending on the outcome, using standard On Error
statements / error handling for the exceptional situations that could arise beyond these worksheet errors we’re already accounting for.
Other Variant/Error Pitfalls
The IsError
function isn’t just useful to determine whether a late-bound WorksheetFunction
call returned a usable value or not. The function returns True
given any Variant/Error
value, which makes it the perfect tool to identify worksheet cells that contain values that aren’t usable either.
Dim cell As Range
Set cell = Sheet1.Range("A1")
If cell.Value > 42 Then 'assumes cell.Value can be compared to 42!
'...
End If
VBA code often assumes cells contain valid values, and whenever that assumption is broken, a type mismatch error occurs. Unless the cell value was written by the same VBA code, it’s never really safe to assume a worksheet cell contains what the code expects it to contain. Using the IsError
function we remove such assumptions and make the code more resilient:
Dim cell As Range
Set cell = Sheet1.Range("A1")
If Not IsError(cell.Value) Then
If cell.Value > 42 Then
'...
End If
Else
MsgBox cell.Address(External:=True) & " contains an unexpected value."
End If
A Variant/Error
value can spell trouble in many other ways. Sometimes it’s an implicit conversion to String
that causes the type mismatch:
Dim cell As Range
Set cell = Sheet1.Range("A1")
MsgBox cell.Value 'assumes cell.Value can be converted to a String!
Implicit conversions can be hard to spot, but if your code is blowing up with a type mismatch error involving the value of a worksheet cell, or a value returned by a worksheet function, then that’s where you need to look.