Problem debugging a Function - code works fine when embedded

[troels]

I am trying to create a function which returns the amount of tax based on a gross pay amount. This uses a lookup on data stored in worksheet.

When I use the code directly in my main worksheet it works fine but its a long formula which I thought could be better implemented as a function.

First issue was that the TRUNC function didn't work in the function so I wrote my own - that now works okay. However something else (I suspect the vlookup) is not now worknig and the function is always returning #VALUE !.

I can't figure out how to debug the function so I've tried to break it down a little to see at which point it goes wrong. The Trunc is working fine. LU_Scale2 is a range whose value is ='NAT1006 2017'!$A$29:$C$43

LU_Scale2 is the actual range for the lookup data.

Anyway here is the code:

Function PAYGFortnightly(ByVal GrossPay As Double) As Double

    Dim tmp As Double
    
    tmp = Trunc(GrossPay / 2, 0)


    PAYGTaxFortnightly = Round((tmp + 0.99) * (ActiveSheet.VLookup(tmp, LU_Scale2, 2)) - ActiveSheet.VLookup(tmp, LU_Scale2, 3), 0) * 2
      
End Function


Public Function Trunc(ByVal value As Double, ByVal num As Integer) As Double
  Trunc = Int(value * (10 ^ num)) / (10 ^ num)
End Function

I thought perhaps VLookup wouldn't work (like the issue I had with Trunc) which is why I added ActiveSheet - not sure if this is correct and whether this is the source of the issue?

This is what the code looks like when directly embedded in my target worksheet and applied to cell L13 (for example). As I said, this works fine.

=IF(ISNUMBER(L13)=FALSE,"-",ROUND((TRUNC((L13/2),0)+0.99)*(VLOOKUP((TRUNC((L13/2),0)),LU_Scale2,2))-VLOOKUP((TRUNC((L13/2),0)),LU_Scale2,3),0)*2)

 

[troels]

Just noticed an error. I've revised the function return to:

PAYGFortnightly = Round((tmp + 0.99) * (ActiveSheet.VLookup(tmp, LU_Scale2, 2)) - ActiveSheet.VLookup(tmp, LU_Scale2, 3), 0) * 2

Now I get #NAME ! as the return value

 

[troels]

Okay after some more research I've made it work by doing the following:

Function PAYGFortnightly(ByVal GrossPay As Double) As Double

    Dim tmp As Double
    tmp = Trunc(GrossPay / 2, 0)
    PAYGFortnightly = Round((tmp + 0.99) * (Application.WorksheetFunction.VLookup(tmp, Sheets("NAT1006_2017").Range("$A$29:$C$43"), 2)) - Application.WorksheetFunction.VLookup(tmp, Sheets("NAT1006_2017").Range("$A$29:$C$43"), 3), 0) * 2


End Function

Is there any way I can get my range value (LU_Scale2) to work instead of hard-coding the sheet name and cell references in here?

 

[StephenCrump]

Welcome to the Forum!

You should just need to replace both occurrences of:

Sheets("NAT1006_2017").Range("$A$29:$C$43")

with

Range("LU_Scale2")

By the way, if you are always using Trunc(Value, 0) then couldn't you simply use VBA's Int() function instead?

 

[troels]

Perfect, thanks.

Yes you are correct on the Trunc thing too - I didn't write to original code, I'm guessing there are instances in the original spreadsheet where the value is non-zero but for my purposes INT works fine.