ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Data input and result on userform using function assignment (https://www.excelbanter.com/excel-programming/427057-data-input-result-userform-using-function-assignment.html)

shabutt
Data input and result on userform using function assignment
 
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

Patrick Molloy[_2_]
Data input and result on userform using function assignment
 
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






"shabutt" wrote:

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

shabutt
Data input and result on userform using function assignment
 
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.

"Patrick Molloy" wrote:

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






"shabutt" wrote:

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


All times are GMT +1. The time now is 05:04 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com