Ch 10 more on variables and subroutines
This presentation is the property of its rightful owner.
Sponsored Links
1 / 18

Ch 10: More on Variables and Subroutines PowerPoint PPT Presentation


  • 66 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Ch 10: More on Variables and Subroutines

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


Ch 10 more on variables and subroutines

Ch 10: More on Variables and Subroutines

CP212

Winter 2012


Topics

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

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

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


Modularity

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

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

    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


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

    Next

        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)

    Next

End Sub


Function subroutines

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

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


References

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


  • Login