WorksheetFunction and Errors

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:

The canned default message for error 1004 is a meaningless “Application-defined or object-defined error”. The message you get for a worksheet function that raises this error is arguably even more confusing: “unable to get the {function name} property of the WorksheetFunction class”.

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:

When 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.

6 thoughts on “WorksheetFunction and Errors”

  1. 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?

    Like

    1. 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 😉

      Like

  2. 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?

    Liked by 1 person

    1. 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

      Like

  3. 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.

    Like

    1. 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.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s