1 / 14

Efficient Use of Subroutines for Engineering Computation

Learn key concepts of using functions and subprograms effectively in engineering computation. Understand benefits of declaring variables and how to utilize subroutines for efficient programming in VBA.

alexa-carlson
Download Presentation

Efficient Use of Subroutines for Engineering Computation

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ME 142Engineering Computation I Using Subroutines Effectively

  2. Key Concepts Functions vs. Subprograms Using Subroutines Effectively Declaring Variables

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

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

  9. 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

  10. 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

  11. 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

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

  13. 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

  14. 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

More Related