Moving code to a function - not working

[troels]

This is similar to an issue i posted last year. I have an Excel formula and reference data taken from the ATO site and used to calculate PAYG tax, based on monthly income.

When I apply the code:

=ROUND((ROUND((TRUNC((3/13)*(D131+IF(ISNUMBER(SEARCH(".33",D131)),0.01,0)),0)+0.99)*(VLOOKUP((TRUNC((3/13)*(D131+IF(ISNUMBER(SEARCH(".33",D131)),0.01,0)),0)),LU2A,2))-(VLOOKUP((TRUNC((3/13)*(D131+IF(ISNUMBER(SEARCH(".33",D131)),0.01,0)),0)),

to a given Cell (A131 in the above), it works fine.

I tried to create a function to replicate the above and just keep getting "#VALUE!". Here's my function:

Function Monthly(ByVal GrossPay As Double) As Double

    Monthly = Round((Round((Application.WorksheetFunction.Trunc((3 / 13) * (GrossPay + Application.WorksheetFunction.IF(Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(".33", GrossPay)), 0.01, 0)), 0) + 0.99) * (Application.WorksheetFunction.VLookup((Application.WorksheetFunction.Trunc((3 / 13) * (GrossPay + Application.WorksheetFunction.IF(Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(".33", GrossPay)), 0.01, 0)), 0)), Range("LU2A"), 2)) - (Application.WorksheetFunction.VLookup((Trunc((3 / 13) * (GrossPay + Application.WorksheetFunction.IF(Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(".33", GrossPay)), 0.01, 0)), 0)), Range("LU2A"), 3)), 0) * (13 / 3)), 0)


End Function

Its the same code but with explicit calls to IF, Search, VLOOKUP and use of "Range".

Any ideas?

 

[pgc01]

Hi

I don't have excel here but I don't remember a method IF in the WorksheetFunction object.

I've just checked the help here and I don't see it.

https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction

Please check.