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

Application.WorksheetFunction

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

Example

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

=PERCENTILE.EXC(array,k)

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

Application.WorksheetFunction.Percentile_Exc(array,k)

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

Application.WorksheetFunction.Sum("A1:A7")

Instead, you should pass a Range

Application.WorksheetFunction.Sum(Range(“A1:A7”))

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

https://msdn.microsoft.com/en-us/library/office/ff841212.aspx

 

 

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *