140 likes | 199 Views
ME 142 Engineering Computation I. Using Subroutines Effectively. Key Concepts. Functions vs. Subprograms Using Subroutines Effectively Declaring Variables. Functions vs. Subprograms.
E N D
ME 142Engineering Computation I Using Subroutines Effectively
Key Concepts Functions vs. Subprograms Using Subroutines Effectively Declaring Variables
Functions vs. Subprograms • A Function is a self-contained program that receives input through arguments (or parameters), performs calculations, and returns a result • A Subprogram is similar to a function, except it does not return a result
Functions vs. Subprograms • Functions may be used in formulas or expressions • Excel provides hundreds of built-in functions • Functions return a result but may not write to other cells in the spreadsheet • Subprograms may write directly to cells in the spreadsheet
Functions vs. Subprograms • Functions are launched from a formula • Subprograms may be launched from a button or as any macro • Subprograms offer the most flexibility and power, including the use of custom dialog boxes
Using Subroutines Effectively • Longer programs are commonly split up into smaller programs, sometimes referred to as subroutines • Typical scenario: • Main program passes information to a subroutine via argument list • The subroutine performs calculations and returns results to main program
Sub MathOp() '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 End Sub Sub Calculate(x, y, Oper, z) 'subroutine to perform math operation If Oper = "*" Then z = x * y ElseIf Oper = "-" Then z = x - y ElseIf Oper = "+" Then z = x + y ElseIf Oper = "/" Then z = x / y ElseIf Oper = "^" Then z = x ^ y Else MsgBox "Invalid Operation" z = "Error" End If End Sub
Declaring Variables • VBA supports a variety of Data Types, the most common being: • Integer • Single precision real • Double precision real • String • Variant
Declaring Variables • If you don’t declare the data type for a variable, VBA uses the default data type of variant • Data stored as variant changes types depending on what you do with it • With variant data types, VBA automatically handles conversion • Trade-off is you sacrifice speed and memory
Why Declare Variables • Declaring variables makes your program run faster and use memory more efficiently • You may “force” yourself to declare all variables by including “Option Explicit” as the first statement of your VBA module • Option Explicit • When this statement is present, you won’t be able to run your code if it contains any undeclared variables
Variable Declaration Examples • Dim x As Integer • Dim y As Single • Dim z As Double • Dim q As String • Dim a As Single, b As Single, c As Single
Life of Variables • Variables may declared at 3 levels • Procedure • Module • All Modules • For our purposes, Procedure variables will be sufficient
Procedure Variables • Most efficient because VBA frees up the memory they use when the procedure ends • To declare, simply use the Dim statement within the procedure • Dim x as Single
Static Variables • Static variables retain their value when the procedure ends • Static B as Integer • Could be used to keep a running total or track the number of times a program is called • Variables are purged/reset when • The workbook is closed or reopened • The reset toolbar button is clicked • When “end” statement/error message is executed