# Ch 10: More on Variables and Subroutines - PowerPoint PPT Presentation

1 / 18

Ch 10: More on Variables and Subroutines. CP212 Winter 2012. 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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

Ch 10: More on Variables and Subroutines

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

## Ch 10: More on Variables and Subroutines

CP212

Winter 2012

### 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)

### 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.

### 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

Sub myFirst()

End Sub

Sub mySecond()

End Sub

### 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

### 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.

### Visibility Levels

See Scope in Chapter10ExamplesXXXX2012.xlsm

### 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.

### 3 Benefits of Modular Programming

• Easier to debug

• Software reuse

• Ch10 shows how the Travelling Salesperson program from the last chapter has been broken down into smaller subroutines.

### A Modular Program

modularity.xls in MyLearningSpace

Option Explicit

' Project level variables

Public Sub MainProgram()

GatherData

ProcessTheMessage

DisplayGreeting

End Sub

Private Sub GatherData()

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

### 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

### 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

### 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.

### 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

### 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)

### 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

### 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

### 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