Excel’s Application object’s WorksheetFunction property is a container for Microsoft Excel worksheet function. This property returns a WorksheetFunction object that allows VBA access to the rich set of functions that are built into Excel. While VBA has some generic functions of its own, Excel’s set is much bigger, and more suited for (you guessed it) Excel. As soon as you type in the dot after WorksheetFunction, you will see a list of Excel functions that you can use in your VBA code. But do you really need the WorksheetFunction object?
What many users do not know is that you can use Excel’s functions directly from the Application object itself.
Sub TheRedundancy() 'The following line.. Debug.Print Application.Min(1, 2) 'works just the same as Debug.Print Application.WorksheetFunction.Min(1, 2) End Sub
What is the difference?
Both the forms are equivalent, except for what would happen when the function returns an error. Try running the following code and check what happens.
Sub TheDifference() 'Let us force the functions to return an error 'and see what happens 'Without the object Debug.Print Application.Min(CVErr(xlErrNA), 1) 'With the object Debug.Print Application.WorksheetFunction.Min(CVErr(xlErrNA), 1) End Sub
When you use the WorksheetFunction object, you’ll notice that VBA displays a “Unable to get the Min property of the WorksheetFunction class” runtime error, and stops the execution. However, when you leave it out, it returns the error as a result, and proceeds with the next line. This is the only material difference between the two forms. However, there is also a practical difference in terms of how you would handle an error, should the function return one.
Now let us take an example, and try to understand the practical difference. When you use the object, you have to employ any of the runtime error handling techniques. I recommend you use the On Error Resume Next statement to suppress the error should it happen, and then check whether an error occurred, using the Err object.
Sub UseTheWorksheetFunctionObject() Dim LookFor As Integer Dim LookIn() As Variant Dim Result As Long 'We will try to match the LookFor variable in the 'LookIn array LookFor = 40 LookIn = Array(1, 2, 3, 4, 5) 'Experiment with different values for LookFor and 'see what happens 'We will suppress the error using the On Error Resume Next 'Statement On Error Resume Next Result = Application.WorksheetFunction.Match(LookFor, LookIn, 0) 'Use the Err Object to check whether an error happened If Err.Number = 0 Then 'Display the result if an error did not happen MsgBox "Match: " & Result, vbInformation Else 'Display an error message if an error happened MsgBox "Match not found.", vbCritical 'Reset the Error Suppressing Settings Err.Clear On Error GoTo 0 End If End Sub
Alternatively, you can drop the object, store the result in a variable, and use the IsError function to test whether an error occurred.
Sub DropTheWorksheetFunctionObject() Dim LookFor As Integer Dim LookIn() As Variant Dim Result As Variant LookFor = 40 LookIn = Array(1, 2, 3, 4, 5) Result = Application.Match(LookFor, LookIn, 0) If IsError(Result) Then MsgBox "Match not found.", vbCritical Else MsgBox "Match: " & Result, vbInformation End If End Sub
Note that the data type of the variable used to store the result has to be Variant. Otherwise, you’d encounter a “Type mismatch” runtime error.
Personally, I prefer leaving the object out, because I try not to use ON ERROR unless I absolutely have to.
Why use the object then?
Some people prefer the list of available functions dropping down as soon as you type in the dot (IntelliSense – Intelligent Code Completion).
While I too prefer the comfort of IntelliSense, the actual names of the function’s arguments do not reveal themselves. They turn up as Arg1, Arg2, and so on. Not that great an advantage isn’t it?
There is no right or wrong way. It’s a matter of preference really. I’d rather not use it. Would you?
Wrapping things up…
Having to explicitly handle errors each time you have to use an Excel Function is a nightmare. I recommend you write a wrapper function in VBA for the Excel functions you use frequently. I will share my favourite with you:
Function xlMatch(ByVal Value As Variant, _ ByVal LookIn As Variant, _ Optional ByVal MatchType As Integer = 0) As Long Dim Result As Variant Result = Application.Match(Value, LookIn, MatchType) xlMatch = IIf(IsError(Result), 0, CLng(Result)) End Function
The Match Type is optional, and is set to give you an exact match by default. When the function returns a 0, you’ll know a match was not found. Elegant isn’t it?
- For a list of functions that are built into VBA, check the Visual Basics for Applications Language Reference for Office.
- For a list of functions that are built into Excel, check the Office Support article on “Excel functions (by category)“.
- For another account on the same topic, check “The WorksheetFunction Method” on Daily Dose of Excel.
- For an account on my preferences when it comes to handling errors in Excel, check my post “To Err is Excel, Handle your Errors with grace“.
Download the following Workbook for all the illustrative code in this post:
A tip for you naysayers
Another reason for why I do not use the WorksheetFunction object is because it is just too long to type out. If you are still adamant about using the WorksheetFunction object, at least consider the following tip.
Sub TheShortCut() 'Declare a WorksheetFunction Object of your own 'Preferably with a really small name Dim XL As WorksheetFunction 'Set the object to the Application object's 'WorksheetFunction Property Set XL = Application.WorksheetFunction 'Use it as you please! Debug.Print XL.Min(3, 2) End Sub