# Integer Worksheets

Document Sample

```					     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
– Allows for private hospital eye ware and dental
– Employee pays Base Medical and an additional \$25 per pay
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
Solution 1
Single Record
Option Explicit
'Written by T. James                   'Medical Expense
Sub A5Q2S1()
Dim Name      as String              Payment = 0.01 * Income
Dim Enumber   as String
Dim Address   as String              If (Level = 1) Then
Dim Income    as Single                Payment = Payment + 10
Dim Level     as Integer             ElseIf (Level = 2) Then
Dim Payment   as Single                Payment = Payment + 25
End If
'Inputs
MsgBox(Name & " " & _
Name = InputBox("Name")                     Enumber & Chr(13) & _
Enumber = Inputbox("Number")                 "I:" & Income & Chr(13) & _
Income = InputBox("Income")                  "L:" & Level & Chr(13) & _
Level = InpuBox("Medical Level")             "P:" & Payment)
End Sub
Solution 2
Multiple Records
 Make the Changes to allow for multiple records to
be entered by the user
Solution 2
Multiple Records
 4 Changes
• 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)
Solution 2
Multiple Records
Option Explicit
'Written by T. James                   'Medical Expense
Sub A5Q2S1()
Dim Name      as String              Payment = 0.01 * Income
Dim Enumber   as String
Dim Address   as String              If (Level = 1) Then
Dim Income    as Single                Payment = Payment + 10
Dim Level     as Integer             ElseIf (Level = 2) Then
Dim Payment   as Single                Payment = Payment + 25
Dim Again     as Integer             End If

Do                                    MsgBox(Name & " " & _
'Inputs                                     Enumber & Chr(13) & _
"I:" & Income & Chr(13) & _
Name = InputBox("Name")                      "L:" & Level & Chr(13) & _
Enumber = Inputbox("Number")                 "P:" & Payment)
Income = InputBox("Income")          Again = MsgBox("Again",4)
Level = InpuBox("Medical Level")    Loop Until (Again = 7)
End Sub
Solution 3
Excel 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
Solution 3
Excel Definite Loop
 6 Changes
•   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
Solution 3
Excel Definite Loop
Option Explicit
'Written by T. James                'Medical Expense
Sub A5Q2S1()
Dim Name      as String           Payment = 0.01 * Income
Dim Enumber   as String
Dim Address   as String           If (Level = 1) Then
Dim Income    as Single             Payment = Payment + 10
Dim Level     as Integer          ElseIf (Level = 2) Then
Dim Payment   as Single             Payment = Payment + 25
Dim Row       as Integer          End If

Worksheets("Med1").Activate       Cells(Row, 5) = Payment
For Row = 5 to 10                 Next Row
'Inputs                         End Sub

Name      =   Cells(Row,   2)
Enumber   =   Cells(Row,   1)
Income    =   Cells(Row,   3)
Level     =   Cells(Row,   4)
Solution 4
Excel Indefinite Loop
 Change the solution to allow for any number of
records
 Every employee must have an Employee Number
Solution 4
Excel 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
Solution 4
Excel Indefinite Loop
Option Explicit
'Written by T. James                 'Medical Expense
Sub A5Q2S1()
Dim Name      as String            Payment = 0.01 * Income
Dim Enumber   as String
Dim Address   as String            If (Level = 1) Then
Dim Income    as Single              Payment = Payment + 10
Dim Level     as Integer           ElseIf (Level = 2) Then
Dim Payment   as Single              Payment = Payment + 25
Dim Row       as Integer           End If

Worksheets("Med2").Activate        Cells(Row, 5) = Payment
Row = 5
Do Until(IsEmpty(Cells(Row,1)))     Row = Row + 1
'Inputs                           Loop
End Sub
Name      =   Cells(Row,   2)
Enumber   =   Cells(Row,   1)
Income    =   Cells(Row,   3)
Level     =   Cells(Row,   4)
Solution 5
Calling a Subroutine
 Change the program to call the medical payment
as a Subroutine
Solution 5
Calling 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
Solution 5
Calling a Subroutine
 3 Changes
• 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.
Solution 5
Calling a Subroutine
    Name – Medical                Sub Medical (ByVal Income as Single, _
    Given – Income, Level                      ByVal Level as Integer, _
    Results – Payment                          ByRef Payment as Single)
Dim Income        as Single
Dim Level         as Integer     Payment = 0.01 * Income
Dim Payment       as Single
If (Level = 1) Then
Payment = Payment + 10
ElseIf (Level = 2) Then
Payment = Payment + 25
End If
End Sub

------

Call Medical(Income, Level, Payment)
Solution 5
Calling a Subroutine
Option Explicit
'Written by T. James                 'Medical Expense
Sub A5Q2S1()
Dim Name      as String            Call Medical(Income, Level, Payment)
Dim Enumber   as String
Dim Address   as String            Cells(Row, 5) = Payment
Dim Income    as Single
Dim Level     as Integer           Row = Row + 1
Dim Payment   as Single           Loop
Dim Row       as Integer         End Sub

Worksheets("Med3").Activate      Sub Medical (ByVal Income as Single, _
Row = 5                                       ByVal Level as Integer, _
Do Until(IsEmpty(Cells(Row,1)))                ByRef Payment as Single)
'Inputs
Payment = 0.01 * Income
Name      =   Cells(Row,   2)
Enumber   =   Cells(Row,   1)      If (Level = 1) Then
Income    =   Cells(Row,   3)        Payment = Payment + 10
Level     =   Cells(Row,   4)      ElseIf (Level = 2) Then
Payment = Payment + 25
End If
End Sub
Solution 6
Using a Function Visual Basic
 Change the system to use a function instead of a
subroutine
Solution 6
Using 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)
Solution 6
Using a Function Visual Basic
    Name – Medical                Function Medical (ByVal Income as Single, _
    Given – Income, Level                      ByVal Level as Integer, _
    Results – Payment                          ) as Single

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

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 59 posted: 11/23/2010 language: English pages: 24
Description: Integer Worksheets document sample
How are you planning on using Docstoc?