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.
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.
Dim x As String
x = Application.WorksheetFunction.VLookup(“Sally”, Range(“A1:B10?), 2, False)
Dim x As Variant
x = Application.VLookup(“Sally”, Range(“A1:B10?), 2, False)
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.