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.
Thanks for pointing this out. Other question about interacting with a sheet. I’m in doubt what’s the best way to do it if I need interacting with a couple of dozen cells. Here you set a variable to a range, currently I have created a ADT( abstract data class) so I can reference to a particular cell like sheetname.cell
What would you advice?
LikeLike
It really just depends what’s going on and why; feel free to put your code up for peer review on codereview.stackexchange.com (make sure there’s more than enough context to describe & justify the code), you’ll get much more actionable feedback than a hypothetical in a comment 😉
LikeLike
I’m not sure why we would use Application.WorksheetFunction. when just using Application. allows us the flexibility described in this post.
What scenario would make us prefer calling the Excel function skipping the “WorksheetFunction” member?
LikeLiked by 1 person
Thanks for the feedback, that’s a great question!
Late/early binding considerations aside (early binding: compiler sees it, Rubberduck resolves it), I prefer to raise a run-time error when… it’s the best thing to do. Let me explain.
In (very common, in my own personal experience) scenarios where we’re looking up a value *that is expected to exist*, for example when there’s a cell with a data validation dropdown and the validation list comes from a named range that refers to TheTable[Code], and you have VBA code that reads this worksheet-layer validated user input (I need to write about that) and gets the corresponding TheTable[SomeOtherColumn] value with worksheet lookup functions. If that lookup fails, there’s a bug somewhere and we *want* to let a run-time error bubble up the call stack – and take down the app if unhandled.
That, in my opinion, is what boils down the decision to this: do/can we recover from getting a Variant/Error result? If the answer is yes, then I guess your preference for early or late binding comes into play; otherwise, when a Variant/Error result means something *unexpected* and *exceptional* happened, then you want to treat an error result as a run-time error (literally an “exception” in other programming languages), and use early binding / Application.WorksheetFunction.
When the compiler sees everything, then everything is compile-time validated and you don’t need to enter sometimes intricate execution paths and application states to catch an embarrassing typo (and error 438), or a missing argument. You get IntelliSense / autocompletion and parameter quick-info tooltips, too.
Rubberduck works best when it accurately resolves exactly what’s being invoked, everywhere; uses/references are tracked across the project so you can easily locate/navigate to all references of WorksheetFunction.Match, like any other identifier anywhere in the code. Rubberduck does pick up a number of problems that are run-time errors but that VBA should really pick up at compile-time.
Now as I write this I recall that Rubberduck’s resolver also makes Excel.Application look like it’s implementing Excel.WorksheetFunction, so Rubberduck actually resolves both forms perfectly well, and knows how they’re different and how to convert late to early binding… and it can do it across the entire project with a single click!
I can see a number of compelling reasons to use either form, and now I need to go see if there’s an open issue to implement an inspection that flags early-bound WorksheetFunction member calls (perhaps at Hint level) and offers to make them late-bound.
Hope it helps, cheers!
Mathieu
LikeLike
This is very interesting: another topic you shed light on and that I did not care much about before. Thank you so much
Can we assume that every function available in “Application.WorksheetFunction.” (visible with Intellisense) has a late-bound identical version available in “Application.” ?
I try a wild guess: the WorksheetFunction object of Excel.Application is predeclared and is its indexed default member (?)
Are there other hidden objects we don’t know about because their public members do not appear through Intellisense.
LikeLike
Thanks! Most functions do and I can’t recall off the top of my head which ones don’t, but the early and the late bound functions sometimes do differ in subtle ways.
As for how the late-bound version works… COM types are extensible by default; usually the type is “sealed” and doesn’t get new members at run-time, but Excel.Application is extensible, and it’s via this mechanism that it gets (some of) the WorksheetFunction members.
LikeLike