Data input and result on userform using function assignment

S

shabutt

I have three functions (FlatRateTax, MarginalReliefTax, MonthlyTax) in a
module and one of these functions (MonthlyTax) calculates the tax when called
from the worksheet. I want the result of (MonthlyTax) into the 2nd textbox on
the userform after I input the "MonthlySalary" argument of MonthlyTax into
1st textbox. The userform design needs to resemble this:

Monthly Salary 1st textbox
Monthly Tax 2nd textbox

Calculate Clear Cancel

The calculate commandbutton will show the result into 2nd textbox and clear
commandbutton will clear the textboxes.

Here is my code:

Function FlatRateTax(TotalIncomePerAnnum)
Select Case TotalIncomePerAnnum
Case Is > 8650000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.2, 0)
Case Is > 4550000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.19, 0)
Case Is > 3550000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.185, 0)
Case Is > 2850000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.175, 0)
Case Is > 2250000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.16, 0)
Case Is > 1950000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.15, 0)
Case Is > 1700000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.14, 0)
Case Is > 1450000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.125, 0)
Case Is > 1200000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.11, 0)
Case Is > 1050000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.1, 0)
Case Is > 900000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.09, 0)
Case Is > 750000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.075, 0)
Case Is > 650000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.06, 0)
Case Is > 550000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.045, 0)
Case Is > 450000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.035, 0)
Case Is > 400000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.025, 0)
Case Is > 350000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.015, 0)
Case Is > 250000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.0075, 0)
Case Is > 180000: FlatRateTax = WorksheetFunction.Round(TotalIncomePerAnnum
* 0.005, 0)
Case Is > 0: FlatRateTax = TotalIncomePerAnnum * 0
End Select
End Function

Function MarginalReliefTax(TotalIncomePerAnnum)
Select Case TotalIncomePerAnnum
Case Is > 8650000: MarginalReliefTax = WorksheetFunction.Round((8650000 *
0.19) + (TotalIncomePerAnnum - 8650000) * 0.6, 0)
Case Is > 4550000: MarginalReliefTax = WorksheetFunction.Round((4550000 *
0.185) + (TotalIncomePerAnnum - 4550000) * 0.6, 0)
Case Is > 3550000: MarginalReliefTax = WorksheetFunction.Round((3550000 *
0.175) + (TotalIncomePerAnnum - 3550000) * 0.5, 0)
Case Is > 2850000: MarginalReliefTax = WorksheetFunction.Round((2850000 *
0.16) + (TotalIncomePerAnnum - 2850000) * 0.5, 0)
Case Is > 2250000: MarginalReliefTax = WorksheetFunction.Round((2250000 *
0.15) + (TotalIncomePerAnnum - 2250000) * 0.5, 0)
Case Is > 1950000: MarginalReliefTax = WorksheetFunction.Round((1950000 *
0.14) + (TotalIncomePerAnnum - 1950000) * 0.4, 0)
Case Is > 1700000: MarginalReliefTax = WorksheetFunction.Round((1700000 *
0.125) + (TotalIncomePerAnnum - 1700000) * 0.4, 0)
Case Is > 1450000: MarginalReliefTax = WorksheetFunction.Round((1450000 *
0.11) + (TotalIncomePerAnnum - 1450000) * 0.4, 0)
Case Is > 1200000: MarginalReliefTax = WorksheetFunction.Round((1200000 *
0.1) + (TotalIncomePerAnnum - 1200000) * 0.4, 0)
Case Is > 1050000: MarginalReliefTax = WorksheetFunction.Round((1050000 *
0.09) + (TotalIncomePerAnnum - 1050000) * 0.4, 0)
Case Is > 900000: MarginalReliefTax = WorksheetFunction.Round((900000 *
0.075) + (TotalIncomePerAnnum - 900000) * 0.3, 0)
Case Is > 750000: MarginalReliefTax = WorksheetFunction.Round((750000 *
0.06) + (TotalIncomePerAnnum - 750000) * 0.3, 0)
Case Is > 650000: MarginalReliefTax = WorksheetFunction.Round((650000 *
0.045) + (TotalIncomePerAnnum - 650000) * 0.3, 0)
Case Is > 550000: MarginalReliefTax = WorksheetFunction.Round((550000 *
0.035) + (TotalIncomePerAnnum - 550000) * 0.3, 0)
Case Is > 500000: MarginalReliefTax = WorksheetFunction.Round((450000 *
0.025) + (TotalIncomePerAnnum - 450000) * 0.3, 0)
Case Is > 450000: MarginalReliefTax = WorksheetFunction.Round((450000 *
0.025) + (TotalIncomePerAnnum - 450000) * 0.2, 0)
Case Is > 400000: MarginalReliefTax = WorksheetFunction.Round((400000 *
0.015) + (TotalIncomePerAnnum - 400000) * 0.2, 0)
Case Is > 350000: MarginalReliefTax = WorksheetFunction.Round((350000 *
0.0075) + (TotalIncomePerAnnum - 350000) * 0.2, 0)
Case Is > 250000: MarginalReliefTax = WorksheetFunction.Round((250000 *
0.005) + (TotalIncomePerAnnum - 250000) * 0.2, 0)
Case Is > 180000: MarginalReliefTax = WorksheetFunction.Round((180000 * 0) +
(TotalIncomePerAnnum - 180000) * 0.2, 0)
Case Is > 0: MarginalReliefTax = TotalIncomePerAnnum * 0
End Select
End Function


Function MonthlyTax(MonthlySalary) As Integer
MonthlyTax = WorksheetFunction.Min(FlatRateTax(MonthlySalary * 12),
MarginalReliefTax(MonthlySalary * 12)) / 12
End Function

TIA
A novice
 
P

Patrick Molloy

add a text box called txtMonthlySalary
add a text box called monthlyTax
add a command button called btnCalculate

add this code to the cade behind the form:
Option Explicit

Private Sub btnCalculate_Click()
Dim tax As Double
txtMonthlyTax.Text = ""
tax = MonthlyTax(txtMonthlySalary.Text)
txtMonthlyTax.Text = tax
End Sub


Your functions, i keft in a standard code module. there was an overflow
error - this was because you have MonthlyTax as Integer. Change this to
Double:

Function MonthlyTax(MonthlySalary) As Double
 
S

shabutt

Thanks Mr. Patrick. I added two textboxes (txtMonthlySalary, txtMonthlyTax)
and button (btnCalculate) and it works like a charm. Thanks for your advice
regarding overflow error. I am really indebted.

Regards.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top