The WorksheetFunction Method

WorksheetFunction is a method of the Application object. It gives you access to many of the worksheet functions available in the user interface. As a general rule, you don’t get access to worksheet functions that have a VBA equivalent. For instance, you can’t get to the NOW() function because VBA has its own Now function. You can, however, get to the SUBSTITUTE() function even though VBA has Replace which almost does the same thing.

There is another way to access those functions besides WorksheetFunction. You can use them as methods of the Application object directly. Instead of writing

Application.WorksheetFunction.VLookup(“Me”, Range(“A1:A10?), 1, False)

You can simply write

Application.VLookup(“Me”, Range(“A1:A10?), 1, False)

I used to use WorksheetFunction all the time because it has one big benefit over Application alone: Intellisense.

Appwf1

But I never use it anymore. The benefits of using just Application are too good to pass up. The biggest benefit is in error handling. If you use a function like VLOOKUP() and the lookup value doesn’t exist in the lookup range, you get the N/A error. In VBA, the WorksheetFunction method throws a run time error when this happens. The error is trappable, so you can use an On Error statement to avoid it, but there’s a better way. If you use the function as a method of the Application object directly, and dimension your variable as a Variant, the variable will hold the error value and no error will occur.

Sub TheWFMethod()

    Dim x As String
    
    x = Application.WorksheetFunction.VLookup(“Sally”, Range(“A1:B10?), 2, False)
    
    Debug.Print x
    
End Sub

Sub ForgetTheMethod()

    Dim x As Variant
    
    x = Application.VLookup(“Sally”, Range(“A1:B10?), 2, False)
    
    Debug.Print x
    
End Sub

When Sally doesn’t exist in A1:A10, the first sub throws a run time error. The second sub does not, but prints Error 2042 to the Immediate Window. You can test the variable with the IsError function to see if Vlookup errored. I prefer the second method and use it exclusively. I don’t like ever having to declare variables as Variants, but in this case I let it slide.

In case you were wondering, the other benefits to avoiding WorksheetFunction is that it’s just too long of a word which makes the code lines too long. And the Intellisense you get with WorksheetFunction isn’t all that great. It saves typing the function name, but look at these really helpful arguments you get.

Appwf2

Posted in Uncategorized

26 thoughts on “The WorksheetFunction Method

  1. Hi Dick –

    I have sometimes wondered about using “Application.Whatever” instead of “WorksheetFunction.Whatever”. To have something shorter to type, I used to do this:

    Set wkfn = WorksheetFunction

    I never thought of the practical aspects of using Application. But declaring the variable as a variant to allow storing an error value is a good reason.

    IMO variants aren’t so bad. I’m sure declaring variants is less inefficient than the rest of my coding habits. And variants are nice to pass arguments to functions: a variant lets me decide at run time whether to pass an array or a single value, and as you point out, they allow an error to be stored.

    – Jon

  2. To get Intellisense, you could type

    Application.WorksheetFunction.Whatever

    and then delete the WorksheetFunction in the middle.

    P.S. What setting do I have to change to allow my personal info to be saved? Checking the box doesn’t work. Another Excel blog I read always remembers my information, but here I always have to type it all back in.

  3. “I’m sure declaring variants is less inefficient than the rest of my coding habits.”

    Me too. I just don’t want to pile on.

    “personal info to be saved?”

    Mine works. What browser are you using? Cookies disabled?

  4. I had a strange behavior when using the “WorksheetFunction” part. In the following statements,

    fcv_C = Application.WorksheetFunction.Match(vc, wbk_C.Worksheets(“Tables”).Range(“Vend”), 0)

    If IsError(fcv_C) Then
    Workbooks(wbk_vc).Close True
    MsgBox (“Not found” & vc & ” in Range Vend”)
    End If

    with “fcv_C” defined as variant, when there was not a match of “vc” in Range “Vend” (which is equivalent to say that Iserror(fcv_C) = True), I got the following situation:

    – fcv_C didn’t change its value to show that there was an error but showed the index in range “Vend” of the last sucessful match.

    – Obviously, Iserror(fcv_C) evaluated to False.

    – Never got the messagebox message.

    When I deleted the “WorksheetFunction” of the statement, thinks worked perfectly.

    Have you ever had this problem?. Why does this occur?.

    Hope this information help you to clarify us the VLOOKUP and MATCH functions’use in VBA.

  5. Carlos,

    I’ve read in NG discussions before that Vlookup behaves differently with and without the .worksheetfunction. I googled “application worksheetfunction match excel” and got this quote from Tom Ogilvy in answer to a question like yours:

    “Using WorksheetFunction as the qualifier, Match raises and error if no match is found… If you just use Application.Match you can test the result with iserror”

  6. He would have to be already using On Error Resume Next otherwise he’d get an “Unable to get the Match property…” error. But getting that error doesn’t set fcv_C to the error. It doesn’t set it to anything. If you don’t reinitialize that variable it will be whatever it was. If it’s the first time through it will be empty. You can either use Doug’s suggestion of eliminating WorksheetFunction and testing with IsError (the way I prefer) or you can use On Error Resume Next and test Err.Number <> 0.

  7. Dick –

    Yep, On Error Resume Next, then the line that’s susceptible, then test whether Err.Number <> zero. If Err.Number = xlErrNA = 2042, you can set fcv_C to “#N/A” or CVErr(xlErrNA) or blank or anything else that’s convenient. Then On Error GoTo 0, and you’re on your way.

    – Jon

  8. I am getting an error message “Unable to get VLookup Property from WorksheetFunction Class.”

    How do I get around this? Am I missing a dll or something?

  9. merci, les gars ! votre m’a été bien précieuse !

    thanks a lot, guys ! your help was very useful !

    c u

  10. bearcub, Were you able to figure out that error? I’ve got it too, also for reasons that I don’t understand.

  11. Got it. My problem was that I was vlooking up a letter when I should have been vlooking up a number.

  12. bearcub and smk, I got the same error too (unable to find…) but I was using the tdist function

    the weird thing is this code worked perfectly if I use it on another excel workbook..

    is this a bug in excel?

  13. There’s a certain paradox between this topic and the rant in ‘How to Spread Bad Habits’ since that is exactly what recommending people drop use of the worksheetfunction property does.

    When Worksheetfunction was introduced (97?) the documentation indicated it would be the future way to access all XL functions made available to VBA. The old method would be supported for existing functions.

    Because of MS’s lack of improvements to XL over the last few versions and now its commitment to dumping VB(A) I doubt there will be any new XL functions made available to VBA. So, luckily, there will be no negative consequence to propagating what should be categorized as a very bad practice.

    And, yes, I have been aware of the ‘benefit’ of not using WorksheetFunction but, personally, I find the arguments very dubious. It isn’t like people have a rational aversion to the use of On Error. So, why do people find a ‘benefit’ to not using it in the context of Worksheetfunction.Match?

  14. Hello – I get a Error2042 when I run the vlookup app … I am looking up to see if a date is in a specified column and if so need to insert a title. valdate is dimmensioned as a public date variable. When I run the vlookup code in a new sheet by itself it works but I can’t get it to work where I need it. I have attached all the preceding code for your perusal… hopefully you can get help me out …

    Public valdate As Date
    Public Rptdt As Date
    Public Masterfilname As String
    Public ExternDataName As String
    Public ExternDataLink As String
    Sub Front_Setup()

    ‘Dim valdate As Date

    ‘**** SETS REPORT AND ANALYSIS DATE
    ‘ **** THIS SECTION USES DATA UP TO ROW 22

    ‘Application.ScreenUpdating = False
    ‘Application.DisplayAlerts = False

    ‘ valdate is date to do reconciliation b/n HMS(Summit) vs BO Systems (IAS Reval Engine File)
    ‘Dim valdate As Date
    Dim valday As String
    ‘Dim Rptdt As Date

    ‘Master file
    ‘Dim Masterfilname As String
    Dim Masterfildir As String

    ‘HMS (Summit) file is downloaded from defi/summitdata/output/date/hms_files
    ‘Dim ExternDataName As String
    ‘Dim ExternDataLink As String

    Sheets(“Front”).Select

    ‘variable references to cells which contain source info
    valday = Weekday(valdate)
    valdate = Cells(Cells(4, 3), 1)

    ‘Do Until Not valday = 7
    valdate = Cells(Cells(4, 3), 1)
    valday = Weekday(valdate)
    If valday = 7 Then
    MsgBox (“It’s a Saturday. Please pick a valid date.”)
    End
    Else
    End If

    ‘Do Until valday 1
    valdate = Cells(Cells(4, 3), 1)
    valday = Weekday(valdate)
    If valday = 1 Then
    MsgBox (“It’s a Sunday. Please pick a valid date.”)
    End
    Else
    End If
    ‘Loop

    If valday = 6 Then
    Rptdt = valdate + 3
    Else
    Rptdt = valdate + 1
    End If

    Cells(6, 3) = Rptdt

    ‘Vlookup()
    ‘Dim srchVal As Date
    ‘Dim dataTable As Range
    ‘Dim colNumber As Long
    ‘Dim found As Variant

    ‘srchVal = valdate
    ‘Set dataTable = Range(“IT2:IU37?)
    ‘colNumber = 2
    ‘found = Application.VLOOKUP(srchVal, dataTable, colNumber, False)

    ‘On Error Resume Next

    ‘If IsError(found) Then
    ‘Range(“D5?).Select
    ‘Selection.ClearContents
    ‘Else

    ‘Range(“D5?).Select
    ‘ActiveCell.Formula = “MONTH-END REPORT”

    ‘End If

    ‘On Error GoTo 0

    Thanks

  15. Hello,
    I guess your issue was solved during the past 2.5 years, but if anyone stumbles here like I juste did…
    The VLookup function is boring to use with dates, because of the “real date” for excel (the cell value stored internally, not the velue displayed) vs. the date configuration for Windows (which VBA takes).

    My case was similar to yours, I sent a Date variable but it didn’t work : I had to use VLookup with CDate(myDate) and the lookup succeeded

    I read on another article that you can also send Format(myDate, “Short Date”) or Format(myDate, “Long Date”) but it didn’t work for me

  16. Pingback: Vba vlookp error


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.