Ch 10: More on Variables and Subroutines

1. Ch 10: More on Variables and Subroutines CP212 Winter 2012

2. Topics • Subroutines • Functions • Modular programming • 3 Main Benefits • Variable and Subroutine Scope • Using and Passing Parameters • Module level "Global" variables • Passing arrays • The Workbook_Open event handler • Generating Random Numbers (Ex. 10.4)

3. Scope • A variable is created in a sub can only be used in that sub. • It has limited scope: sub-level scope. • Known as a local variable. Sub mySecondSub() Dim score As Integer     score = 4     Msgbox score End Sub Sub myFirstSub() Dim score As Integer     score = 3     Msgbox score End Sub Different variables, different location in memory.

4. Module-Level Scope • If you want subs to share a variable, declare it outside of a sub • This makes it a module-level variable • Every sub in the module can access the variable Option Explicit Dim userName As String Sub myFirst()  userName = “Biff” End Sub Sub mySecond()     Msgbox userName End Sub

5. Visibility Levels • Keywords such as Public or Private are used to indicate their visibility, aka, their scope. • userName has project-level scope, all modules can access it • income has Module Level scope - only accessed in this module. This is the same as Private. • To limit visibility to subs in Module1, use "Private" instead of "Public". • Private - good for "helper" subs that have limited use or will only be called by subs in that module

6. Visibility Levels • Using Public makes variable available to the entire Project (all modules) • Using Dim outside of subroutines makes Module-Level Scope (same as Private). • Subroutines can be either Public or Private as well.

7. Visibility Levels See Scope in Chapter10ExamplesXXXX2012.xlsm

8. Modularity • break down a large problem into smaller ones • each subroutine should handle a small, complete problem • a program then consists of many subroutines and functions • each sub can call other functions or subs Sub MainProgram() ' The main program calls other subs     Call GetInput     Call ProcessData     Call DisplayResults End Sub The keyword CALL is optional, but it helps illustrate what is going on.

9. 3 Benefits of Modular Programming • Easier to read • Easier to debug • Software reuse • Ch10 shows how the Travelling Salesperson program from the last chapter has been broken down into smaller subroutines.

10. A Modular Program modularity.xls in MyLearningSpace Option Explicit ' Project level variables Public userName As String Public Sub MainProgram()     GatherData     ProcessTheMessage     DisplayGreeting End Sub Private Sub GatherData()     userName = InputBox("What is your name?", "Greetings", "-user-") End Sub Private Function Greeting(s As String) As String     Greeting = "Hello there, " & s End Function Private Sub ProcessTheMessage()     MsgBox "The length of the message is now " & _        Len(Greeting(userName)) & " characters.", _         vbOKOnly + vbInformation, _         Title:="Results" End Sub

11. Passing Arrays • pretty important, don't let the () mess you up • Write a subroutine that will sort an array of any size Sub CallingSub() Dim names(100) As String For i = 1 to 100         names(i) = Range("Names").Cells(i).Value Next     SortNames names   '<---- Pass the array to the another sub ' Call SortNames(names) ' can also be used End Sub

12. The Sub Sub SortNames(names() As String) Dim nNames As Integer     nNames = UBound(names) ' determines the upper index limit     ' More sorting code goes here End Sub • UBound returns the largest index of the array, ie: ArraySize -1 if using 0 based arrays (the default) • Can also use LBound to determine the lowest bound for the array

13. 3 Minute Challenge Write a subroutine that takes an array of any size and prints all the values. Take 3 minutes. GO! Solution on next slide.

14. Using LBound and UBound Sub PrintArray(theArray() As String) Dim i As Integer      ' Safer to check LBound and UBound       ' in case there is Base 0 or Base 1 confusion     For i = LBound(theArray) To UBound(theArray) MsgBox theArray(i)     Next End Sub

15. Function Subroutines Function CircleArea (r As Single) As Single If r > 0 Then         CircleArea = 3.14159 * r * r Else         CircleArea = 0  End If End Function • Can be used in VBA and in the Spreadsheet like a built-in function • If saved to personal.xlsb, you'll have to call it with =personal.xlsb.CircleArea(A5)

16. Generating Random Numbers See Example 10.4 - Functions.xlsm • use Application.Volatile to ensure that different values are calculated each time the sheet recalculates • matches the operation of the RAND spreadsheet function • VBA RND function generates a value between 0 and 1 with a uniform distribution

17. Workbook_Open Event Handler • code placed in this subroutine runs when the workbook is opened • not placed in a Module, its placed in the ThisWorkbook code window • lots of other Workbook events worth exploring

18. References For more details, check out: http://www.cpearson.com/excel/PassingAndReturningArrays.htm Recommended Exercises Chapter 10 - 1, 3, 4, 6, 8, 15, 16, 19