R
Rayashe
Hi. I am new to the VBA idea and am trying to learn. My Functions work fine,
but am I putting too much extra in that is not needed?
I have a sheet with C5 the contract term, C9 the total gross salary based on
contract term. Follows is my code to work out Employer NI, Tax, and Employee
NI based on the gross salary and the number of months (term):
Function EerNI(salary)
Const FreePay = 5435 'Per Year
Const Rate = 0.128
Dim Term As Integer
Term = Range("C5")
If salary > (FreePay / 12 * Term) Then EerNI = (salary - (FreePay / 12 *
Term)) * Rate
End Function
Function Tax(salary)
Dim LowRate As Double, HighRate As Double
Dim Term As Integer
Dim FreePay As Double, UpperLimit As Double
FreePay = 5435 'Per Year
UpperLimit = 36000 'Per Year
LowRate = 0.2
HighRate = 0.4
Term = Range("C5")
FreePay = FreePay / 12 * Term
UpperLimit = UpperLimit / 12 * Term
If salary < FreePay Then Tax = 0
If (salary - FreePay - UpperLimit) > UpperLimit Then Tax = UpperLimit *
LowRate _
+ (salary - FreePay - UpperLimit) * HighRate
If (salary - FreePay - UpperLimit) <= UpperLimit Then Tax = (salary -
FreePay) * LowRate
Tax = Round(Tax, 2)
End Function
Function NI(salary)
Dim Primary As Double, Upper As Double, UEL As Double
Primary = 453 'Per Month
Upper = 3337 'Per Month
UEL = Round((Upper - Primary) * 0.11, 2)
Term = Range("C5")
salary = Round(salary / Term, 2)
If salary < Primary Then NI = 0
If salary <= Upper Then NI = (salary - Primary) * 0.11
If salary > Upper Then NI = (salary - Upper) * 0.01 + UEL
NI = NI * Term
End Function
Thanks.
but am I putting too much extra in that is not needed?
I have a sheet with C5 the contract term, C9 the total gross salary based on
contract term. Follows is my code to work out Employer NI, Tax, and Employee
NI based on the gross salary and the number of months (term):
Function EerNI(salary)
Const FreePay = 5435 'Per Year
Const Rate = 0.128
Dim Term As Integer
Term = Range("C5")
If salary > (FreePay / 12 * Term) Then EerNI = (salary - (FreePay / 12 *
Term)) * Rate
End Function
Function Tax(salary)
Dim LowRate As Double, HighRate As Double
Dim Term As Integer
Dim FreePay As Double, UpperLimit As Double
FreePay = 5435 'Per Year
UpperLimit = 36000 'Per Year
LowRate = 0.2
HighRate = 0.4
Term = Range("C5")
FreePay = FreePay / 12 * Term
UpperLimit = UpperLimit / 12 * Term
If salary < FreePay Then Tax = 0
If (salary - FreePay - UpperLimit) > UpperLimit Then Tax = UpperLimit *
LowRate _
+ (salary - FreePay - UpperLimit) * HighRate
If (salary - FreePay - UpperLimit) <= UpperLimit Then Tax = (salary -
FreePay) * LowRate
Tax = Round(Tax, 2)
End Function
Function NI(salary)
Dim Primary As Double, Upper As Double, UEL As Double
Primary = 453 'Per Month
Upper = 3337 'Per Month
UEL = Round((Upper - Primary) * 0.11, 2)
Term = Range("C5")
salary = Round(salary / Term, 2)
If salary < Primary Then NI = 0
If salary <= Upper Then NI = (salary - Primary) * 0.11
If salary > Upper Then NI = (salary - Upper) * 0.01 + UEL
NI = NI * Term
End Function
Thanks.