Calling Functions

WorksheetFunction or Application Object ?

Some of the worksheet functions cannot be used from VBA since there are equivalent built-in VBA functions that do exactly the same thing.
One of these functions is the ISBLANK() function which cannot be used as there is an equivalent ISEMPTY() function provided in VBA.
If there is an equivalent VBA function then the worksheet function is not made available.

These two lines of code are identical and both return the total number of non blank cells in the current selection

Call MsgBox (Application.WorksheetFunction.CountA(Selection)) 
Call MsgBox (Application.CountA(Selection))

Since Application is considered a global member this could abbreviate it and just use:

Call MsgBox (CountA(Selection)) 

Because of this all the VBA functions are all considered to be global and can be used without qualification:

Advantages of using the Application Object

1) Intellisense - help is given for the arguments you need to provide
2) Error Handling

Example - Intellisense


Example - Error Handling

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 CallFunction1() 
    Dim x As String
    x = Application.WorksheetFunction.VLookup("Sally", Range("A1:B10"), 2, False)
    Debug.Print x
End Sub

Sub CallFunction2() 
    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.

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) 

© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext