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
