Excel VBA Application WorksheetFunction Property

This Excel VBA tutorial explains how to use Application WorksheetFunction Property in Excel VBA.

Excel VBA Application WorksheetFunction Property

Excel VBA Functions and Excel Worksheet Functions are slightly different. Sometimes even though the Function names and syntax are the same, the results are different.

There are many nifty Worksheets Functions but unfortunately they are not present in VBA, and vice versa. In order to use Worksheet Function in VBA, use Application WorksheetFunction Property.

Syntax of Application WorksheetFunction Property


E.g. Application.WorksheetFunction.Round(100.3, 0)


Example 1 – Create a custom function

Sometimes worksheet Function doesn’t work in the same way as VBA Function, Round Function is an example. Therefore we need to create a custom Function for use in VBA.

Public Function wsRound(sinput,digit)
    wsRound = Application.WorksheetFunction.Round(sinput,digit)
End Sub

Example 2 – Different Function Name

Some Functions in Excel worksheet have “dot”. For example, PERCENTILE.EXC has the below syntax


When we use WorksheetFunction Property in VBA, the dot becomes underline.


Example 3 – Use Range in WorksheetFunction

When you need to pass a Range argument in WorksheetFunction, be very careful that you have to pass a Range not text.

For example, the below Function would not work


Instead, you should pass a Range


If you need to pass Range directly from Function argument, you can directly pass it to the WorksheetFunction

Public Function test(dataRng As Range)
    test = Application.WorksheetFunction.Sum(dataRng)
End Function

Outbound References





Leave a Reply

Your email address will not be published.