ME 142 Engineering Computation I Using Subroutines Effectively

Key Concepts Functions vs. Subprograms Using Subroutines Effectively Declaring Variables

Functions vs. Subprograms w A Function is a self-contained program that receives input through arguments (or parameters), performs calculations, and returns a result w A Subprogram is similar to a function, except it does not return a result

Functions vs. Subprograms w Functions may be used in formulas or expressions w Excel provides hundreds of built-in functions w Functions return a result but may not write to other cells in the spreadsheet w Subprograms may write directly to cells in the spreadsheet

Functions vs. Subprograms w Functions are launched from a formula w Subprograms may be launched from a button or as any macro w Subprograms offer the most flexibility and power, including the use of custom dialog boxes

Using Subroutines Effectively w Longer programs are commonly split up into smaller programs, sometimes referred to as subroutines w Typical scenario: n n Main program passes information to a subroutine via argument list The subroutine performs calculations and returns results to main program

Sub Math. Op() 'Demonstrates using subroutines 'Key Variables: ' Oper - math operation (* - + / ^) ‘Get input from spreadsheet A = Cells(1, 2) B = Cells(2, 2) Oper = Cells(2, 1) 'Call subroutine to perform calculation Call Calculate(A, B, Oper, C) 'Output results to spreadsheet Cells(3, 2) = C Sub Calculate(x, y, Oper, z) 'subroutine to perform math operation If Oper = "*" Then z=x*y Else. If Oper = "-" Then z=x-y Else. If Oper = "+" Then z=x+y Else. If Oper = "/" Then z=x/y Else. If Oper = "^" Then z=x^y Else Msg. Box "Invalid Operation" z = "Error" End If End Sub

Declaring Variables w VBA supports a variety of Data Types, the most common being: n n n Integer Single precision real Double precision real String Variant

Declaring Variables w If you don’t declare the data type for a variable, VBA uses the default data type of variant w Data stored as variant changes types depending on what you do with it w With variant data types, VBA automatically handles conversion w Trade-off is you sacrifice speed and memory

Why Declare Variables w Declaring variables makes your program run faster and use memory more efficiently w You may “force” yourself to declare all variables by including “Option Explicit” as the first statement of your VBA module n Option Explicit w When this statement is present, you won’t be able to run your code if it contains any undeclared variables

Variable Declaration Examples w Dim x As Integer w Dim y As Single w Dim z As Double w Dim q As String w Dim a As Single, b As Single, c As Single

Life of Variables w Variables may declared at 3 levels n n n Procedure Module All Modules w For our purposes, Procedure variables will be sufficient

Procedure Variables w Most efficient because VBA frees up the memory they use when the procedure ends w To declare, simply use the Dim statement within the procedure n Dim x as Single

Static Variables w Static variables retain their value when the procedure ends n Static B as Integer w Could be used to keep a running total or track the number of times a program is called w Variables are purged/reset when n The workbook is closed or reopened The reset toolbar button is clicked When “end” statement/error message is executed