Ch 10 more on variables and subroutines
1 / 18

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

  • Uploaded on

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.
Download Presentation

PowerPoint Slideshow about ' Ch 10: More on Variables and Subroutines' - jack

An Image/Link below is provided (as is) to download presentation

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 - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript


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


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

Visibility levels
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 levels1
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 levels2
Visibility Levels

See Scope in Chapter10ExamplesXXXX2012.xlsm


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

A modular program
A Modular Program

modularity.xls in MyLearningSpace

Option Explicit

' Project level variables

Public userName As String

Public Sub MainProgram()




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, _


End Sub

Passing arrays
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


        SortNames names   '<---- Pass the array to the another sub

    ' Call SortNames(names) ' can also be used

    End Sub

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


End Sub

Function subroutines
Function Subroutines

Function CircleArea (r As Single) As Single

If r > 0 Then

        CircleArea = 3.14159 * r * r


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


For more details, check out:

Recommended Exercises

Chapter 10 - 1, 3, 4, 6, 8, 15, 16, 19