Using Procedures and Functions, Module-level Variables, User Forms

1. Using Procedures and Functions, Module-level Variables, User Forms What is a procedure? What is a sub procedure (subroutine)? Built-in functions in your code What is a function? Danger of module-level variables

2. Procedures A procedure is a block of programming code. • There are two kinds of procedures: • Subroutines (sub procedures) • Functions • All macros are VBA subroutines 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 2006

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 module if sub procedure is not stored in "Modules") Sheet1. Commission Sheet1.Format CS 105 Spring 2006

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 You can use macros easily because they are on a Module sheet, that makes them available to everything, so all you do is write the macro name CS 105 Spring 2006

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 2006

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" CS 105 Spring 2006

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

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 2006

9. 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 2006

10. 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 2006

11. 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 2006

12. Parameter vs. Argument You *declare* a function/sub procedure using something like this: Function Reverse(strWord as String) As String - strName is called a *parameter* of the function Reverse When 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. CS 105 Spring 2006

13. 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 2006

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

15. 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 2006

16. Module-level variables • A variable may be accessible to any procedures affiliated with a module – This is a module level variable and has the prefix "m". • Declared in General/Declarations CS 105 Spring 2006

17. What happens here? Dim mintTotal As Integer Private Sub cmdButtonAddOne_Click() mintTotal = 1 Range("A1").Value = mintTotal End Sub Private Sub cmdButtonMultiply_Click() Dim intNumberTwo As Integer mintTotal = mintTotal * 2 Range("C1").Value = mintTotal End Sub CS 105 Spring 2006

18. Danger of Module-level variables • The problem with module-level variables is that they can be changed by more than one procedure. • We use them sparingly! • You will lose points if you don’t follow directions on MPs (use them only when you are told to) CS 105 Spring 2006

19. User Forms • How are they different from worksheets? • What is a frame? • How do you make a User Form appear? CS 105 Spring 2006

20. UserForms, Frames, and Labels • User Form • Labels • Frame CS 105 Spring 2006

21. Creating a User Form CS 105 Spring 2006

22. Name the form, and insert a caption CS 105 Spring 2006

23. How do we show and hide the User Form? • Code for button is: Private Sub cmdShow_Click() frmDemo.show End Sub What do you think the code (method) is for hiding a form? CS 105 Spring 2006

24. Add labels and a frame in VB Editor CS 105 Spring 2006

25. Change the properties of the label CS 105 Spring 2006