180 likes | 302 Views
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 Loop Y = Z Z = 0 Loop Until (Y < 10) Z = X * 10 + Y MsgBox ("X = " & X & Chr(13) & _ "Y = " & Y & Chr(13) & _
E N D
Quiz 2What 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 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() Dim Code as String*2 Dim KM as Integer Dim Nights as Integer Dim Meals as Integer Dim Refund as Currency Code = InputBox("Code") KM = InputBox("KM") Nights = InputBox("Nights") Meals = InputBox("Meals") 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 ElseIf (Code = "MK") Then Refund = 0.5 * KM + 80 * Nights + 25 * Meals Else Refund = -9999 End If MsgBox("R = " & Refund) End Sub Write as a simple Sub( ) Get Code Get KM, Nights, Meals If (Code = TO) .... Else Refund = -999 Give Refund
Write as Sub( param ) Sub Trav2( ByVal Code as String, _ ByVal KM as Integer, _ ByVal Nights as Integer, _ ByVal Meals as Integer, _ ByRef Refund as Currency) 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 ElseIf (Code = "MK") Then Refund = 0.5 * KM + 80 * Nights + 25 * Meals Else Refund = -9999 End If End Sub Get Code Get KM, Nights, Meals If (Code = TO) ..... Else Refund = -999 Give Refund
Write as Function( param )Version 1 Dim Result Function Trav3( ByVal Code as String, _ ByVal KM as Integer, _ ByVal Nights as Integer, _ ByVal Meals as Integer) as Currency Dim Refund as Currency 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 ElseIf (Code = "MK") Then Refund = 0.5 * KM + 80 * Nights + 25 * Meals Else Refund = -9999 End If Trav3 = Refund End Function Get Code Get KM, Nights, Meals If (Code = TO) .... Else Refund = -999 Give Refund
Write as Function( param )Version 2 without Dim Function Trav4( ByVal Code as String, _ ByVal KM as Integer, _ ByVal Nights as Integer, _ ByVal Meals as Integer) as Currency 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 Trav4 = 0.5 * KM + 60 * Nights + 10 * Meals ElseIf (Code = “MK") Then Trav4 = 0.5 * KM + 80 * Nights + 25 * Meals Else Trav4 = -9999 End If End Function Get Code Get KM, Nights, Meals If (Code = TO) ... Else Refund = -999 Give Refund
Utilizing the Sub (Param) Sub Main1() Dim C as String*2 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 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
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