# 20 VBExtra 2

Document Sample

```					Quiz 2
Quiz 2
What is output (5 Marks) [8 min]
X = 987
Y = X
Z = 0
Do
Do While (Y > 0)
Z = Z + Y Mod 10
Y = Y \ 10                        Quiz2
Loop

Y = Z
Z = 0
Loop Until (Y < 10)

Z = X * 10   +   Y
MsgBox ("X   =   " & X & Chr(13) & _
"Y   =   " & Y & Chr(13) & _
"Z   =   " & Z)
Example 2

Travel Expenses
Travel Expenses
• You need to calculate travel expenses for a
company.
• The company assigns travel codes for each trip
–   TO – Trip to Toronto
–   MO – Trip to Montreal
–   KI – Trip to Kingston
–   RS – Research Trip
–   MK – Marketing Trip
Travel Expenses
• Since travel to the cities of Toronto
Montreal and Kingston are frequent, a flat
rate of the following:
– Toronto - \$70 + \$50 per night
– Montreal - \$50 + \$40 per night
– Kingston - \$40 + \$40 per night
Travel Expenses
• Research Trips have the following
calculation
– \$0.05 per km traveled
– \$60 per night stayed
– \$10 per meal
Travel Expenses
• Marketing Trips have the following
calculation
– \$0.05 per km traveled
– \$80 per night stayed
– \$25 per meal
Algorithm
•   Name Travel
•   Given Code, KM, Nights, Meals
•   Results – Refund
•   Intermediates – None
•   Definition Refund := Travel (Code, KM,
Nights, Meals)
Algorithm
Get Code
Get KM, Nights, Meals

If (Code = TO)
Refund = 70 + 50 * Nights
Else if (Code = MO)
Refund = 50 + 40 * Nights
Else if (Code = KI)
Refund = 40 + 40 * Nights
Else if (Code = RS)
Refund = 0.5 * KM + 60 * Nights + 10 * Meals
Else if (Code = MK)
Refund = 0.5 * KM + 80 * Nights + 25 * Meals
Else
Refund = -999

Give Refund
Sub Trav1()

Write as a simple        Dim Code as String*2
Dim KM as Integer
Dim Nights as Integer

Sub( )              Dim Meals as Integer
Dim Refund as Currency

Get Code                 Code = InputBox("Code")
Get KM, Nights, Meals    KM = InputBox("KM")
Nights = InputBox("Nights")
Meals = InputBox("Meals")
If (Code = TO)
If (Code = "TO") Then
....                   Refund = 70 + 50 * Nights
ElseIf (Code = "MO") Then
Refund = 50 + 40 * Nights
ElseIf (Code = "KI") Then
Refund = 40 + 40 * Nights
ElseIf (Code = "RS") Then
Refund = 0.5 * KM + 60 * Nights + 10 * Meals
Else                     ElseIf (Code = "MK") Then
Refund = -999      Refund = 0.5 * KM + 80 * Nights + 25 * Meals
Else
Refund = -9999
Give Refund              End If

MsgBox("R = " & Refund)
Trav1
End Sub
Write as Sub( param )
Sub Trav2( ByVal Code as String, _
Get Code                           ByVal KM as Integer, _
Get KM, Nights, Meals              ByVal Nights as Integer, _
ByVal Meals as Integer, _
ByRef Refund as Currency)
If (Code = TO)
.....                    If (Code = "TO") Then
Refund = 70 + 50 * Nights
ElseIf (Code = "MO") Then
Refund = 50 + 40 * Nights
ElseIf (Code = "KI") Then
Refund = 40 + 40 * Nights
ElseIf (Code = "RS") Then
Else                      Refund = 0.5 * KM + 60 * Nights + 10 * Meals
ElseIf (Code = "MK") Then
Refund = -999      Refund = 0.5 * KM + 80 * Nights + 25 * Meals
Else
Give Refund               Refund = -9999
End If

End Sub
Write as Function( param )
Version 1 Dim Result
Get Code                Function Trav3( ByVal Code as String, _
ByVal KM as Integer, _
Get KM, Nights, Meals             ByVal Nights as Integer, _
ByVal Meals as Integer) as Currency
Dim Refund as Currency
If (Code = TO)
....                     If (Code = "TO") Then
Refund = 70 + 50 * Nights
ElseIf (Code = "MO") Then
Refund = 50 + 40 * Nights
ElseIf (Code = "KI") Then
Refund = 40 + 40 * Nights
ElseIf (Code = "RS") Then
Else                      Refund = 0.5 * KM + 60 * Nights + 10 * Meals
Refund = -999     ElseIf (Code = "MK") Then
Refund = 0.5 * KM + 80 * Nights + 25 * Meals
Else
Give Refund               Refund = -9999
End If

Trav3 = Refund
End Function
Write as Function( param )
Version 2 without Dim
Get Code                Function Trav4( ByVal Code as String, _
ByVal KM as Integer, _
Get KM, Nights, Meals             ByVal Nights as Integer, _
ByVal Meals as Integer) as Currency
If (Code = TO)
...                     If (Code = "TO") Then
Trav4 = 70 + 50 * Nights
ElseIf (Code = "MO") Then
Trav4 = 50 + 40 * Nights
ElseIf (Code = "KI") Then
Trav4 = 40 + 40 * Nights
ElseIf (Code = "RS") Then
Else                      Trav4 = 0.5 * KM + 60 * Nights + 10 * Meals
Refund = -999     ElseIf (Code = “MK") Then
Trav4 = 0.5 * KM + 80 * Nights + 25 * Meals
Else
Give Refund               Trav4 = -9999
End If

End Function
Utilizing the Sub (Param)
Sub Main1()
Dim C as String*2
Main1
Dim K as Integer
Dim N as Integer
Dim M as Integer
Dim R as Currency

C = InputBox("C")
K = InputBox("K")
N = InputBox("N")
M = InputBox ("M")

Call Trav2(C, K, N, M, R)

MsgBox("Refund = "& R)
End Sub
Utilizing using Function
Sub Main2()
Dim C as String*2
Dim K as Integer
Main2
Dim N as Integer
Dim M as Integer
Dim R as Currency

C = InputBox("C")
K = InputBox("K")
N = InputBox("N")
M = InputBox ("M")

‘Use Either one of the following
R = Trav3(C, K, N, M)
‘ R = Trav4(C, K, N, M)

MsgBox("Refund = "& R)
End Sub
Excel Worksheet
A     B      C   D       E       F

1   Emp Code     km Nights   Meals   Refund

2   123   TO         3               ??

3   234   RE     128 4       8       ??
Using Excel Only
• What would the Function to be written in F2 to
be copied down?

= if (B2 = “TO”, 70 + 50 * D2, if(B2 = “MO”, 50 +
40 * D2, if(B2 = “KI”, 40 + 40 * D2, if (B2 =
“RS”, 0.05 * C2 + 60 * D2 + 10 * E2, if(B2 =
“MK”, 0.05 * C2 + 80 * D2 + 25 * E2, -9999)))))

What if Research department changes its rules (say
\$20 per meal) or corporate changes base rate for
Toronto?
Using Excel with Visual Basic
• What what the function to be written in F2
to be copied down?

• = Trav3(B2, C2, D2, E2)

Maintenance is easier under Visual Basic
Note if Constants were used, maintenance
becomes very ease

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 1 posted: 9/15/2012 language: Unknown pages: 18
How are you planning on using Docstoc?