Outline Writing VBA Statements
Writing VBA Statements
Writing VBA Statements
Larry Caretto Mechanical Engineering 209
Programming for Mechanical Engineers
February 7, 2017
February 7, 2017
Outline
? Review last class ? How to write programming statements ? Information transfer in functions and subs ? Arithmetic operators and precedence ? Declaring variables ? Second programming assignment
2
Review Program Variable Names
? Look like mathematical variables but are really references to memory locations
? Not case sensitive (Editor will maintain consistent use of capital letters)
? 1 to 255 characters (A-Z, a-z, 0-9, _) ? Name must start with a letter ? Editor will not allow use of reserved words ? Use meaningful names that are easy to
understand
3
Review Program Statements
? Sequential order unless a control statements is used
? An assignment statement gives a value to a variable: KE = m * V^2 / 2
? Choice statements choose different parts of code to execute depending on program variables
? Looping code repeats statements with different data (conditional and count? controlled loops) 4
Review VBA Editor Navigation
? Various ways to transfer between Excel
worksheet and VBA editor
? Click Visual Basic on Code group of developer tab
? Use Alt-F11 shortcut key
? Put VB icon on QAT
? Return from editor to Excel
? Excel icon on VBE toolbar
? Alt-F11 shortcut key
Can also use taskbar icon
5
Project Explorer
Code Window
Properties Window (not required in ME 209)
Windows of Visual Basic Editor (VBE)
6
ME 209 ? Programming for Mechanical Engineers
1
Writing VBA Statements
February 7, 2017
Inserting a Module
? Click Insert in Visual Basic Editor
? Click on Module in resulting menu
? Can enter multiple functions and subs in one module
New module into active
workbook
7
Function Structure
Function cylVol(R As Double, _ H As Double) As Double
Const PI As Double = 3.14159265358979 cylVol = PI * R ^ 2 * H
End Function
Must have statement assigning value to function name
? First statement is function header with argument list that transfers information to and from functions and subs
8
Sub Procedure Structure
Sub TestCylVol() Dim R As Double `Cylinder Radius Dim H As Double `Cylinder Height R = InputBox("Enter the Radius:" ") H = InputBox("Enter the Height:" ") MsgBox "V = " & cylVol(R,H)
End Sub
? Input taken from user and written to standard output box, MsgBox
? Calls cylVol function from previous slide
9
Running Sub TestCylVol
Input Box
MsgBox for inputs R = 1 and H = 10
10
Writing Statements
? Start each statement on a new line
? Press enter to terminate a statement
? Long statements can be continued over more than one line by placing a space followed by an underscore ( _) after each incomplete line in the statement
? Not recommended, but you can place more than one statement per line if a colon (:) separates statements
x=3 : y=2
11
Comments in Code
? Comments are information for humans that are ignored by the computer
? Used to explain what is going on in the code to make it easier to read
? Start comment with an apostrophe (`)
? Font turns green for completed comment
? Can place comments on separate line or after completed program statement
? Not after initial lines of continued statement
_ `this comment is illegal12
ME 209 ? Programming for Mechanical Engineers
2
Writing VBA Statements
February 7, 2017
Writing Comments
? Put overview of what procedure is supposed to do start of procedure
? Explain what individual parts of code are supposed to do before the code
? Change comments with code changes ? Do not overcomment (example below)
`The following statement sets x equal to y x = y
13
Information Transfer
? Variables listed in function (or sub) header called argument list
cylVol(radius As Double, height As Double)
? A call to this function from the worksheet, =cylVol(A1, B1), transfers information based on the order of the arguments radius and height
? Order of arguments is the only thing that matters in procedure arguments
=cylVol(height, radius)??? 14
Information Transfer in Macros
? Subs without arguments, executed from the worksheet, are called macros
? Such subs typically use statements like the following to exchange information
R = Range("b1").Value H = Range("b2").Value V = PI * R^2 * H Range("b3").Value = V
? First two get information from the worksheet; last one puts information there
15
Arithmetic Operations
? The following symbols are used
? Addition +
Subtraction ?
? Multiplication * Division /
? Exponentiation ^ Grouping ( )
? Some expressions with variables using these operators are shown below
? y = x + 3 / z^2
? area = PI * radius^2
? ratio = x + y / p + q
16
Operator Precedence
? Arithmetic expressions are evaluated in
stages using operator precedence
? All operations at a given precedence level are
done before any operations on the next level
Exponentiation ^
Opposite order on
Unary minus ? (E.g. ?x) Excel worksheet
Multiply/Divide * /
Integer Division \
Remainder Mod (E.g. 7 Mod 4 = 3)
Addition/Subtraction + ?
17
Operations in Parentheses
? These are done first
? Can be used to overcome normal rules
of precedence
34
? What if you wanted
25
? 3 + 4 / 2 + 5 = 3 + 2 + 5 = 10 with
normal rules of precedence
? (3 + 4) / (2 + 5) = 7 / 7 = 1
? Must use parentheses to get correct mathematical results in some cases
18
ME 209 ? Programming for Mechanical Engineers
3
Writing VBA Statements
February 7, 2017
Library Functions
? VBA has function library with many
different functions
? Common ones are trig functions (sin, cos, tan), ex (exp), and square root (sqr)
? Examples of function use
y = exp(a * x) * sin(b * x) + c * z
x = exp(y)
disc = sqr(b^2 ? 4 * a * c)
4
PI = 4 * atn(1) `atn is arctangent function
4
1
19
ArcSine and ArcCosine
? VBA has no arcsine or arccosine functions
? Can write your own as follows
Function ArcSin(X As Double) As Double ArcSin = Atn(X / Sqr(-X * X + 1))
End Function
Function ArcCos(X As Double) As Double ArcCos = Atn(-X / Sqr(-X * X + 1)) _ + 2 * Atn(1)
End Function
Reference:
Accessed January 31, 2017
20
Exercise
? Write the following equations as VBA
statements
Use variable names p, x, pi, sigma;
functions are sqr for square root
?
and exp for exponential, ex
p = exp(-(x/sigma)^2/2) / sqr(2*pi*sigma)
?r
Use variable names r, x,
r = ( 3x^2 - 6x + 7 ) / ( 4x^2 ? 8 )
21
What Kind of Information
? Computers process different types (kinds) of data
? Counting variables (only integers) ? Real variables (with decimal points) ? String variables (names and labels) ? Decision (Boolean) variables (true/false) ? Date and time variables
? Computer storage, all ones and zeros, has different structure for each type
22
Data Types for VBA Variables
Data Type Byte Boolean Integer Long (integer) Currency (scaled integer)
Single Double
String (fixed-length) String (variable-length) Date
Memory Size 1 byte 2 bytes 2 bytes 4 bytes 8 bytes
4 bytes 8 bytes
string length (bytes) 10 bytes + length 8 bytes
Range
0 to 255 TRUE (non-zero) or FALSE (0) -32,768 to 32,767 -2,147,483,648 to 2,147,483,647 -922,337,203,685,477.5808 to 922,337,203,685,477.5807 (+/-) 1.401298E-45 to 3.402823E38 (+/-) 4.94065645841247E-324 to 1.79769313486232E308 1 to ~65,400 characters 0 to about 2 billion characters time of day + dates between 1/1/100 to 12/31/9999
Shaded types are most common for engineering VBA
23
Variant Data Type in VBA
? Same as type for Excel cell ? treats any kind of variable
? Requires 16 bytes to keep information on actual type of variable as well as its value (more bytes for longer strings)
? Default if variable is not assigned a specific type in a Dim statement
? Can declare variables to be of this type
24
ME 209 ? Programming for Mechanical Engineers
4
Writing VBA Statements
February 7, 2017
Declaring Variables
? Use Option Explicit statement at top of module to force variable declaration
? Provides check on errors due to misspelled variables names (strong typing)
? Submissions that do not do this will have automatic penalty of 10% of grade
? Can set VBE options to do this for you
? In VBE go to Tools | Options and check box next to Require Variable Declaration
25
Declaring Variables
? Use the following examples Dim x As Double Dim k As Long Dim s As String, d as Date Dim y as Double, z As Double
? Do not use the following syntax Dim x, y As Double
? In statement above x has default data type of Variant
26
Declaring Variables II
? Dim statements are usually placed at the start of a procedure
? Can be placed anywhere before the first use of the variable
? Variables in procedure argument lists and functions are typed in headers
Sub test (x as double, y as double) Function mine (d as Date) as Boolean
Setting data type of function
27
Declaring Variables III
? Good practice for declaring variables
? Use a separate Dim statement for each variable
? Place a comment after the end of the Dim statement to describe variable
? Some examples of this below Dim V0 as Double `Initial velocity Dim name as String `Part name for order Dim done as Boolean `True if code finished
28
Variable Names
? Must start with letter and can only use letters, numbers and underscore ( _ )
? Use meaningful variable names
? chamberPressure, mainStrutStress
? Use capital letters to set off different words
? This is more work at start, but makes program easier to read and understand
? Ease is especially important for others who use your program or for yourself when you use a program after some period of time
29
Symbolic Constants
? Useful way to program items that are constant or not expected to change often
? Syntax:
Const PI as Double = 3.14159265358979
? A const cannot be assigned a value in any other statement
? Use for mathematical and physical constants and infrequently changed items such as an overhead rate
30
ME 209 ? Programming for Mechanical Engineers
5
Writing VBA Statements
February 7, 2017
Second Programming Assignment
? Download from course web site
? Use simple VBA code to do same calculations from first assignment
? Write a VBA function with molar mass ? Write a macro with molar mass ? Write a VBA function with species name ? Write a macro with species name ? Download workbook pa2Start.xlsm from
course web site to start assignment 31
First Exercise
? Write a VBA function that has molar mass, pressure and temperature as input parameters and returns the density
? Calculation is simple: = MP/RuT = P/RT ? How do you write function header to get
information from worksheet? ? How do you call function from worksheet? ? Detailed instructions on assignment
32
First Exercise continued
? Insert a VBA module (see slide 7) ? Write code for all parts of this assignment
in the module ? Write Option Explicit at top of module to
force declarations of all variables ? Next write Const statement for Ru
Const universalGasConstant As Double = 8.31447 'kJ/kmol-K
? Write VBA code for idealGasDensity function
33
Second Exercise
? Write a macro ? Macro is a sub with no arguments ? Get information from worksheet by
statements like P = Range("B4").Value ? Send results back to worksheet by
statements like Range("B6").Value = rho ? Call function from Task one to compute
ideal gas density ( = MP/RuT = P/RT)
34
ME 209 ? Programming for Mechanical Engineers
6
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- vba text files tutorialspoint
- outline writing vba statements
- learn vba for excel
- introductory notes variables equations functions
- chapter 1 vba
- wonderware application server scripting implementations
- make excel talk with vba
- vba programming data visualization part 2
- l7 vba workbook sheets and cells
- the basics of vba programming
Related searches
- how to find financial statements of companies
- companies financial statements online
- financial statements publicly traded company
- personal purpose statements examples
- how to read financial statements 101
- closing statements for employee reviews
- sample statements for employee evaluation
- philosophy of education statements samples
- persuasive writing outline template
- writing outline worksheet
- essay writing outline template
- informative writing outline pdf