Excel VBA Application WorksheetFunction Property

This Excel 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 fully utilize all Functions under any environment, we can do the followings:

1) To use VBA Function in Worksheet, create a User Defined Function in VBA.

For example, create the below custom Function in order to use VBA Split Function.

Public Function wSplit(sInput, seperator As String, n As Integer)
    wSplit = Split(sInput, seperator)(n)
End Function

2) On the other hand, in order to use Worksheet Function in VBA, use Application WorksheetFunction Property (see below)

Example of Application WorksheetFunction Property

Syntax

Application.WorksheetFunction

Example

Although both Worksheet Function and VBA Function have Round Function, VBA Round Function uses banker’s rounding (not the rounding we normally use in Worksheet).

For Excel Worksheet Round Function, if decimal part is >=0.5, the integer rounds up, otherwise the integer rounds down.

For Excel VBA Round Function, the Round Function uses “Round to even” logic. Integer rounds up if decimal part >=0.6, round down if <=0.4. For case decimal part exactly =0.5,  then round down, otherwise round up(round up to even integer).

If we need to use the Worksheet Round Function, we need to use Application WorksheetFunction Property as below

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

Outbound References

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