Lecture Outline

1. Lecture Outline Mathematical Expressions String Concatenation Built-in Functions Planning for Programming Debugging Decisions (Branching/Selection) If – Then – Else ElseIf Select Case

2. Mathematical Expressions iAns = 3 + 2*(iNum/4) precedence order ( ) Brackets ^ Exponentiation - Negation / * Division and Multiplication in order of occurrence \ Integer division Mod Division remainder (modulus) + - Addition and Subtraction in order of occurrence

3. Mathematical Expressions vAns becomes vAns = 5 ^ 2 25 vAns = 8 / 2 4 vAns = 81 / 4 20.25 vAns = 9 \ 6 1 vAns = 9 Mod 6 3 vAns = 3 \ 5 * 2 0 vAns = 4 * 5 Mod 2 ^ 3 4 vAns = (1 + 2) ^ (3 \ 4) 1 vAns = 1 + 3 ^ 2 \ 4 3 vAns = 10 * (4 + 2) / 3 20 vAns = 1 + 9 * 2 / 3 ^ 2 3

4. String Concatenation Dim sFullName, sName AsString Concatenation sFullName = “Homer” & “Simpson” sFullName = “Homer” & “ “ & “Simpson” Concatenation and Replacement sName = InputBox(“Enter your name:”) sName = “Name: ” & sName Output on two lines sFullName = “Homer” & vbNewLine & “Simpson”

5. String Functions sText = “Hi, Snipe” Len(string) returns integer iVal = Len(sText) 9 Left(string, length) returns string sVal = Left(sText, 5) “Hi, S” Right(string, length) returns string sVal = Right(sText, 2) “pe”

6. String Functions Assume: sText = “Hi, Snipe” Mid(string, start[, length]) returns string sVal = Mid(sText, 3, 4) “, Sn” sVal = Mid(sText, 3) “, Snipe” InStr(start, string1, string2) returns int iPos = InStr(1, sText, “,”) 3 iPos = InStr(1, sText, “b”) 0 (not found)

7. String Function Example sName = “Hunter, Andria” How would you extract “Andria”? find position of comma vCommAt = InStr(1, sName, “,”) use Mid starting 2 characters after comma vFirstName = Mid(sName, vCommAt+2) Alternate method – nest the function vFirstName = Mid(sName, Instr(1, sName, “,”)+2)

8. Type Conversion Functions Format(number, format) returns string Format(123.876, “$###.00”) “$123.88” Format(123.876, “0.0”) “123.9” Format(123.876,”Currency”) “$123.88” Val(string) returns number Val(“125 lbs”) 125 Val(“65”) 65 Str(number) returns string Str(365) “365”

9. Type Mismatch Errors When value assigned to variable does not match the variable’s declaration type Dim iNum As Integer iNum = 15 iNum = 21.6 rounds to integer iNum = “CSC A01F” type mismatch error iNum = “8.7” converts to numeric Dim sText As String sText = 12.9 converts to string

10. Planning for Programming • Observe process to be automated • Draw Flowchart • Write Pseudo Code • Edit Code with VBA Editor • Run and Test analysis design design programming testing maintenance

11. Observe the Process State problem:Given cost of materials & labour, and knowing markup for overhead & profit, determine price to bid. Identify formula:Price = (Materials + Labour) * (1 + Overhead + Profit) Price = Cost + OH + Profit Identify Variables - entered by user • Material cost • Labour cost Identify Constants - set by programmer • Overhead percentage (85%) • Profit percentage (30%)

12. Observe the Process Having identified: Formula to Use Variables Constants Define specifically: Inputs – items the user will enter Outputs – form and media

13. Start or Stop DataInput/Output Process steps Decisions Flow Charts Schematic drawing of the process • use standard symbols • flow from top to bottom • connect with arrows PredefinedProcess

14. Problem: Get Coffee! Any task can be flow-charted Each step could have its own chart Decision introduces a branch (selection) Start Decide you need a cup of coffee Get cup Go to coffee pot Makecoffee Yes Is potempty? No Fill cup End

15. Problem: Cost Model Start Get user inputs Material Labour Determine total cost Determine bid price Output results Quit Start Get: Labour costMaterial cost Determine:Total costBid Price Show Bid Price Quit

16. Pseudo Code Start Get: Labour costMaterial cost Ask user for labour cost [vLabour] and material cost [vMaterial] Determine:Total costBid Price [vCost] = [vLabour] + [vMaterial] [vPrice] = [vCost]*(1+[dOH]+[dPROFIT]) Show Bid Price Display message showing [vPrice] Quit Write steps, formulas needed Pseudo Code often ends up as comments in the VBA code

17. Sub BidPrice() Dim vLabour, vMaterial, vCost, vPrice Const dOH = 0.85 Const dPROFIT = 0.3 'Ask user for labour cost and material cost vLabour = InputBox("Enter the labour cost:") vMaterial = InputBox("Enter the cost of materials:") 'Total cost is labour plus materials vCost = Val(vLabour) + Val(vMaterial) 'Price is total cost, with overhead and profit markup vPrice = vCost * (1 + dOH + dPROFIT) 'Display message showing bid price MsgBox "Bid price is " & Format(vPrice, “Currency”) End Sub Write VBA Code

18. Run and Test Run the program Test with different inputs confirm the logic is correct confirm there are no bugs The program should not crash!!

19. Debugging • Debug Toolbar • Breakpoints • Observed Values • Watches • Step Into • Step Over

20. Debugging Watch an expression Step Into: process one line Debug Toolbar Breakpoint Yellow: next line to process Observed value: cursor on object

21. Conditional Expression Evaluates to true or false used to make a decision Formed by using a comparison operator: = < > > < >= <= 10 < > 20 true iValue <= iCount ? “apple” < “cat” true Range(“B2”) = Range(“B3”) ?

22. If-Then Execute code only if condition is true IfconditionThen Actions if condition is true End If dPrice = InputBox(“Enter Price:”) If dPrice > 1000 Then MsgBox “Too expensive!” End If

23. If-Then Flow chart branches forward Dim sName As String sName = Inputbox(“Name?”) If sName = “” ThensName = Inputbox(“Name?”) End If Range(“B3”) = sName Start Get user name Is nameempty? Yes Get user name No Write name to cell B3 End

24. If-Then-Else Selects one of two choices IfconditionThen Actions if condition is true Else Actions if condition is false End If

25. If-Then-Else Flow chart takes one of two paths If Range(“B3”) < 0 ThenRange(“B3”).Font.ColorIndex = 3 ElseRange(“B3”).Font.ColorIndex = 1 End If Is valuenegative? Yes No Set text color to red Set text colorto black

26. If-Then-Else Example Determine which message to display Dim sName, sOut As String sName = InputBox (“What’s your name?”) If sName = “” Then sOut = “You didn’t enter a name!” Else sOut = “Welcome, ” & sName & “.” End if MsgBox sOut

27. MsgBox(prompt, buttons, title)  returns int vAns = MsgBox(“It’s raining.”, vbOKOnly, “Raining”) vAns = MsgBox(“Is it raining?”, vbYesNo, “Raining”) vAns = MsgBox(“I said RAIN!”, vbOKCancel, “Raining”) What is returned? vbOK, vbYes, vbNo, vbCancel MsgBox Function

28. MsgBox If-Then Example Examine value returned by MsgBox and take an appropriate action iAns = MsgBox(“Is it raining?”, vbYesNo) If iAns = vbYes Then sOut = “Help, it’s raining!” Else sOut = “Yippee, it’s dry!” End if MsgBox sOut

29. Select only one of a number of options Stops when first true condition encountered iQuantity = InputBox(“Enter Quantity:“) If iQuantity <= 25Then dDiscount = 0.10ElseIf iQuantity <= 50 Then dDiscount = 0.15ElseIf iQuantity <= 75 Then dDiscount = 0.20Else dDiscount = 0.25End If MsgBox “Discount is “ & Format(dDiscount, ”Percent”) If–Then-ElseIf-Else

30. Multiple Choice Get Colour Is ColourGreen ? Is ColourBlue ? Is ColourRed ? No No No Yes Yes Yes Set text colorto Green Set text colorto Red Set text colorto Blue

31. Multiple Choice Use ElseIf to select sMyColour = InputBox (“Colour?”) If sMyColour = “Green” Then Range(“B3”).Font.Color = vbGreen ElseIf sMyColour = “Red” Then Range(“B3”).Font.Color = vbRed ElseIf sMyColour = “Blue” Then Range(“B3”).Font.Color = vbBlue End If

32. Multiple Choice Or use Select Case sMyColour = InputBox (“Colour?”) Select Case sMyColour Case “Green” Range(“B3”).Font.Color = vbGreen Case “Red” Range(“B3”).Font.Color = vbRed Case “Blue” Range(“B3”).Font.Color = vbBlue End Select