Spreadsheet-Based Decision Support Systems Chapter 15: Sub Procedures and Function Procedures Prof. Name firstname.lastname@example.org Position (123) 456-7890 University Name
Overview • 15.1 Introduction • 15.2 Sub Procedures • 15.3 Function Procedures • 15.4 Public and Private Procedures • 15.5 Applications • 15.6 Summary
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
Sub Procedures • Calling Other Procedures • Passing Variables in Procedures
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.
Organizing Sub Procedures (cont’d) • 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.
Function Procedures • Passing Variables in Functions
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
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)
Passing Variables (cont’d) • 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)
Passing Variables (cont’d) • 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)
Passing Variables (cont’d) • 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
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.
Public and Private Procedures (cont’d) • 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
Public and Private Procedures (cont’d) • 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
Public and Private Procedures (cont’d) • 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
Applications • Navigating Procedures • Derived Math Functions
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
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.
Navigating Functions (cont’d) • 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
Navigating Functions (cont’d) • 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
Navigating Functions (cont’d) • 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
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.
Navigating Functions (cont’d) • 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.
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
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.
Derived Math Functions (cont’d) • 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
Derived Math Functions (cont’d) • 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
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 Callfollowed 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.
Additional Links • (place links here)