Posted inTips and Tricks User Defined Function

Is the WorksheetFunction property necessary in Excel VBA?

February 9, 2018
Is the WorksheetFunction property necessary in Excel VBA?

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?

Further Reading

  1. For a list of functions that are built into VBA, check the Visual Basics for Applications Language Reference for Office.
  2. For a list of functions that are built into Excel, check the Office Support article on “Excel functions (by category)“.
  3. For another account on the same topic, check “The WorksheetFunction Method” on Daily Dose of Excel.
  4. 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

Download the following Workbook for all the illustrative code in this post:

Download

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

 

Tags: