EIN 4905/ESI 6912 Decision Support Systems Excel

1
Spreadsheet-Based Decision Support Systems
Chapter 15 Sub Procedures and Function Procedures
Prof. Name
name_at_email.com Position
[phone] University Name
2
Overview

• 15.1 Introduction
• 15.2 Sub Procedures
• 15.3 Function Procedures
• 15.4 Public and Private Procedures
• 15.5 Applications
• 15.6 Summary

3
Introduction

• Calling sub procedures
• Executing function procedures to return a value
• Pass variables by reference and by value
• Private and public procedures
• Two applications which work with calling sub
  procedures and creating function procedures which
  pass variables

4
Sub Procedures

• Calling Other Procedures
• Passing Variables in Procedures

5
Organizing Sub Procedures

• You should group various actions into several
  smaller sub procedures rather than having one
  large sub procedure in which the entire program
  is written.
• The ideal module structure for your program is to
  have one Main() sub procedure from which other
  sub procedures are called.
• This Main macro will usually be assigned to a
  Start button on the Welcome sheet.
• To call another sub procedure, we use the command
  Call followed by the sub procedure name.

6
Organizing Sub Procedures (contd)

• For example, consider three different sub
  procedures, called GetInput(), Calculations(),
  and CreateReport().
• We can then call these three sub procedures from
  the Main() sub procedure as follows.
• Sub Main()
• Call GetInput
• Call Calculations
• Call CreateReport
• End Sub
• You can also call other sub procedures from these
  three sub procedures.

7
Function Procedures

• Passing Variables in Functions

8
Creating Function Procedures

• Function procedures are sub procedures which can
  pass variables or return values.
• A function procedure can be called from any sub
  procedure or any other function procedure, using
  the Call statement.
• Function FunctionName()
• .
• End Function

9
Passing Variables

• To pass a variable in VBA, you should insert the
  variable as an argument/parameter of the function
  when it is called.
• Call FunctionName(variable to be passed)
• If the function will receive a variable as input
  to the function, the function procedure statement
  must include a variable name as an argument.
• Function FunctionName(variable passed)

10
Passing Variables (contd)

• We create a function which calculates the sum of
  two values.
• Function Sum(a, b)
• End Function
• The variable name used as an argument when the
  function is called and the variable name used in
  the function procedure statement to not need to
  be the same.
• They must be of the same data type if the data
  type is specified in the function statement.
• Call Sum(x, y)

11
Passing Variables (contd)

• The variables a and b in this example do not need
  to be declared.
• They will be the variables used throughout the
  function procedure.
• In this case a and b will assume the respective
  data types assigned to x and y.
• If we had defined our function statement with
  data types in the argument, we would be
  restricted to only passing variables of that data
  type.
• Function Sum(a As Integer, b As Integer)

12
Passing Variables (contd)

• To return a value from a function in VBA, you
  should assign a value to the name of the function
  procedure.
• Function Sum(a, b)
• Sum a b
• End Function

13
Public and Private Procedures

• A sub procedure, like a variable, can also be
  defined as Public or Private.
• A private sub procedure is declared by putting
  the word Private before the Sub statement.
• Private sub procedures can only be called from
  procedures in the same module.
• Private sub procedures are also not listed when
  you try to run a macro in Excel.
• A public sub procedure can be called from any
  other procedure.
• The word Public can be put in front of the Sub
  statement, or the Sub statement can be written
  without a preceding statement.

14
Public and Private Procedures (contd)

• Consider four small sub procedures.
• Two of these procedures are private Test1() and
  Test2()
• Since they are in the same module, they can call
  one another.
• Private Sub Test1()
• MsgBox "This is Test1"
• Call Test2
• End Sub
• ----------------------------------
• Private Sub Test2()
• MsgBox "This is Test2"
• End Sub

15
Public and Private Procedures (contd)

• The third sub procedure called Test3() is public
  but in another module.
• We are not allowed to call either of the private
  sub procedures in the original module.
• That is, Test3() cannot contain the code Call
  Test1 or Call Test2.
• However, we can call this public procedure from
  one of our private procedures.
• Private Sub Test1()
• MsgBox "This is Test1"
• Call Test3
• End Sub

16
Public and Private Procedures (contd)

• The fourth sub procedure called Test4() is also
  public and in the same module as Test1() and
  Test2().
• Even though Test4() is public it can still call
  the private procedures since they are in the same
  module.
• Sub Test4()
• MsgBox This is Test4
• Call Test1
• End Sub

17
Applications

• Navigating Procedures
• Derived Math Functions

18
Applications

• Many functions are already available to us
  through Excel formulas and VBA math functions
  however, there may be specific needs depending on
  the program or DSS you are developing that may
  require a customized function.
• Navigating Functions
• Derived Math Functions

19
Navigating Functions

• In many of the case studies we develop, and in
  general good GUI design, there are several
  buttons in our workbook used to navigate through
  the different spreadsheets.
• Continue, Next, Back, Previous, etc.
• Each time one of these buttons is clicked, we
  want to close the current worksheet, that is hide
  it, and make the next appropriate worksheet
  visible.

20
Navigating Functions (contd)

• Consider a workbook with several worksheets.
• Input, Step 1, Step 2, and Optimization
• These worksheets should be viewed by the user in
  the order listed.
• If we click a Next button on the Input
  worksheet we want to hide the Input sheet and
  make the Step 1 sheet visible
• Sub NexSheett()
• Worksheet(Step 1).Visible True
• Worksheet(Input).Visible False
• End Sub

21
Navigating Functions (contd)

• We cannot assign this same macro to the Next
  button found on the Step 1 worksheet.
• If we press Next on the Step 1 sheet, we want
  to make the Step 2 sheet visible and hide the
  Step 1 sheet.
• Sub NexSheett()
• Worksheet(Step 2).Visible True
• Worksheet(Step 1).Visible False
• End Sub

22
Navigating Functions (contd)

• Our solution is to create a function procedure
  which passes a worksheet name as its variable we
  call this the NextSheet() function.
• We can capture the desired worksheet name in each
  unique sub procedure associated with the buttons
  on each sheet and then call a function to make
  this worksheet visible and hide the current
  worksheet.
• Public name As String
• -------------------------------------------------
  -
• Sub InputNext()
• name Step 1
• Call NextSheet(name)
• End Sub
• -------------------------------------------------
  -
• Function NextSheet(name)
• Worksheets(name).Visible True
• ActiveSheet.Visible False
• End Function

23
Figure 15.1

• We use this function structure above to assign
  related macros to the Next button on each of
  the sheets in our workbook.

24
Navigating Functions (contd)

• We have a particular sheet that is usually hidden
  but which can be shown at some point this is an
  Example worksheet which the user may refer to
  at times while using our program.
• To view this Example sheet, there may be a
  View Example button on all other sheets in the
  workbook.
• If this button is clicked, we want to close the
  current sheet and show the Example sheet.
• On the Example sheet we may have a Return to
  Program button which should re-open the
  previously visible sheet.

25
Figure 15.2

• To perform these actions, we need to capture the
  original worksheet name in which we first click
  the Example button so that we know which sheet
  to re-open when the Return to Program button is
  clicked.
• Public ws As Worksheet
• ----------------------------------------------
• Sub ViewExample()
• Worksheets(Example).Visible True
• Call CloseCurrent()
• End Sub
• ----------------------------------------------
• Function CloseCurrent()
• Set ws ActiveSheet
• ws.Visible False
• End Function
• ----------------------------------------------
• Sub ReturnToProgram()
• Ws.Visible True
• Worksheets(Example).Visible False
• End Sub

26
Derived Math Functions

• As we saw in Chapter 13, the pre-defined VBA math
  functions and trigonometric functions can be used
  to derive new functions.
• To actually create these derived functions in
  Excel, we create a function procedure and both
  pass a variable and return some value.

27
Derived Math Functions (contd)

• To create the Log base n derived math function,
  we could create the following function.
• Function LogBaseN(x, n)
• LogBaseN Log(x) / Log(n)
• End Function

28
Derived Math Functions (contd)

• The values for x and n would need to have been
  assigned in the sub procedure which calls this
  function prior to calling the function.
• In these functions we may not directly call the
  function using the Call statement we can instead
  just refer to the function name, such as in a
  Message Box or another function.
• Sub FindLog()
• Dim x, n As Integer
• x InputBox(Enter x value of Log base n
  of x function )
• n InputBox(Enter n value of Log base n of
  x function )
• MsgBox The value of Log base n of
  x is LogBaseN(n, x)
• End Sub

29
Summary

• The ideal module structure for a program is to
  have one Main() sub procedure from which other
  sub procedures are called.
• Function procedures are similar to sub procedures
  and follow this basic structure
• Function FunctionName()
• .
• End Function
• To call another sub or function procedure, use
  the command Call followed by the sub procedure
  name.
• To pass a variable in VBA, you should insert the
  variable as an argument/parameter of the function
  when it is called. Use the following structure to
  call a function
• Call FunctionName(variable to be passed)
• A sub procedure, like a variable, can also be
  defined as Public or Private.

30
Additional Links

• (place links here)