Lecture Outline Mathematical Expressions String Concatenation Builtin Functions

Lecture Outline Mathematical Expressions String Concatenation Built-in Functions Planning for Programming Debugging Decisions (Branching/Selection) If – Then – Else. If Select Case University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 1

Mathematical Expressions i. Ans = 3 + 2*(i. Num/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 University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 2

Mathematical Expressions v. Ans v. Ans = = = v. Ans becomes 5 ^ 2 25 8 / 2 4 81 / 4 20. 25 9 6 1 9 Mod 6 3 3 5 * 2 0 4 * 5 Mod 2 ^ 3 4 (1 + 2) ^ (3 4) 1 1 + 3 ^ 2 4 3 10 * (4 + 2) / 3 20 1 + 9 * 2 / 3 ^ 2 3 University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 3

String Concatenation Dim s. Full. Name, s. Name As String Concatenation s. Full. Name = “Homer” & “Simpson” s. Full. Name = “Homer” & “ “ & “Simpson” Concatenation and Replacement s. Name = Input. Box(“Enter your name: ”) s. Name = “Name: ” & s. Name Output on two lines s. Full. Name = “Homer” & vb. New. Line & “Simpson” University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 4

String Functions s. Text = “Hi, Snipe” Len(string) i. Val = Len(s. Text) Left(string, length) s. Val = Left(s. Text, 5) returns integer 9 returns string “Hi, S” Right(string, length) returns string s. Val = Right(s. Text, 2) “pe” University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 5

String Functions Assume: s. Text = “Hi, Snipe” Mid(string, start[, length]) s. Val = Mid(s. Text, 3, 4) s. Val = Mid(s. Text, 3) In. Str(start, string 1, string 2) i. Pos = In. Str(1, s. Text, “, ”) i. Pos = In. Str(1, s. Text, “b”) University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin returns string “, Sn” “, Snipe” returns int 3 0 (not found) CSCA 01 VBA-2 6

String Function Example s. Name = “Hunter, Andria” How would you extract “Andria”? find position of comma v. Comm. At = In. Str(1, s. Name, “, ”) use Mid starting 2 characters after comma v. First. Name = Mid(s. Name, v. Comm. At+2) Alternate method – nest the function v. First. Name = Mid(s. Name, Instr(1, s. Name, “, ”)+2) University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 7

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) Val(“ 125 lbs”) Val(“ 65”) Str(number) Str(365) University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin returns number 125 65 returns string “ 365” CSCA 01 VBA-2 8

Type Mismatch Errors When value assigned to variable does not match the variable’s declaration type Dim i. Num As Integer i. Num = 15 i. Num = 21. 6 rounds to integer i. Num = “CSC A 01 F” type mismatch error i. Num = “ 8. 7” converts to numeric Dim s. Text As String s. Text = 12. 9 University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin converts to string CSCA 01 VBA-2 9

Planning for Programming 1. Observe process to be automated analysis 2. Draw Flowchart design 3. Write Pseudo Code design 4. Edit Code with VBA Editor programming 5. Run and Test testing maintenance University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 10

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%) University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 11

Observe the Process Having identified: Formula to Use Variables Constants Define specifically: Inputs – items the user will enter Outputs – form and media University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 12

Flow Charts Schematic drawing of the process Start or Stop Data Input/Output Process steps • use standard symbols • flow from top to bottom • connect with arrows Predefined Process Decisions University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 13

Problem: Get Coffee! Start Decide you need Any task can be flowcharted a cup of coffee Each step could have its own chart Go to coffee pot Decision introduces a branch (selection) Is pot empty? Get cup Yes Make coffee No Fill cup End University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 14

Problem: Cost Model n n n Start Get user inputs ¨ Material ¨ Labour Determine total cost Determine bid price Output results Quit Start Get: Labour cost Material cost Determine: Total cost Bid Price Show Bid Price Quit University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 15

Start Pseudo Code Write steps, formulas needed Get: Labour cost Material cost Determine: Total cost Bid Price Show Bid Price Quit University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin Ask user for labour cost [v. Labour] and material cost [v. Material] [v. Cost] = [v. Labour] + [v. Material] [v. Price] = [v. Cost]*(1+[d. OH]+[d. PROFIT]) Display message showing [v. Price] Pseudo Code often ends up as comments in the VBA code CSCA 01 VBA-2 16

Write VBA Code Sub Bid. Price() Dim v. Labour, v. Material, v. Cost, v. Price Const d. OH = 0. 85 Const d. PROFIT = 0. 3 'Ask user for labour cost and material cost v. Labour = Input. Box("Enter the labour cost: ") v. Material = Input. Box("Enter the cost of materials: ") 'Total cost is labour plus materials v. Cost = Val(v. Labour) + Val(v. Material) 'Price is total cost, with overhead and profit markup v. Price = v. Cost * (1 + d. OH + d. PROFIT) 'Display message showing bid price Msg. Box "Bid price is " & Format(v. Price, “Currency”) End Sub University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 17

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!! University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 18

Debugging n Debug Toolbar n Breakpoints n Observed Values n Watches n Step Into n Step Over University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 19

Debugging Watch an expression Step Into: process one line Debug Toolbar Breakpoint Yellow: next line to process Observed value: cursor on object University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 20

Conditional Expression Evaluates to true or false used to make a decision Formed by using a comparison operator: = < > > < >= <= 10 < > 20 i. Value <= i. Count “apple” < “cat” Range(“B 2”) = Range(“B 3”) University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin true ? CSCA 01 VBA-2 21

If-Then Execute code only if condition is true If condition Then Actions if condition is true End If d. Price = Input. Box(“Enter Price: ”) If d. Price > 1000 Then Msg. Box “Too expensive!” End If University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 22

If-Then Flow chart branches forward Dim s. Name As String s. Name = Inputbox(“Name? ”) If s. Name = “” Then s. Name = Inputbox(“Name? ”) End If Range(“B 3”) = s. Name Start Get user name Is name empty? No Yes Get user name Write name to cell B 3 End University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 23

If-Then-Else Selects one of two choices If condition Then Actions if condition is true Else Actions if condition is false End If University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 24

If-Then-Else Flow chart takes one of two paths Is value negative? No Set text color to black Yes Set text color to red If Range(“B 3”) < 0 Then Range(“B 3”). Font. Color. Index = 3 Else Range(“B 3”). Font. Color. Index = 1 End If University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 25

If-Then-Else Example Determine which message to display Dim s. Name, s. Out As String s. Name = Input. Box (“What’s your name? ”) If s. Name = “” Then s. Out = “You didn’t enter a name!” Else s. Out = “Welcome, ” & s. Name & “. ” End if Msg. Box s. Out University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 26

Msg. Box Function Msg. Box(prompt, buttons, title) returns int v. Ans = Msg. Box(“It’s raining. ”, vb. OKOnly, “Raining”) v. Ans = Msg. Box(“Is it raining? ”, vb. Yes. No, “Raining”) v. Ans = Msg. Box(“I said RAIN!”, vb. OKCancel, “Raining”) What is returned? vb. OK, vb. Yes, vb. No, vb. Cancel University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 27

Msg. Box If-Then Example Examine value returned by Msg. Box and take an appropriate action i. Ans = Msg. Box(“Is it raining? ”, vb. Yes. No) If i. Ans = vb. Yes Then s. Out = “Help, it’s raining!” Else s. Out = “Yippee, it’s dry!” End if Msg. Box s. Out University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 28

If–Then-Else. If-Else Select only one of a number of options Stops when first true condition encountered i. Quantity = Input. Box(“Enter Quantity: “) If i. Quantity <= 25 Then d. Discount = 0. 10 Else. If i. Quantity <= 50 Then d. Discount = 0. 15 Else. If i. Quantity <= 75 Then d. Discount = 0. 20 Else d. Discount = 0. 25 End If Msg. Box “Discount is “ & Format(d. Discount, ”Percent”) University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 29

Multiple Choice Get Colour Is Colour Green ? No Yes Set text color to Green University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin Is Colour Red ? Yes Set text color to Red No Is Colour Blue ? No Yes Set text color to Blue CSCA 01 VBA-2 30

Multiple Choice Use Else. If to select s. My. Colour = Input. Box (“Colour? ”) If s. My. Colour = “Green” Then Range(“B 3”). Font. Color = vb. Green Else. If s. My. Colour = “Red” Then Range(“B 3”). Font. Color = vb. Red Else. If s. My. Colour = “Blue” Then Range(“B 3”). Font. Color = vb. Blue End If University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 31

Multiple Choice Or use Select Case s. My. Colour = Input. Box (“Colour? ”) Select Case s. My. Colour Case “Green” Range(“B 3”). Font. Color = vb. Green Case “Red” Range(“B 3”). Font. Color = vb. Red Case “Blue” Range(“B 3”). Font. Color = vb. Blue End Select University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA-2 32