week5_slides - Subprograms/functions Learning Objectives...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 8/22/2011 Subprograms/functions Learning Objectives • • • • • • • VBA allows both subprograms and functions, and they are different • Functions are similar to subprograms, but are shorter and can compute just one value • Subroutines are longer and can compute multiple values Why Subroutines? How to declare subroutine variables? Passing values in and out of subroutines What are functions? How to call an EXCEL function from VB? How to use a VB function in EXCEL? 1 Trap rule program (without sub) n, i should be integers ** h, fa, fb, a, b, x, fx, fxSum, sum should be single ** n = 30 Note, here we are repeating the a = 1 formula three times b = 8 1 fa = (a ^ 2) * log(a) Also, note LOG is VB is natural log, LN fb = (b ^ 2) * log(b) h = (b ‐ a) / n 2 For i = 1 To n ‐ 1 3 x = a + (h * i) fx = (x ^ 2 * log(x)) fxSum = fxSum + fx Next i n 1 sum = h * (fa + (2 * fxSum) + fb) / 2 f ( x0 ) 2 f ( xi ) f ( xn ) Cells(1, 1) = sum i 1 I h End Sub 2 Limitation of the program without a subroutine • We have to compute f(x) at least 3 times in the program • If the expression is large, mistakes can happen. Difficult to debug such errors. • If you want to change the expression, you have to change it in 3 places. Trap rule program (with sub) n, i should be integers fa, fb, a, b, fx, sum, h, x should be single n = 30 a = 1 b = 8 h = (b ‐ a) / n Call Myexpress (a, fa) Call Myexpress (b, fb) For i = 1 To n ‐ 1 x = a + h * i Call Myexpress (x, fx) fxSum = fxSum + fx Next i sum = h * (fa + (2 * fxSum) + fb) / 2 Cells(1, 1) = sum End Sub 2 Sub Myexpress (x1 as Single, fx1 as Single) fx1 = (x1 ^ 2) * Log(x1) End Sub n 1 f ( x0 ) 2 f ( xi ) f ( xn ) i 1 I h 2 Trap rule program (with sub) • You describe the f(x) only once in the sub • If you need to change f(x), you have to just change it in the subroutine alone • Less chance of making errors • Call the subroutine Myexpress every time you want to evaluate the value of the function at x. 1 8/22/2011 Beware of the order of variables in the argument list! Subprograms – How does it work? • When you call the subroutine Myexpress from the main program, the control of the program will shift to sub Myexpress • Once the subprogram is executed, the control will come back to the line after the calling line • Example, Call Myexpress (x as Single, fx as Single) • We send the value of x into the sub program and get the value of fx, which is the value of the f(x) at x. • Argument list of variables are the variables we specify within the brackets. These variables are exchanged between the main and calling programs • In our example, argument list in the main program has: “a” and “fa” Call Myexpress (a, fa) • In the subprogram, variables in the argument list are “x1” and “fx1” Sub Myexpress (x1 as Single, fx1 as Single) • In this example, a is linked to x1, and fa to fx1 • Order is very important – If values in the call is reversed as fa and a then sub will treat x1 as fa, and fx1 as a 7 8 Passing by Reference (default, and also our the prefered method) Passing by Reference • All the variables declared in the main program are stored in a memory address within the computer. • Consider two variables in the call: a, fa • Let a = 100, fa = 500 in the main program a = 100 • In Visual basic, by default ,the value is passed to the subroutine by reference. • When you pass the variable by reference, you pass the memory address of the location where the variable is stored. a = 100 x1 = 100 fx1= 500 • So if you modified the values of x1 and fx1 in the sub program, then the values of a and fa are automatically change in the main program. Empty a = 2000 Subroutine memory before you Call the Sub x1 = 2000 fa= 3000 fa = 500 Main program memory Memory address fa = 500 fx1 = 3000 Main program memory Subroutine memory Advantages of using subroutines Passing by Value (another option) • Sometimes you may wish to protect the value of a variable in the main program (not allowing it to change) • This is accomplished by placing variable names in calling statement using an extra bracket around each variable: Call Myfunc((a), (b), c, d, e) – here a and b are passed by value, and c,d, and e are passed by reference. WE WILL NOT USE THIS OPTION IN THIS CLASS Subroutines can handle many variables Subroutines can modify the input parameters Subroutines can output several parameters In this class, it is recommended that you use subroutines (not functions) • Also, in the argument list place the input variables first and then place the output variables. (This is not required by VB, but it is a good programming practice) • • • • 11 2 8/22/2011 Construction site example (subroutine) Subroutine e xam ple N o of plots R ad ius o f the p lots 3 10 Typical sub program 12 15 T o tal are a 1 47 4 To tal fence leng th 232.5 71 4 26 4 P rivate Sub Com m andBu tton1_Cli ck() Dim n As In teg er Dim r1 As Sing le, a1 As Sin gle, p1 As Sin gle Dim r2 As Sing le, a2 As Sin gle, p2 As Sin gle, total_area As Sing le Dim r3 As Sing le, a3 As Sin gle, p3 As Sin gle, total_p erimeter As Sing le n = Cells(1, 2) r1 = Cells(2, 2) r2 = Cells(2, 3) r3 = Cells(2, 4) Ca ll mycircle(r1, a 1, p1) Ca ll mycircle(r2, a 2, p2) Ca ll mycircle(r3, a 3, p3) tota l_area = a1 + a2 + a3 tota l_perimeter = p1 + p2 + p3 Cells (3, 2) = total_a rea Cells (4, 2) = total_perim eter End S ub Sub m ycircle(r As S ing le, a As Sin gle, p As Sin gle) Dim p i As S ingle pi = 22# / 7# a = pi * (r ^ 2) p = 2# * pi * r End S ub Sub Myexpress (x1 as Single, fx1 as Single) fx1 = (x1 ^ 2) * Log(x1) End Sub Subroutines are defined as: Sub name( x single, n as integer, ..etc., y as single, fx.. etc.) INPUT VARIABLES OUTPUT VARIABLES *most of the time, x and n will not be modified* The values of y and fx will be calculated and sent back … fx=… y=… End Sub Argument list contains both input and output variables Name of the subroutine “name” is never used 13 Defining Functions Function Example • Functions are defined as: Function name( x single, n as integer, etc.) as single ONLY INPUT VARIABLES ARE USED … IN THE ARGUMENT LIST … name = … End Function • Note, “name” is the output variable and is used within the function. • The outer single declares the variable “name” as single • Name of function must be used as variable calculated within function as name is in example • Function to calculate the f(x) in trapezoidal rule: Function Myvalue(x as Single) as Single Myvalue = x^2 * Log(x) End Function 15 16 Trap rule program (with function) Function Example n, i should be integers fa, fb, a, b, fx, sum, h, x should be single n = 30 a = 1 b = 8 h = (b ‐ a) / n fa = Myvalue (a) fb = Myvalue(b) For i = 1 To n ‐ 1 x = a + h * i fx = Myvalue(x) fxSum = fxSum + fx Next i sum = h * (fa + (2 * fxSum) + fb) / 2 Cells(1, 1) = sum End Sub Calling the function accomplished by: fa = Myvalue(a) fb = Myvalue(b) fx = Myvalue(x) Note a function can be used multiple times in the same line Function Myvalue(x as Single) as Single Myvalue =( x ^ 2)*Log(x) End Function n 1 f ( x0 ) 2 f ( xi ) f ( xn ) i 1 I h 2 17 3 8/22/2011 Construction site example (function) No of sites Radius of the sites Total area 3 10 1474 12 Private Sub CommandButton1_Click() Dim n As Integer Dim r1 As Single, r2 As Single, r3 As Single, total_area As Single n = Cells(1, 2) r1 = Cells(2, 2) r2 = Cells(2, 3) r3 = Cells(2, 4) total_area = circlearea(r1) + circlearea(r2) + circlearea(r3) Cells(3, 2) = total_area End Sub Function circlearea(r As Single) As Single Dim pi As Single pi = 22# / 7# circlearea = pi * (r ^ 2) End Function Subroutines Vs Functions 15 •A subroutine takes a number of input parameters and computes and outputs a number of parameters. • The output variable are passed within the parenthesis •Invoke by using a call statement. E.g., Call Myexpress(b, fb) Note, several function calls can be made in the same line. •A function takes a number of input parameters and computes and returns a single value. • Name of the function is the output value returned by the function. • Invoke by using the function name within the equation of interest. E.g. sum = h * (Myvalue(a) + (2 * fxSum) + Myvalue(b)) / 2 19 How to use a VB function in EXCEL How to use an EXCEL function in VB • You can invoke a VB function from your EXCEL spreadsheet. This is can be useful way to evaluate certain formula • Click on Visual Basic editor icon under developer tab • Insert module • Insert procedure • Select type as function, enter name as: “metertofeet” and then type the following code • Go to EXCEL and use the function, as you would use any normal EXCEL function • Visual basic can invoke specialized EXCEL functions with the VB code. For example, EXCEL has function called “fact” to compute factorials. This function can be invoked as shown below Public Function metertofeet(x As Single) As Single metertofeet = x * 3.28 End Function Private Sub CommandButton1_Click() Dim nfact As Integer nfact = Application.WorksheetFunction.Fact(Cells(1, 1)) Cells(1, 2) = nfact End Sub 21 22 4 ...
View Full Document

{[ snackBarMessage ]}

Ask a homework question - tutors are online