1 / 24

Extra 8

Extra 8. Yes this can be posted. Problem. Calculate the medical expense for an employee. Level 0 Basic Medical, Employee pays 1% of gross income Level 1 Allows for semi-private hospital, and eye ware Employee pays Base Medical and an additional $10 per pay Level 2

oceana
Download Presentation

Extra 8

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Extra 8 Yes this can be posted

  2. Problem • Calculate the medical expense for an employee. • Level 0 • Basic Medical, • Employee pays 1% of gross income • Level 1 • Allows for semi-private hospital, and eye ware • Employee pays Base Medical and an additional $10 per pay • Level 2 • Allows for private hospital eye ware and dental • Employee pays Base Medical and an additional $25 per pay

  3. Algorithm • Name – Medical • Given – Income, Level • Results – Payment • Get Income, Level • Payment = 1% of Income • If Level = 1 • Payment = Payment + 10 • Else If Level = 2 • Payment = Payment + 25 • Give Payment

  4. Option Explicit 'Written by T. James Sub A5Q2S1() Dim Name as String Dim Enumber as String Dim Address as String Dim Income as Single Dim Level as Integer Dim Payment as Single 'Inputs Name = InputBox("Name") Enumber = Inputbox("Number") Income = InputBox("Income") Level = InpuBox("Medical Level") 'Medical Expense Payment = 0.01 * Income If (Level = 1) Then Payment = Payment + 10 ElseIf (Level = 2) Then Payment = Payment + 25 End If MsgBox(Name & " " & _ Enumber & Chr(13) & _ "I:" & Income & Chr(13) & _ "L:" & Level & Chr(13) & _ "P:" & Payment) End Sub Solution 1Single Record

  5. Solution 2 Multiple Records • Make the Changes to allow for multiple records to be entered by the user

  6. Solution 2Multiple Records • 4 Changes • Add a variable • Dim Again as Integer • Start a loop before the Gets • Do • Name = InputBox("Name") • Get Again at bottom of loop • MsgBox(….) • Again = MsgBox("Again",4) • Finish the Loop • Again = MsgBox("Again, 4") • Loop Until (Again = 7)

  7. Option Explicit 'Written by T. James Sub A5Q2S1() Dim Name as String Dim Enumber as String Dim Address as String Dim Income as Single Dim Level as Integer Dim Payment as Single Dim Again as Integer Do 'Inputs Name = InputBox("Name") Enumber = Inputbox("Number") Income = InputBox("Income") Level = InpuBox("Medical Level") 'Medical Expense Payment = 0.01 * Income If (Level = 1) Then Payment = Payment + 10 ElseIf (Level = 2) Then Payment = Payment + 25 End If MsgBox(Name & " " & _ Enumber & Chr(13) & _ "I:" & Income & Chr(13) & _ "L:" & Level & Chr(13) & _ "P:" & Payment) Again = MsgBox("Again",4) Loop Until (Again = 7) End Sub Solution 2Multiple Records

  8. Solution 3Excel Definite Loop • Change the program to work with an excel worksheet • Data in Rows 5 to 10 • Data Layout • Col A – Employee Number • Col B – Name • Col C – Income • Col D – Medical Level • Col E – Medical Payment • Worksheet Nam is Med1

  9. Solution 3Excel Definite Loop • 6 Changes • Add a variable • Dim Row as Integer • Go to worksheet • Dim Row as Single • Worksheets("Med1").Activate • Start Loop • For Row = 5 to 10 • Name = InputBox("Name") • Change Gets • Name = InputBox("Name") becomes • Name = Cells(Row, 2) • Repeat for Emp Number, and Level • Change Gives • MsgBox(…) Becomes • Cells(Row, 5) = Payment • Finish Loop • Cells(Row, 5) = Payment • Next Row

  10. Option Explicit 'Written by T. James Sub A5Q2S1() Dim Name as String Dim Enumber as String Dim Address as String Dim Income as Single Dim Level as Integer Dim Payment as Single Dim Row as Integer Worksheets("Med1").Activate For Row = 5 to 10 'Inputs Name = Cells(Row, 2) Enumber = Cells(Row, 1) Income = Cells(Row, 3) Level = Cells(Row, 4) 'Medical Expense Payment = 0.01 * Income If (Level = 1) Then Payment = Payment + 10 ElseIf (Level = 2) Then Payment = Payment + 25 End If Cells(Row, 5) = Payment Next Row End Sub Solution 3Excel Definite Loop

  11. Solution 4Excel Indefinite Loop • Change the solution to allow for any number of records • Every employee must have an Employee Number

  12. Solution 4Excel Indefinite Loop • 2 Changes for loop • For Row • For Row = 5 to 10 BECOMES • Row = 5 • Do Until (IsEmpty(Cells(Row, 1))) • Next Row • Next Row BECOMES • Row = Row + 1 • Loop

  13. Option Explicit 'Written by T. James Sub A5Q2S1() Dim Name as String Dim Enumber as String Dim Address as String Dim Income as Single Dim Level as Integer Dim Payment as Single Dim Row as Integer Worksheets("Med2").Activate Row = 5 Do Until(IsEmpty(Cells(Row,1))) 'Inputs Name = Cells(Row, 2) Enumber = Cells(Row, 1) Income = Cells(Row, 3) Level = Cells(Row, 4) 'Medical Expense Payment = 0.01 * Income If (Level = 1) Then Payment = Payment + 10 ElseIf (Level = 2) Then Payment = Payment + 25 End If Cells(Row, 5) = Payment Row = Row + 1 Loop End Sub Solution 4Excel Indefinite Loop

  14. Solution 5Calling a Subroutine • Change the program to call the medical payment as a Subroutine

  15. Solution 5Calling a Subroutine • Sub Routine Changes • This is where Givens/Results from algorithms become important • All have just one result, at very least note this (will need for Function Changes) • Although names don't matter, keep it simple; • Reuse the same names as before

  16. Solution 5Calling a Subroutine • 3 Changes • Create a Header • Sub Name (ByVal Givens, ByRef Result) • Create the Body • CUT and PASTE from main program • Create the Call Statement • If you use the same variable names then Call becomes copy past of the Sub Header, remove all the As.

  17. Name – Medical Given – Income, Level Results – Payment Dim Income as Single Dim Level as Integer Dim Payment as Single Sub Medical (ByVal Income as Single, _ ByVal Level as Integer, _ ByRef Payment as Single) Payment = 0.01 * Income If (Level = 1) Then Payment = Payment + 10 ElseIf (Level = 2) Then Payment = Payment + 25 End If End Sub ------ Call Medical(Income, Level, Payment) Solution 5Calling a Subroutine

  18. Option Explicit 'Written by T. James Sub A5Q2S1() Dim Name as String Dim Enumber as String Dim Address as String Dim Income as Single Dim Level as Integer Dim Payment as Single Dim Row as Integer Worksheets("Med3").Activate Row = 5 Do Until(IsEmpty(Cells(Row,1))) 'Inputs Name = Cells(Row, 2) Enumber = Cells(Row, 1) Income = Cells(Row, 3) Level = Cells(Row, 4) 'Medical Expense Call Medical(Income, Level, Payment) Cells(Row, 5) = Payment Row = Row + 1 Loop End Sub Sub Medical (ByVal Income as Single, _ ByVal Level as Integer, _ ByRef Payment as Single) Payment = 0.01 * Income If (Level = 1) Then Payment = Payment + 10 ElseIf (Level = 2) Then Payment = Payment + 25 End If End Sub Solution 5Calling a Subroutine

  19. Solution 6Using a Function Visual Basic • Change the system to use a function instead of a subroutine

  20. Solution 6Using a Function Visual Basic • Rewrite a Sub as a Function • 4 Changes • Change ByRef to make it a Dim • Sub(… ByRef X as ???) BECOMES • Dim X as ??? • Change Sub to Function • As same type as Dim • Sub Name (…) BECOMES • Function Name (…) As ??? • Add "Name" = "Dim Variable" as last line • Name = X • Change Call to be a Function • Call Name (A, B, C) BECOMES • C = Name (A, B)

  21. Name – Medical Given – Income, Level Results – Payment Dim Income as Single Dim Level as Integer Dim Payment as Single Function Medical (ByVal Income as Single, _ ByVal Level as Integer, _ ) as Single Dim Payment as Single Payment = 0.01 * Income If (Level = 1) Then Payment = Payment + 10 ElseIf (Level = 2) Then Payment = Payment + 25 End If Medical = Payment End Function ------ Payment = Medical(Income, Level) Solution 6Using a Function Visual Basic

  22. Option Explicit 'Written by T. James Sub A5Q2S1() Dim Name as String Dim Enumber as String Dim Address as String Dim Income as Single Dim Level as Integer Dim Payment as Single Dim Row as Integer Worksheets("Med4").Activate Row = 5 Do Until(IsEmpty(Cells(Row,1))) 'Inputs Name = Cells(Row, 2) Enumber = Cells(Row, 1) Income = Cells(Row, 3) Level = Cells(Row, 4) 'Medical Expense Payment = Medical(Income, Level) Cells(Row, 5) = Payment Row = Row + 1 Loop End Sub Function Medical (ByVal Income as Single, _ ByVal Level as Integer) _ as Single Dim Payment as Single Payment = 0.01 * Income If (Level = 1) Then Payment = Payment + 10 ElseIf (Level = 2) Then Payment = Payment + 25 End If Medical = Payment End Function Solution 6Using a Function Visual Basic

  23. Solution 7Using a Function Excel • Use your Custom Function on an Excel Worksheet • Data Layout • Col A – Employee Number • Col B – Name • Col C – Income • Col D – Medical Level • Col E – Medical Payment • This is the easiest • E5 = Medical(C5, D5) • Copy/Past Down

  24. Solution 8No Visual Basic at all • E5 = If(D5 = 0, 0.01 * C5, If(D5 = 1, 0.01 * C5 + 10, 0.01 * C5 + 25) • Not very nice but play the "what if" game • A new level is added, Level 3, with a 1% of income premium, but it pays for life insurance • The base rate gets changed to 1.5% • The premium for level 2 is raised to $30

More Related