Problem debugging a Function - code works fine when embedded

troels

New Member
Joined
Jan 11, 2018
Messages
5
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:

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.

Code:
=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)
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

troels

New Member
Joined
Jan 11, 2018
Messages
5
Just noticed an error. I've revised the function return to:

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

New Member
Joined
Jan 11, 2018
Messages
5
Okay after some more research I've made it work by doing the following:

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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,049
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 11, 2018
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top