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
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