259 Lecture 10 Spring 2017 Advanced Excel Topics

259 Lecture 10 Spring 2017 Advanced Excel Topics – More User Defined Functions; Conditional Statements

Outline o More User Defined Functions! n n Perp_Bisector_Slope() Perp_Bisector_Intercept() Quadratic_1() Quadratic_2() o Pseudo Code o IF…THEN…ELSE Statements n Modified Quadratic Function n A Piecewise Defined Function 2

Perp_Bisector_Slope() o Example 1: Create a user defined function Perp_Bisector_Slope() that will return the slope of the line that is the “perpendicular bisector” of the line segment determined by the points (x 1, y 1) and (x 2, y 2). 3

Perp_Bisector_Slope() o Given: n Points (x 1, y 1) and (x 2, y 2). n Line L is the perpendicular bisector of the line segment determined by the points (x 1, y 1) and (x 2, y 2). o Find: n The slope of line L. 4

Perp_Bisector_Slope() o Pseudo Code: (1) Find the slope of the line segment determined by the points (x 1, y 1) and (x 2, y 2), and then store the result in the variable m 1. (2) Find the negative reciprocal of m 1 and then store the result in the variable m 2. (3) Return the value stored in the variable m 2 as the result of the user defined function. 5

Perp_Bisector_Slope() Function Perp_Bisector_Slope(x 1 As Double, y 1 As Double, x 2 As Double, y 2 As Double) As Double 'Returns the slope of the line that is the '"perpendicular bisector" of the line segment 'determined by points (x 1, y 1) and (x 2, y 2). 'The function does NOT work for vertical lines Dim m 1 As Double, m 2 As Double m 1 = (y 2 - y 1) / (x 2 - x 1) 'negative reciprocal of m 1 m 2 = -1 / m 1 Perp_Bisector_Slope = m 2 End Function 6

Perp_Bisector_Intercept() o Example 2: Create a user defined function Perp_Bisector_Intercept() that will return the y-intercept of the line that is the “perpendicular bisector” of the line determined by the points (x 1, y 1) and (x 2, y 2). 7

Perp_Bisector_Intercept() o Given: n Points (x 1, y 1) and (x 2, y 2). n Line L is the perpendicular bisector of the line segment determined by the points (x 1, y 1) and (x 2, y 2). o Find: n The y-intercept of line L. 8

Perp_Bisector_Intercept() o Pseudo Code: (1) Find the slope of the line by using the Perp_Bisector_Slope(x 1, y 1, x 2, y 2) function previously defined, and then store the result in the variable m. (2) Find the x-component of the midpoint of the line segment, and then store the result in the variable Midpoint_X. (3) Find the y-component of the midpoint of the line segment, and then store the result in the variable Midpoint_Y. (4) Return the y-intercept of line L, by substituting the coordinates of the midpoint into the expression y -m*x. 9

Perp_Bisector_Intercept() Function Perp_Bisector_Intercept(x 1 As Double, y 1 As Double, x 2 As Double, y 2 As Double) As Double 'Returns the y-intercept of the line that is the 'perpendicular bisector" of the line segment 'determined by points (x 1, y 1) and (x 2, y 2). 'The function does NOT work for vertical lines Dim m As Double Dim Midpoint_X As Double, Midpoint_Y As Double m = Perp_Bisector_Slope(x 1, y 1, x 2, y 2) Midpoint_X = (x 1 + x 2) / 2 Midpoint_Y = (y 1 + y 2) / 2 'b = y - m*x 'put in a known point (Midpoint_X, Midpoint_Y) on the line Perp_Bisector_Intercept = Midpoint_Y - m * Midpoint_X End Function 10

Quadratic_1() o Example 3: Create a user defined function Quadratic_1() that will return the first real root of a quadratic equation Ax 2 + Bx + C = 0. 11

Quadratic_1() o Given: n A, B, and C n Roots of Ax 2 + Bx + C = 0 are and o Find: n The first real root of a quadratic equation Ax 2 + Bx + C = 0. 12

Quadratic_1() o Pseudo Code: (1) Substitute the A, B, and C values into the formula and return the result of this expression. 13

Quadratic_1() Function Quadratic_1(A As Double, B As Double, C As Double) As Double 'Returns the first real root of a quadratic equation 'Ax^2+Bx+C=0 'Does NOT return a complex root Quadratic_1 = (-B + Sqr(B ^ 2 - 4 * A * C)) / (2 * A) End Function 14

Quadratic_2() o Example 4: Create a user defined function Quadratic_2() that will return the second real root of a quadratic equation Ax 2 + Bx + C = 0. 15

Quadratic_2() o Given: n A, B, and C n Roots of Ax 2 + Bx + C = 0 are and o Find: n The second real root of a quadratic equation Ax 2 + Bx + C = 0. 16

Quadratic_2() o Pseudo Code: (1) Substitute the A, B, and C values into the formula and return the result of this expression. 17

Quadratic_2() Function Quadratic_2(A As Double, B As Double, C As Double) As Double 'Returns the second real root of a quadratic equation 'Ax^2+Bx+C=0 'Does NOT return a complex root Quadratic_2 = (-B - Sqr(B ^ 2 - 4 * A * C)) / (2 * A) End Function 18

IF…THEN…ELSE Statements o Just like in Excel, we can create conditional statements within VBA! o One of the most useful conditional statements in VBA is the IF…THEN…ELSE statement! 19

IF…THEN…ELSE Syntax o Syntax: If condition_1 Then result_1 Else. If condition_2 Then result_2. . . Else. If condition_n Then result_n Else result_else End If o o o How the command works: condition_1 to condition_n are evaluated in the order listed. Once a condition is found to be true, the IF…THEN…ELSE statement will execute the corresponding code and not evaluate the conditions any further. result_1 to result_n is the code that is executed once a condition is found to be true. If no condition is met, then the Else portion of the IF…THEN…ELSE statement will be executed. Note that the Else. If and Else portions are optional. 20

Quadratic_1_Improved() o Example 5: Modify the Quadratic_1() user defined function to return the first root (real or complex). o For example x 2+4 x+8=0 should return (2+2 i) and x 2+5 x+6=0 should return -2. o Then modify the VBA code for this new function to make a user defined function to find the other root! 21

Quadratic_1_Improved() o Given: A, B, and C Roots of Ax 2 + Bx + C = 0 are: o Find: The first root of a quadratic equation Ax 2 + Bx + C = 0. 22

Quadratic_1_Improved() o Pseudo Code: (1) If B^2 – 4 AC � 0, then substitute the A, B, and C values into the formula and return the value of this expression. (2) Otherwise, put the A, B, and C values into the formula and store the result in the variable Real_Part. (3) Substitute the A, B, and C values into the formula and store the result in the variable Imaginary_Part. (4) Return the string value “{Real_Part} + {Imaginary_Part} i “ by substituting the actual numeric values for Real_Part and Imaginary_Part into {Real_Part} and {Imaginary_Part}. 23

Quadratic_1_Improved() Function Quadratic_1_Improved(A As Double, B As Double, C As Double) As Variant 'Returns the first root of a quadratic equation Ax^2+Bx+C=0 'Returns both real and complex roots Dim Real_Part As Double, Imaginary_Part As Double If B ^ 2 - 4 * A * C >= 0 Then Quadratic_1_Improved = (-B + Sqr(B ^ 2 - 4 * A * C)) / (2 * A) Else Real_Part = -B / (2 * A) Imaginary_Part = Sqr(Abs(B ^ 2 - 4 * A * C)) / (2 * A) Quadratic_1_Improved = Str(Real_Part) + "+ " + Str(Imaginary_Part) + "i" End If End Function 24

A piecewise “user defined function” f(x) o Example 6: Use VBA IF statement(s) to create the following piecewise “user defined function” f(x): o Plot a graph of this function! 25

A piecewise “user defined function” f(x) Function f(x As Double) As Double 'Returns x^3, if x<0 'Returns x^2, if 0<=x<=3 'Returns Sqr(x-3), if x>3 If x < 0 Then f=x^3 End If If (x >= 0) And (x <= 3) Then f=x^2 End If If x > 3 Then f = Sqr(x-3) End If End Function 26

References o User Defined Functions Notes – John Albers o IF…THEN…ELSE Statements (p. 20 of this lecture) http: //www. techonthenet. com/excel/f ormulas/if_then. php 27