Using Procedures and Functions

1. Using Procedures and Functions What is a procedure? What is a sub procedure (subroutine)? Built-in functions in your code What is a function? CS 105 Spring 2010

2. Procedures A procedure is a block of programming code. • There are two kinds of procedures: • Subroutines (sub procedures) • Functions If we have to repeat code, we isolate it in a subroutine or function in order to avoid programming or typographical errors and make our code easier to debug or change—you only have to change it in one place! CS 105 Spring 2010

3. Calling Sub Procedures We can invoke or call each sub procedure when we wish to perform those operations. Just write the name of the sub procedure. Commission Format (must give name of work sheet or place the code is stored if the sub procedure is not in “Modules") Commission Format CS 105 Spring 2010

4. Private Sub versus Public Sub Private Sub means the procedure can only be called from other procedures in its object (like a UserForm, or worksheet). Sub means available to all worksheets, can be called from any procedure in any sheet. Sheet1.Commission Commission CS 105 Spring 2010

5. For Excel to use it, and for each spreadsheet to use it, where should the public function be? A public function in a module can be accessed from all worksheets. CS 105 Spring 2010

6. Functions and Subroutines The main difference: • Functions do things and return a value. strNew=Reverse(Cells(1,1).Value) • Sub(routine)s do things, but do not return values. MsgBox "Welcome",vbExclamation, "Hi” Format 2 CS 105 Spring 2010

7. Excel has many built-in functionsthat are the same as VBA functions,and vice-versa CS 105 Spring 2010

8. VBA Functions Predefined: A. vntAnswer =MsgBox("Quit?", vbYesNo, "Leaving?") B. If IsNumeric(strName) = True Then A Function is just like a subroutine,except that it • returns a value to a variable or cell (A.) or • is evaluated on the fly as above (B.) CS 105 Spring 2010

9. Quick Quiz What do we mean by IsNumeric returns True or False “on the fly”? The Value T or F is used immediately and discarded. For example, the content of a page that is sent to you from a Web site can be varied "on the fly" based on what pages you looked at previously—the page is dynamic and will change • B CS 105 Spring 2010

10. Functions The value returned by a function can be True, False, a value, vbYes, etc. • You invoke (CALL) a Function by mentioning its name (typically as part of a larger expression), together with its argument(s). • Sometimes a function doesn’t have arguments—you cannot tell for sure by looking at the statement calling the function, but you can tell by looking at how the function is written. Cells(1,1).Value =Now() CS 105 Spring 2010

11. We can create our own functions • Reverse(strWord) is at the heart of the function • The function Reverse wants an argument, either a cell address or a word, and then it will reverse the letters in the word • With functions such as Average, Count, etc. we need to feed them an argument CS 105 Spring 2010

12. Name Parameters Type of Return Value Body Defining a Function Function Useful(strName as String, intNumber as Integer) As String Do something here Useful = "a string of some sort" End Function CS 105 Spring 2010

13. Parameter vs. Argument You *declare* a function/sub procedure using something like this: Function Reverse(strWord as String) As String - strWord is called a *parameter* of the function Reverse You *call* a function/sub procedure using something like the following: Cells(1,1).Value = Reverse(strEntry) strEntry (or the value in a cell) is called an *argument* that you *pass* to the function Reverse. Note: what you pass to the parameter can have a different name than the parameter has. CS 105 Spring 2010

14. So… • To connect the two, a function/sub procedure defines a parameter, and the calling code passes a value/variable as an argument to that parameter. • You can think of the parameter as a parking place and the argument as an automobile. • Just as different automobiles can park in the parking place at different times, the calling code can pass a different argument to the same parameter each time it calls the procedure. CS 105 Spring 2010

15. Functions: Returning a Value Function FixFormat(strName as String, intNumber as Integer) As String FixFormat = strName & " " & intNumber End Function CS 105 Spring 2010

16. Private Sub cmdNew_Click() Dim strWord As String Dim strNoun As String Dim intQuantity As Integer strNoun = Cells(1, 2).Value intQuantity = Cells(2, 4).Value strWord = FixFormat(strNoun, intQuantity) MsgBox "The result is " & strWord End Sub ------------------------------------------------------------------------------------------- Function FixFormat(strName as String, intNumber as Integer) As String FixFormat = strName & " " & intNumber End Function Joe 78 strNoun intQuantity Joe 78

17. Calling Functions Dim strAnswer as String strAnswer = DoCalculation(17) … What is the name of the function? DoCalculation Better be a String What type of value does it return? One How many arguments does it have? Probably an Integer What are the type(s) of argument(s)? CS 105 Spring 2010

18. To Summarize: • What is a procedure? • What is a sub procedure (subroutine)? • Built-in functions in your code • What is a function? CS 105 Spring 2010