Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Data input and result on userform using function assignment
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 31
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 31
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
InputBox vs. UserForm for Numerical Data Input Chuckles123[_55_] Excel Programming 0 November 13th 04 02:05 PM
InputBox vs. UserForm for Numerical Data Input Chuckles123[_52_] Excel Programming 1 November 13th 04 07:01 AM
InputBox vs. UserForm for Numerical Data Input Chuckles123[_54_] Excel Programming 0 November 12th 04 06:06 PM
Automatic userform data input into spreadsheet Dan Excel Programming 2 June 11th 04 04:54 PM
Automatic userform to input data to spreadsheet Dan Excel Programming 1 June 11th 04 04:54 PM


All times are GMT +1. The time now is 01:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement