Unit 6: Functions and Subroutines

1. Excel Macros Level 1 Functions and Subroutines

2. Functions & Subroutines Functions: Return a value Subroutines Do not return a value Both are used to: Make code modular (i.e. reusable) Make code more readable 4/29/2010 M. Campbell - 2010 2 p. 69

3. Activities In the Unit 6 Activities complete: Activity 1: Uppercase Subroutine Activity 2: Lowercase Subroutine Activity 3: Title Case Subroutine Activity 4: Uppercase Function Note that these can all go in the same module 4/29/2010 M. Campbell - 2010 3

4. Functions In Activity 4 you saw the function declaration: Public Function Uppercase2(Text As String ) _ As String 4/29/2010 M. Campbell - 2010 4

5. Functions In Activity 4 you saw the function declaration: Public Function Uppercase2(Text As String ) _ As String 4/29/2010 M. Campbell - 2010 5 Indicates the Function's scope: Public: Can be called from any module Private: Can only be called from module it was declared in p. 76

6. Functions In Activity 4 you saw the function declaration: Public Function Uppercase2(Text As String ) _ As String 4/29/2010 M. Campbell - 2010 6 Declares this code to be a Function p. 69

7. Functions In Activity 4 you saw the function declaration: Public Function Uppercase2(Text As String ) _ As String 4/29/2010 M. Campbell - 2010 7 The Function's name This is something that you define p. 69

8. Functions In Activity 4 you saw the function declaration: Public Function Uppercase2(Text As String ) _ As String 4/29/2010 M. Campbell - 2010 8 This is the Function's Parameter list Value is a Parameter When you ran the Function, the value of Text was set to hello The value placed in a Parameter is known as an Argument p. 71

9. Functions In Activity 4 you saw the function declaration: Public Function Uppercase2(Text As String ) _ As String 4/29/2010 M. Campbell - 2010 9 This is the Function's Return Type In this Function, it will return a value of type String p. 69

10. Functions In Activity 4 you saw this line of code: Uppercase2 = UCase (Text) 4/29/2010 M. Campbell - 2010 10

11. Functions In Activity 4 you saw this line of code: Uppercase2 = UCase (Text) 4/29/2010 M. Campbell - 2010 11 This is the Function's Return statement The value of the expression on the right side of the assignment operator will be returned by the Function

12. Functions In Activity 4 you saw this line of code: Uppercase2 = UCase (Text) 4/29/2010 M. Campbell - 2010 12 Remember that Text is the parameter to the Function. It currently holds the value of hello This sends the value to the UCase Function which converts hello to uppercase

13. Subroutines In Activity 1 you saw the subroutine declaration: Sub Uppercase() 4/29/2010 M. Campbell - 2010 13

14. Subroutines In Activity 1 you saw the subroutine declaration: Sub Uppercase() 4/29/2010 M. Campbell - 2010 14 Declares this code to be a Subroutine p. 70

15. Subroutines In Activity 1 you saw the subroutine declaration: Sub Uppercase() 4/29/2010 M. Campbell - 2010 15 The Subroutine's name This is something that you define p. 70

16. Subroutines In Activity 1 you saw the subroutine declaration: Sub Uppercase() 4/29/2010 M. Campbell - 2010 16 Note the absence of the scope keyword Public or Private If it is omitted, the procedure (subroutine or function) is declared to be Public It should always be included for readability p. 76

17. Calling Subroutines Has the form: [Public or Private] Sub SubroutineName (_ Param1 As DataType1 , Param2 As DataType2 ,_ …) To call: Call SubroutineName ( parameters , …) Or SubroutineName parameters , … 4/29/2010 M. Campbell - 2010 17 p. 70

18. Activities In the Unit 6 Activities complete: Activity 5: Calling a Function with a Subroutine 4/29/2010 M. Campbell - 2010 18

19. Optional Arguments Can set some arguments to be optional Sub DisplayName ( firstName As String , _ Optional lastName As String , _ Optional midName As String ) Note that all optional arguments must come at end of parameter list 4/29/2010 M. Campbell - 2010 19 p. 71

20. Optional Arguments Sub DisplayName ( firstName As String , _ Optional lastName As String , _ Optional midName As String ) To call procedure with only firstName and midName : Call DisplayName (" Winnie ", , "the") Must include blank spot as arguments expected in order defined in parameter list 4/29/2010 M. Campbell - 2010 20 p. 71

21. Positional Arguments In previous example, we used positional arguments: Call DisplayName (" Winnie ", , "the") The position of arguments tells VBA which parameters they fill 4/29/2010 M. Campbell - 2010 21 p. 73

22. Named Arguments Naming the arguments: Improves readability Removes the need for blank spaces Allows arguments to go in any order Call DisplayName ( midName := "the", _ firstName := Winnie ") 4/29/2010 M. Campbell - 2010 22 p. 73

23. Activities In the Unit 6 Activities complete: Activity 6: Optional and Named Arguments 4/29/2010 M. Campbell - 2010 23

24. ByRef Sub ProcedureA () x = 5 MsgBox x Call AddOne (x) MsgBox x End Sub Sub AddOne ( ByRef i As Integer ) i = i + 1 End Sub M. Campbell - 2010 24 p. 73

25. ByRef Sub ProcedureA () x = 5 MsgBox x Call AddOne (x) MsgBox x End Sub Sub AddOne ( ByRef i As Integer ) i = i + 1 End Sub M. Campbell - 2010 25 p. 73 Initializes x to 5

26. ByRef Sub ProcedureA () x = 5 MsgBox x Call AddOne (x) MsgBox x End Sub Sub AddOne ( ByRef i As Integer ) i = i + 1 End Sub M. Campbell - 2010 26 p. 73 Displays 5

27. ByRef Sub ProcedureA () x = 5 MsgBox x Call AddOne (x) MsgBox x End Sub Sub AddOne ( ByRef i As Integer ) i = i + 1 End Sub M. Campbell - 2010 27 p. 73 Calls AddOne and sends x

28. ByRef Sub ProcedureA () x = 5 MsgBox x Call AddOne (x) MsgBox x End Sub Sub AddOne ( ByRef i As Integer ) i = i + 1 End Sub M. Campbell - 2010 28 p. 73 AddOne is sent a reference to x, effectively replacing the i with x

29. ByRef Sub ProcedureA () x = 5 MsgBox x Call AddOne (x) MsgBox x End Sub Sub AddOne ( ByRef x As Integer ) x = x + 1 End Sub M. Campbell - 2010 29 p. 73 AddOne is sent a reference to x, effectively replacing the i with x

30. ByRef Sub ProcedureA () x = 5 MsgBox x Call AddOne (x) MsgBox x End Sub Sub AddOne ( ByRef x As Integer ) x = x + 1 End Sub M. Campbell - 2010 30 p. 73 1 is added to x to get 6 This is the same x as in ProcedureA