project
Ayoub-94
handout.zipME209-1ClassNotesWeek10.pdf
Maclaurin Series Sine
Create a Function procedure to calculate sin 𝑥 using the Maclaurin series expansion for the sine function (Maclaurin series are named after the Scottish mathematician Colin Maclaurin):
where 𝑥 is in radians.
The terms of this series expansion get smaller and smaller. Calculate sin 𝑥 using the diminishing terms until you encounter one below a "tolerance" value 𝜀 = 1E-5.
Create your own Function procedure to calculate the factorial needed in the equation. For example:
term = (-1)^n*x^(2*n+1)/factorial(2*n+1)
The solution looks like this:
Function MySin(x As Double) As Double
'Declare local variables
Dim term As Double, tol As Double, n As Integer
'initial conditions
tol = 0.00001 'tolerance value
n = 0 'n for first term
term = x 'first term value
MySin = term
'loop until tolerance achieved
Do While Abs(term) > tol
n = n + 1 'next value of n
term = (-1) ^ n * x ^ (2 * n + 1) / _
Factorial(2 * n + 1) 'next term value
MySin = MySin + term 'update sin(x)
Loop
End Function 36 executable statements to compute sin(𝜋/4)
Another approach...
It helps to recognize the following pattern in the above term sequence...
n term(n)
0 x^1/1!
1 -x^3/3! = -term(0) * x^2/(3*2)
2 x^5/5! = -term(1) * x^2/(5*4)
3 -x^7/7! = -term(2) * x^2/(7*6)
By taking advantage of this pattern, a more efficient code can be written, as shown on the following page.
Function MySin2(x As Double) As Double
'Declare local variables
Dim term#, tol#, p%
'initial conditions
tol = 0.00001 'tolerance value
p = 1 'power of first term
term = x 'first term value
MySin2 = term 'initial value of sin(x)
'loop until tolerance achieved
Do While Abs(term) > tol
p = p + 2
term = -term * x ^ 2 / (p * (p - 1))
MySin2 = MySin2 + term
Loop
End Function 12 executable statements to compute sin(𝜋/4)
Creating an Add-In
First: Make sure your code is working!
Second: Protect your code with a password. Click on "Tools -> VBA Project Properties" to set a password. You don't want your users to meddle with your code.
Save this password somewhere; it cannot be easily recovered. Consider that you may need to modify your code YEARS from now.
Third: From the worksheet, use "Save As" and select file type "Excel Add-In (*.xlam)" from the "Save as type" dropdown menu, type in the desired Add-In file name, then click “Save”.
Notice that when you chose the file type, Excel changed to the default directory where is stores Add-Ins. Excel will automatically load Add-Ins located there when the application starts.
You can save the file anywhere you want, however.
Fourth: Exit and re-start Excel, so that your Add-In will be loaded.
Fifth: Enable the new Add-In. In "File -> Options", choose "Add- Ins", and then click "Go...".
This will open the "Add-Ins" window where you can enable your new Add-In by checking the box next to its name.
If you did not save the *.xlam file to the default folder, you will need browse to load the file first.
Now your Add-In will be available to use on your worksheets and in your VBA projects every time that you run Excel.
Sixth: Share the *.xlam file and these installation instructions with your friends and colleagues to impress them with your VBA prowess!
__MACOSX/._ME209-1ClassNotesWeek10.pdf
ME209-1ClassNotesWeek3.pdf
Programming for Mechanical Engineers (ME209)
Class Notes - Week 3
Programming for Mechanical Engineers (ME209)
Part 1: Introduction to Microsoft Excel
(continued)
More About Excel Built-In Functions
A function has the general form:
function_name(argument_1, argument_2, argument_3, ...)
We have used the function Pi(), which did not require any arguments, but most functions require arguments, and the SUM(), COUNT(), and AVERAGE() functions that can accept many arguments.
Now let’s look at functions that implement LOGIC. Consider the following table of sales data:
The data columns have been given Range Names, constructed using the labels in the header row.
Thusly, the following named ranges are defined:
Now, we can have some fun with the logical functions COUNTIF(), COUNTIFS(), SUMIF(), and SUMIFS().
Month
Region
Sales
Enter the following formulas using the COUNTIF() and COUNTIFS() built-in functions:
#occurrences of "North" in named range Region #occurrences of "300" in named range Sales #occurrences of ">300" in named range Sales #occurrences of "<>100" (not equal to 100) #occurrences with exactly 5 letters or numbers #occurrences containing an "h" anywhere
simultaneous conditions: #where both are true
Now practice using the SUMIF() and SUMIFS() built-in functions:
Programming for Mechanical Engineers (ME209)
Course Part 2
Introduction to the Excel Visual Basic Environment
Developer Tab By default, Excel does not display the Developer tab.
To turn-on this tab, click "File -> Options -> Customize Ribbon" and then under "Customize the Ribbon -> Main Tabs", select the Developer check box, and finally press "OK".
To access the Visual Basic Environment (VBE),
• Choose the Developer tab and select "Visual Basic" from the menu:
OR
• Press "Alt+F11"
OR
• Right-click on a worksheet tab and select "View Code".
Be careful using the last option. It takes you to the VBE, but opens up a code window for the current worksheet. In ME209, we do not program in worksheet code windows, and any code there will not be graded. You will learn how to create a "code module" where your code will be entered.
Visual Basic Environment
Project Explorer
Note your workbook "project" section. VBA Code Window
Properties Window
This is where you can view and modify properties of a selected project object.
Watch Window
If Project Explorer, Properties Window, or Watch Window are not shown, you can open them by selecting them on the View menu.
Creating a Code Module
In the Project Explorer window, right-click on any object in your project, and select "Insert -> Module". The VBE will create a new code module in the Modules folder, and will open the code editor window for it.
Module Name
You can change the module name here.
VBA Code
Window
More about Code Modules
Double-click on a code module in the Project Explorer window to open its code editor window. Take care to put your work in code module objects ONLY. In ME209, our code will only be written in code modules.
Code modules contain your VBA statements, most organized into procedures (functions and subs), which perform useful work. Code modules are stored with the Excel workbook when you save it, but only if you save with the file type *.xlsm (that is, "macro enabled").
Code: Statements in the VBA programming language to perform calculations and change the properties of Excel objects.
Procedure: A unit of computer code (statements) that performs a specific function or group of functions.
Example: Create a Function Procedure
Develop the function "TankVolume" to calculate the volume of a right cylindrical tank, given the inputs of radius and height. Enter the following code in the Module1 code editor window:
This is now a new User Defined Function (UDF), and can be used on our Storage Tank worksheet that we created in Part 1, just like any Excel built-in function.
VBA expression and assignment statement, assigning expression result to the function return value.
Function end statement (all procedures must have an end statement).
Function statement, declaring the function name and arguments.
Note that VBA does not have a built-in function for Pi(), so we use 4*Atn(1), which equals Pi().
Using Your Function in a Worksheet Formula
In our previous storage tank example, we wrote the volume calculation using a worksheet formula in the cells of column C.
Now use your UDF in the cells of column E.
The VBA function definition declares local procedure variables to correspond with the values that are passed. The argument order must match, but the names can be different (names are not case sensitive).
A Closer Look at Passing Arguments to Procedures
The cell values in this example are actually copied to temporary memory locations, and the addresses of those locations are passed to the function. This is called passing by value.
The function can change the local variable values, but it won't effect the calling procedure or worksheet values.
Temporary Memory 000...110
000...001
A Closer Look at Returning a Result from a Function Procedure
In our function, named "TankVolume", the corresponding procedure variables "radius" and "height" can be used in any calculations. It is said they have "scope" in the function.
Within the function, the value to be returned is assigned to the function name.
When the function ends, the function value in temporary memory is copied to the formula location where the function was used.
Temporary Memory 000...110
000...001
Avoid Ambiguous Module and Procedure Naming
Be careful when naming modules and procedures. If they have the same names, Excel and VBA get confused, and generate an error. For example:
Function name is "mytank", which is the same as a module (names are not case- sensitive)
"Name" error results when trying to use the function on a worksheet.
Module name is "myTank"
Editor Tab
Auto Syntax Check VBE checks the syntax as you enter lines of code.
Require Variable Declaration VBE inserts "Option Explicit" command at the top of each new code module. Then, VBA will require that you explicitly define each variable that is used within your program.
Customizing the VBE
Select "Tools -> Options -> Editor". Please match selections on your editor with the selections shown here. Remember, every machine (i.e. computer) you are going to use must be changed to the same settings. Many are set by default, but it is worth checking.
Auto List Members When type the name of a VBA object, VBE displays a list of member objects (methods and properties). For example:
Auto Quick Info
When you type the name of a built-in function, VBE shows the arguments needed. For example:
Auto Data Tips One of the best debugging tools. You can hover with your mouse on a variable to display its value. More on this later when we discuss debugging.
Auto Indent VBE automatically indents each new line of the code with the number of spaces defined by "Tab Width". This helps you write code that is easier for you to read and debug.
Use the "Tab" key at the beginning of a line where you want indenting to start.
Use "Shift+Tab" or the "Backspace" key at the beginning of a line where you want indenting to end.
Please use 2 or 3 spaces for Tab Width.
Editor Format Tab
Font Size Option Set this to 10 or 12 for assignments that you print to turn in.
Set this to 24 for code you share in class on the projector.
Programming for Mechanical Engineers (ME209)
Course Part 3
VBA Syntax, Arithmetic & String Operators, Built-In Functions, Sub
Procedures
Object-Oriented Programming
Excel is an application object within the Microsoft Office Suite. Excel follows an object-oriented approach in its design. In object-oriented programming, an object is a unit of code or data storage that performs a function or contains data. An object may include subordinate objects.
By analogy, think of a town as an object. A town consists of many subordinate objects, such as streets, houses, and businesses. In turn, a house consists of subordinate objects such as a roof, walls, floor, furniture, and appliances. Each of those objects can be further broken down, as needed, to define the system of objects for the town.
Likewise, Excel is comprised of subordinate objects, such as workbook objects that, in turn, contain one or multiple worksheet objects and other objects we will learn about in this course – such as your VBA code modules. Worksheet objects contain such items as charts, cells, and cell ranges.
Objects may consist of data, properties, methods (a.k.a. "functions"). Cells, for example, contain values (numerical or text), but also have properties, such as font color and size, background color, and other formatting properties.
In the programming language of Visual Basic for Applications (VBA), we will refer to objects within the Excel application so that we can use their data and methods, and affect their properties. The syntax (language structure) for doing that requires an understanding of the hierarchical relationships of the objects within Excel, and the use of dot notation ("." ) to refer to objects. This is illustrated by the following example.
Assume that you opened a new Excel workbook, which is named "Book1" by default. Then the following describes how you might refer to objects within Excel.
This is the object name of the Excel application itself, and is a container of all of included subordinate objects within it:
Application
You can refer to a specific workbook member of this application by using following dot notation:
Application.Workbooks("Book1")
Where "Book1" is the name of an open workbook. Note the dot (".") that separates the "parent" object from its "child" object.
The following refers to a specific worksheet member object of this workbook:
Application.Workbooks("Book1").Worksheets("Sheet1")
The above line of "code", which we will also refer to as a "VBA Statement", is rather long. Fortunately, Excel allows us to take some shortcuts. For instance, if we don't specify the Application object, Excel will assume that we refer to the Excel Application, so the following works:
Workbooks("Book1").Worksheets("Sheet1")
Also, if Book1 is the currently selected ("active") workbook, we can use the following shortcut to refer to the same worksheet object:
Worksheets("Sheet1")
But if you need to refer to a sheet in another open workbook, you will need to include a reference to it as above.
We can refer to a cell on the worksheet using this syntax:
Worksheets("Sheet1").Cells(irow, icol)
Where "irow" is the row number and "icol" is the column number of the cell on Sheet1.
You can write a value to the cell referenced above by using the assignment operator ("=") and specifying the value to be written. For example:
Worksheets("Sheet1").Cells(irow, icol) = 3
If we are sure that the desired sheet is currently the selected (active) worksheet, then we can simply write this to set its value:
Cells(irow, icol) = 3
But remember, it is up to you to be sure that the correct sheet is active, or that you specify it, and that the correct workbook is active, or that you specify it, using the syntax described above. We will discuss this in more detail later when we start writing VBA code, but this should give you a good understanding of how Excel is organized. That understanding will help you be a better VBA programmer.
Variable, Sub, and Function Names
VBA names for variables, Sub procedures, and Function procedures...
...must start with a letter and may contain letters, numerals, and the underscore character (_). Use meaningful names; this will help you in debugging. For example, call a displacement variable “displacement” instead of just “x”.
...can be as long as you like.
...may not have spaces. For example, “wave_speed” or “WaveSpeed” are acceptable, but “wave speed” will result in an error.
...are not case sensitive, so “Density” and “density” are the same variable. However, Excel will ensure that the case matches everywhere that the variable name is used in your workbook.
Caution: In the Courier font (VBE default), the uppercase letter “O” and number “0” are hard to differentiate, and worse are the lowercase letter “l” and number “1”.
Example:
a1O = al0
Is that an l ("el") or a 1 (one) on the left? Is that an O ("oh") or a 0 ("zero") on the left?
Good programming practice is to give meaningful names to variables. It makes it easier for others to understand what you have created, and how it works.
If the variable is used only temporarily (such as in a very small section of code), it is OK to use something like “i”, “j”, “k” (customarily used for integer types), or “x”, “y”, “z” (customarily used for floating point types).
However, when defining important variables such as those that are passed to and from other procedures, name variables in a way that makes it clear what they represent, such as:
mass, density, elasticity, stress
Often two or more words are needed to clearly define variables, such as:
fluid_density = fluid_mass / fluid_volume
weekly_pay = hours_worked * pay_rate
The above examples are using the underscore character “_”, which is the only special character Excel allows.
Another popular style of naming when multiple words are needed is called the “Camel Method”:
gasDensity = gasMass / gasVolume
Variable Types
Data are stored in memory in different ways, according to the variable type. A list of the most common variable types used in engineering programs are:
Integer Types Max (+/-) Storage Declaration Examples
Integer 32,767 2 bytes (16 bits) “Dim m as Integer” or “Dim m%”
Long 2,147,483,647 4 bytes (32 bits) “Dim n as Long” or “Dim n&”
Floating-Point Types Max (+/-) Precision Storage Declaration Examples
Single 10^38 ~7 sig. fig’s 4 bytes (32 bits) “Dim x as Single” or “Dim x!”
Double 10^308 ~15 sig. fig’s 8 bytes (64 bits) “Dim y as Double” or “Dim y#”
Character Types Max. Length Storage Declaration Examples
Variable Length > 2 billion up to ~2GB “Dim s2 as String” or “Dim s2$”
Fixed Length > 65 thousand up to ~65KB “Dim s1 as String*50”
Logical Type Possible Values Storage Declaration Example
Boolean True or False 2 bytes (16 bits) “Dim x as Boolean”
Variant Variables
Variables that are typed as Variant change to whatever type is assigned to it. Example: Dim y As Variant, i As Integer, pi As Double
i = 0
pi = 3.14159
y = i ‘now y is type Integer
y = pi ‘now y is type Double
Variables in your code that are not explicitly typed will be “Variant” by default. Use of Variant type variables causes a lot of computational overhead and can make your code significantly slower, so we will always include the “Option Explicit” statement at the top of our code modules in ME209, and we’ll use the variant type only when the program logic design requires that a variable take on different types.
Constants
Declaring a variable as “Constant” will protect it from being changed anywhere in your code. Example:
Const pi As Double = 3.14159 or Const pi = 3.14159
Just use the keyword "Const" instead of "Dim", and assign the constant value. If a subsequent statement tries to change the value, you will get a runtime error. In the example statement on the right, VBA uses the type of the constant value to assign the appropriate type to the variable pi (Double in this case).
Arithmetic Operations
Basic arithmetic operations are addition (+), subtraction (-), multiplication (*), division (/), and exponentiation (^). For example,
Addition: c = a + b
Subtraction: c = a – b
Multiplication: c = a * b
Division: c = a / b
Exponentiation: c = a ^ b
Notes: • One variable only is allowed on the left of the equals sign. For
example, "a + b = c" is not permitted.
• The statement "c = c + a" adds "a" to the previously calculated "c" then replaces the old value of "c" with the result. Note the comparison with algebra, where we could simplify the statement by subtracting "c" from each side of the equation and get "0 = a", which makes no sense in the programming context.
Arithmetic Operator Hierarchy
VBA generally executes operations from left to right, but also follows some rules that dictate hierarchy (precedence) levels of the various operators.
Parentheses always take precedence and are used to group quantities.
Exponentiation is performed before negation (as in "y = -x ^ 2").
Negation is performed before multiplication and division.
Multiplication and division are performed before addition and subtraction.
Examples (see Chapra Chapter 9):
x = 5 + 7 * 2 Is x going to be 19 or 24?
z = -2 ^ 2 Is z going to be -4 or 4?
d = 10 / 5 * 7 Is d going to be 0.28571 or 14?
When in doubt, use parentheses...
d = (10 / 5) * 7 But don’t get carried away. Use your knowledge of
hierarchy to write compact, but legible code.
Note Excel idiosyncrasy: In worksheet formulas, negation has precedence over exponentiation.
Increasing hierarchy
Walking through a more complex example:
a = 6: b = 3: c = 5: d = 2: e = 4 'Assigning values to variables
a,b,c,d r = a * (-b + (c ^ d - e * d * b)) 'Inner paranthetical expression is
processed first r = a * (-b + (c ^ d - e * d * b)) 'Exponentiation has precedence
r = a * (-b + (25 – e * d * b)) 'Multiplication has precedence, and is
evaluated left to right r = a * (-b + (25 – 8 * b))
r = a * (-b + (25 – 24)) 'Lastly, subtraction operation is performed
r = a * (-b + 1) 'Outer parenthetical expression is evaluated next
r = a * (-b + 1) 'Negation has precedence
r = a * (-3 + 1) 'Lastly, addition operation is performed
r = a * -2 'Final operation performed
r = -12 'Result assigned to variable r
In-Class Exercise:
Write the VBA statements that implement these mathematical expressions (Chapra, Problem 9.2). Use the VBA built-in function SQR() where needed to get the square root of a quantity.
__MACOSX/._ME209-1ClassNotesWeek3.pdf
ME209-1ClassNotesWeek4.pdf
Programming for Mechanical Engineers (ME209)
Class Notes - Week 4
Programming for Mechanical Engineers (ME209)
Course Part 3 (continued)
VBA Basic Syntax, Arithmetic & String Operators, Built-In Functions, Sub
Procedures
Numeric Data Type Recasting
When assigning numeric values to variables using the assignment operator ("="), VBA will convert the value to the type of the variable, if possible.
Floating-Point to Integer Recasting
Dim myInteger% 'Creates an Integer
myInteger = 3.14159
What happens? Since myInteger cannot hold the fractional part of the number, VBA rounds the number to 3, and then assigns that result to variable myInteger.
myInteger = 7.5
What happens? If the most significant discarded digit ("5" in this case) is greater than or equal to 5, VBA rounds up to the next larger integer. Thus 8 is assigned to variable myInteger.
Integer to Floating-Point Recasting
Similarly, integer values will be converted to floating-point when assigned to floating point type variables (i.e., Single or Double).
Text to Numeric Recasting
If a text string can be converted to a number, VBA will do that too. For example:
Dim myNumber# 'Creates Double type
myNumber = "3.14159"
What happens? VBA interprets the string as a number, and assigns 3.14159 to variable myNumber.
myNumber = "7.23E+3"
What happens? VBA understands scientific notation and assigns 7,230. to variable myNumber.
myNumber = "ME209"
What happens? A runtime error occurs, because VBA cannot convert that string to a number.
myNumber = "3.14159 is pi to 5 decimal places"
What happens? A runtime error occurs, because VBA cannot convert the entire string to a number.
String Concatenation Operator
Strings and String variables can be concatenated (connected together) using the "&" operator. For example:
Dim FirstName As String, LastName As String
Dim MI As String, Salutation As String
Salutation = "Dear Mr. or Ms. " & FirstName & " " & MI & ". "
Salutation = Salutation & LastName & ","
The resulting string contained in the variable Salutation would look like this:
Dear Mr. or Ms. Grade A. Student,
Statement Continuation Character
VBA statement lines can be as long as you like, but for readability you may want to continue a statement onto multiple lines using the continuation character ("_"). For example:
Dim Height As Double, Radius As Double, Volume As Double
Dim Result As String
Result = "Tank model " & Radius & "-" & Height & _
"will hold" & Volume & "cubic meters"
The resulting string contained in the variable Result would look like this:
Tank model 4-7 will hold 351.86 cubic meters
Multiple Statements on One Line
Statements can be combined into one line using the ":" character. For example:
a = 37.1: b = 12.5: z = 1
Use of the ":" character to combine statements should be limited to simple assignment statements like the one above.
Comment Lines
Adding comment lines in your code will help you and others understand what your code is doing. Start a comment anywhere by using an apostrophe. For example:
a = 37.1: b = 12.5: z = 1 'Initialize the local variables
It is a good practice to have more comment lines than you have executable statements!
VBA Built-In Functions Below are some functions that are found in VBA.
Purpose Function Syntax Example
Absolute value of x Abs(x) Abs(-11) = |-11| = 11
Arctangent of x Atn(x) Atn(1.5574) = 1
Cosine of x Cos(x) Cos(1) = 0.5403
Exponential of x (base e) Exp(x) Exp(3) = e3 = 20.086
Natural log of x Log(x) Log(20.086) = 3
Random number between 0 and 1 Rnd() Rnd() = 0.301948
Round x to n decimal places Round(x,n) Round(3.141, 2) = 3.14
Sine of x Sin(x) Sin(1) = 0.8415
Square root of x Sqr(x) Sqr(2) = 1.414
Tangent of x Tan(x) Tan(1) = 1.5574
Convert string s to Double type Val(s) Val("31.23") = 31.23
Notes: 1) Excel and VBA use radians for all angles. 2) Excel function for Square root is "Sqrt()", but the same function in VBA is "Sqr()". 3) Note the "Val()" function. We’ll discuss its special use in a moment.
Using the Val() Function
The Val() function is used here to protect against invalid user input. For example, if the user enters something like "a=12" in the InputBox, we would get a runtime error when we try to assign that string to the Double typed variable a.
The Val() function tries to convert the user's entry to a numeric type by scanning the user's entry one character at a time, skipping blanks. If it encounters a non- numeric character, it quits and returns what it has up to that point.
For example, if the user enters "12", this will return the number 12. However, if the user enters something like "a = 12", Val() will give up when it sees the "a", since that can't be part of any number, returning 0.
What if the user enters "12 meters"? This would work because Val() will give up when it sees the "m", and will return 12.
Other than Arabic numerals, which are always part of valid numbers, Val() will also accept a decimal point ("."), sign ("+" or "-"), and letter designating engineering notation ("E" or "D"), as long as they are placed where they may logically be part of a numeric quantity.
Use InputBox() without the Val() function when you want your user to enter a text string, such as their name.
Accessing Excel Worksheet Functions in VBA
In general, VBA’s built-in function library is limited in comparison to Excel worksheet functions. We’ve already noticed that VBA does not have a function like Pi(), but its also lacking functions for arccosine, and arcsine.
Fortunately, VBA gives us a way to access the worksheet functions from the VBA code. For instance, using the object-oriented structure of Excel:
pi = WorksheetFunction.Pi()
A = WorksheetFunction.Acos(b / c)
B = WorksheetFunction.Asin(a / c)
A very useful worksheet function calculates the arctangent of an angle anywhere in the cartesian plane (0 <= angle <= 2*pi):
A = WorksheetFunction.Atan2(a, b)
This functions avoids the runtime error you would get with Atan() if b were 0.
The syntax above is how you could use any worksheet function in your VBA code, however the above worksheet functions for Pi, Acos, Asin, and Atan2 are the only ones that we will use in ME209.
f = m N
m = tan q
Class Exercise
The friction force that keeps a block from sliding down a ramp is determined by the weight of the block, mg, the coefficient of friction between the sliding surfaces, m, and the angle of the ramp, q.
You conducted an experiment to determine the coefficient of friction by lifting the ramp until the block began to slide. As an assistant raised the ramp, you recorded the angle, 𝜃.
Class Exercise
You conducted 3 trials, with the following results recorded to a spreadsheet:
theta (degrees) Coefficient of Friction, mu
18.0
17.5
19.9
Average mu:
Exercise: Write a user-defined function to calculate and return the coefficient of friction, with an input argument of theta. Use your function to complete the above table of experiment results. Use a worksheet function to calculate the average mu.
f = m N
m = tan q
Function vs. Sub Procedure
A Function procedure gets its input as arguments, and normally returns a single result to the location where it was used in a worksheet formula or VBA expression.
A Sub procedure can get its inputs as arguments, and can also read inputs from an external source. It can return results as arguments, and can also write outputs to an external source. The external sources can be the InputBox/MsgBox functions, worksheet cells, or files. In ME209 the external source will usually be a user interface worksheet.
Here is the general syntax to create a Sub procedure:
Sub ProcedureName(arg1, arg2, arg3, ...)
<VBA statements>
End Sub
Notice that the syntax is the similar to a Function procedure.
Functions can be used in worksheet formulas, but Subs cannot be.
A Main Sub must be run manually, and it cannot accept arguments:
Sub MainSubName()
<VBA statements>
End Sub
A Main Sub can call other Subs and Functions, and can pass arguments to them. For example:
Sub MainSubName()
Dim radius#, height#, volume#
radius = 3: height = 5
Call CalculateVolume(radius, height, volume)
MsgBox "The tank volume is " & volume & " m^3"
End Sub
Sub CalculateVolume(r, h, v)
v = 4 * Atn(1) * r ^ 2 * h
End Sub
A main sub procedure can be run from the VBE by using the F5 key (or "Run -> Run Sub" menu), or can be run from a user interface worksheet by assigning it to a "Run Button". To create a Run Button:
1. On the worksheet, select the "Developer" ribbon. In the Controls group, use "Insert". Under "Form Controls", select the button control (first one in the top row).
2. The input cursor will change to a cross-hairs. Select where you want to place the button, and while holding the left mouse button, drag the button to the desired size. The Assign Macro window will open.
3. In the Assign Macro window, select the VBA main sub that should be run by the button and press "OK". If you haven't created it yet, just accept the default. You can assign it later.
You can easily edit the button properties (size, position, title, assigned macro, etc.) at any time by right-clicking on it.
Give your button a meaningful title.
InputBox Function
The InputBox() function can be used to prompt the user to enter one value at a time. The user input is returned as a character string, which can be converted safely to a numeric type using the Val() function.
For example, suppose we want the user to input the values for program Double type variables a, b, and c. We could use the InputBox() and Val() functions this way:
When the program runs, the user will be prompted like this.
Message Box Function
The MsgBox (message box) function can be used to write program results or other messages back to the user. For example, we can echo back the user inputs, to verify that the right inputs were provided.
The MsgBox Syntax is: MsgBox string (no parentheses required)
So for example, we can use it like this:
Want each variable on a separate line? Use the "Chr()" function or "vbCr" built-in constant to insert carriage returns where you want them:
or
Example
Let’s create a Sub procedure to calculate the roots of the quadratic equation. In general, the form of a quadratic equation is ax2 + bx + c = 0, and the roots (values of x which solve the equation) are given by:
Use the InputBox function to get the user’s input for the coefficients a, b, and c, and use the MsgBox function to display the results back to the user (formatted to use several lines).
Test your Sub procedure using the coefficients of the following quadratic equation:
You should get something like this:
When floating-point variables contain whole integer values, VBA displays only the integer part in the MsgBox.
What happens when the coefficients are a = 0.34, b = 2, and c = -3?
When floating-point variables contain fractional values, VBA displays the full precision, using a total of 16 places for the entire number.
How can we control the formatting to show the number of places we want? Use the VBA Format() function:
Format(x1, ".000")
Now the result will look something like this:
American Standard Code for Information Interchange (ASCII) Code Table
__MACOSX/._ME209-1ClassNotesWeek4.pdf
ME209-1ClassNotesWeek5.pdf
Programming for Mechanical Engineers (ME209)
Class Notes - Week 5
Programming for Mechanical Engineers (ME209)
Course Part 3 (continued)
VBA Basic Syntax, Arithmetic & String Operators, Built-In Functions, Sub
Procedures
Direct Access to Cell Values from VBA Procedures
Using a worksheet (or worksheets) as the I/O interface is strongly recommended. Often, your programs will be integrated with data contained in worksheets, so using VBA methods to access the values of worksheet cells will be convenient AND facilitates documentation of inputs and outputs. We have two methods for referencing a worksheet cell:
• Range() Method
For example: Range("A1")
• Cells() Method
For example: Cells(iRow, iCol)
Also, the Cells() method lets us specify the column letter like this: Cells(iRow, "A")
The Cells() method can only reference one cell at a time, however the Range() method can reference ranges of cells, as in these examples:
Range("B2:E12")
or
Range("B2","E12")
The first example uses one argument in the form of a range reference.
The second uses two arguments, one referencing the upper-left corner of the range and the other referencing the lower-right corner.
If the range has been named, as for example "myRange", you can refer to it this way: Range("myRange")
Note the range name must be in double-quotes.
If you have multiple worksheets, the range "B2:E12" exists on each sheet. Which range did we just select? Answer: The one on whichever worksheet was active (or selected) at the time.
You can specify a range on any worksheet (whether selected or not) using this syntax:
Worksheets("Sheet2").Range("B2:E12")
Where "Sheet2" is the tab name of the worksheet.
This is another example of the object-oriented nature of Excel and VBA. A worksheet is an Excel object, and its member objects can be referenced by using the "." object separator.
If you have to write a lot of range or cell references, you might want to make the sheet active using one of these statements, which are equivalent:
Worksheets("Sheet2").Activate
or
Worksheets("Sheet2").Select
Then, whatever Range or Cells references that follow will reference cells on Sheet2, until another sheet is activated or selected.
Alternatively, you can use a "With-End With" block:
With Worksheets("Sheet2")
.Range("B2", "E12").Select
.Cells(2, "B") = "This is the selected range"
End With
This example does not make the sheet active. The user may be looking at Sheet1 while you are working with cells on Sheet2.
Reading a worksheet cell value, and assigning it to a variable:
Dim a as Double
Worksheets("Sheet1").Activate
a = Cells(7, "D")
This reads whatever value is in cell D7 on Sheet1 and assigns that value to variable "a". If D7 is blank, VBA will read zero.
If the sheet you want is already active or selected, you don’t need to activate or select it again.
Writing the value of a variable to a worksheet cell:
Cells(8, "D") = a
This writes the value of variable "a" to worksheet cell D8.
It's as easy as that!
Example: Summing the numbers in a worksheet column
Next, we will write our own sub procedure to compute the sum of the values in column A, and name it "mySum". We will use this sub procedure to demonstrate using the "Selection" object and the "Selection.Offset()" method.
Create the worksheet shown on the right, using the built-in function Sum() to calculate the sum of the scores in column A. The value returned by Sum() will be displayed in cell B2: 247.516
In a code module, start creating the following Sub procedure to calculate the same result:
Sub mySum()
'Declare local variable
Dim sums As Double
'Sum the values in column A
Cells(2,1).Select
sums = Selection
"Selection" is a special Excel object that always refers to whatever cell or range is currently selected. The last statement above reads the value in the selected cell (17.938) and assigns that value to variable "sums".
This selects cell A2, as shown above.
Now add the following lines to the procedure:
Cells(3,1).Select
sums = sums + Selection
Cells(4,1).Select
sums = sums + Selection
Cells(5,1).Select
sums = sums + Selection
Cells(6,1).Select
sums = sums + Selection
After these statements are executed, the variable "sums" should contain the sum of the values in column A: 247.516.
To prove that, add the following statements to complete the Sub procedure:
'Display the result
MsgBox sums
End Sub
And then run your sub by pressing the "F5" key.
These statements repeatedly change the selection to the next cell down, and then add that value to variable "sums".
Cells(2, 1).Select
sums = Selection 'No change up to this statement
sums = sums + Selection.Offset(1,0) 'Adds 35.282
sums = sums + Selection.Offset(2,0) 'Adds 76.81
sums = sums + Selection.Offset(3,0) 'Adds 63.439
sums = sums + Selection.Offset(4,0) 'Adds 54.047
Note: The selection (cell A2) is never changed. Values are read from cells 1 row down, 2 rows down, etc., in the same column (0 columns to the right).
Using the "Offset" Method
The Offset(iRow, iCol) method can be used to refer to a cell relative to a specified range object. Thus we can refer to the values in cells A3, A4, A5, and A6 without having to select them as in this example:
What happened to the worksheet formula that was in cell B2? You over-wrote it with the number 247.516, because cell B2 is 0 rows down and 1 column to the right of the current Selection (A2).
Note that Ctrl+Z won't bring it back, because you cannot "undo" changes made by your VBA procedures.
What happens if you try to write the total of the scores with this statement?
Selection.Offset(0,-1) = sums
You get an error, because it refers to a column that doesn't exist.
Now that your procedure has calculated the sum of the numbers, replace the MsgBox statement with this one:
Selection.Offset(0,1) = sums
Ways to Reference a Worksheet There are three ways to reference a worksheet in VBA. We used the first one already.
Note: If you change the tab name, sheet location, or module name, Excel won’t update your code; you’ll have to edit/replace every instance of it. I find using code name is most convenient, because you can change it and edit your code in the same window.
Example: Sub Procedure Cell I/O and a Sub called by another Sub (Chapra Problem 5.3)
Create a worksheet input/output area as shown on the right, and enter the values shown for a and b.
We will write a Sub procedure to read in the values of a and b from the "Before:" (input) area, swap them, and write the swapped values out to the "After:" (output) area.
In a code module, create the following Sub procedure:
Now we need to write Sub Switch. Let's put it in the same code module, below this (but we could put it in any code module in our workbook).
Now run the main sub "Problem5p3".
Note that the resulting values of x and y are passed back to the main sub as the new values of a and b, which are then written out to the worksheet output area.
Arguments: Passing by "Value" vs. Passing by "Reference"
In the previous example, notice that after SwitchDouble() changed the values of x and y, the corresponding values of variables a and b also changed.
This is because VBA passed a reference to the memory addresses of a and b when it called SwitchDouble(). This is called "passing by reference", and is the normal way that variables are passed as illustrated below.
Memory
a b
Arguments: Passing by "Value" vs. Passing by "Reference"
You can tell VBA to pass copies to the called procedure, which is called "passing by value". A method for this is shown below.
Temporary Memory
copy of a
copy of b
Memory
a b
Method 1: In the called procedure, use the "ByVal" keyword.
Arguments: Passing by "Value" vs. Passing by "Reference"
Another method for ensuring a copy of the argument values are passed is illustrated below.
Temporary Memory
copy of a
copy of b
Memory
a b
Method 2: In the calling procedure, put () around the variable names.
Please note:
In ME209, we will use InputBox() and MsgBox() functions sparingly, and never in Functions.
Next, we will learn better ways to get user input and communicate results to a user via the user interface worksheet (UIW).
Henceforth...
If an assignment doesn't ask for an InputBox() or MsgBox() to be used, don't use them!
Use the MsgBox() in a Sub procedure only to alert the user of an unusual error or condition requiring immediate attention.
Programming for Mechanical Engineers (ME209)
Course Part 4
Flowcharting
Flow Control Structures
User-Friendly Programming
Debugging
What is a program? It is a set of instructions issued to a computer to perform very specific tasks in a particular sequence of operations. We generally group the instructions to perform a specific task into a procedure, so that a program consists of a group of procedures working together. Each instruction in a procedure is called a line of code or statement. Take note of the three key words in the above definition:
Statements are written in a language that the computer can understand, or what is called the programming language syntax.
Sequence of Operations is the logic flow, or algorithm. It is the link between the user’s inputs and the program outputs.
Words of advice: “Organization is the key to success.”
What is the first step you take in writing an English paper? You PLAN by outlining what you intend to write. You define the objective, then define the steps to get there.
The basic parts of a computer program are the inputs, the logic, and the outputs.
Inputs: Information used by a program to perform its purpose. Input can be read from a worksheet or external file, or entered by a user using a keyboard or mouse.
Logic: How the computer is going to calculate, analyze, and/or process the inputs to produce the results.
Outputs: Program results, written to worksheet cells or external files, or displayed in pop-up windows.
Simple enough!
Identify the data required inputs, understand how to process them logically, and report the results.
Flowchart
Every complex task can be broken down into a series of smaller, simpler tasks that eventually leads to specific steps that are taken in a logical sequence to achieve the goal.
A flowchart is a graphical depiction of that logical sequence of operations, independent of the programming language; it is meant to highlight the logic not the syntax. If you spend time developing a detailed flowchart, writing the program becomes an easier task.
Here is a basic flowchart:
The block symbols are connected by arrows to show the direction of processing flow. The logic block needs to be expanded into more detail for complex programs having many branches of operations.
INPUT OUTPUTLOGICSTART END
Decision Block
These are the conventional block symbols that are used in creating flowcharts:
?
Loop
1 2
Yes/True
No/False
INPUT
LOGIC
START Start/Stop
Terminal Block
Input/Output Block
Process Block
Repeated Process Block
Exit Repeat
Return
Flow Junction
Cross Over
Page ConnectorsContinued
on Page 2
Continued from Page 1
Flowchart Rules
• Always show flow direction arrow on connecting lines.
• No parallel processing. Statements are executed on one path only.
• Process blocks have ONE input and ONE output. Use a Loop block to show a repeated process.
• Decision blocks have ONE input and TWO possible outputs, only one of which is taken each time.
• A Flow Junction (a.k.a. Confluence) has TWO or MORE inputs and ONE output.
Example (Chapra Chapter 11, Section 3)
Task: Calculate the square root of any real number, "a", input by the user. Write out the result, "𝑅𝑒" (for a real result) or "𝑖 ∗ 𝐼𝑚" (for an imaginary number result).
Input a
𝑅𝑒 = 𝑎 1 2
Start
a < 0? 𝐼𝑚 = 𝑖 ∗ 𝑎 1 2
Output "Answer is 𝑅𝑒
(real)"
Output "Answer is 𝑖 ∗ 𝐼𝑚
(imaginary)”
End
YesNo
Note: Many programming languages support imaginary number math, but Excel VBA does not.
VBA Control Structures
Control structures are used to implement branching of program processing flow, in order to implement a flowchart logic design.
"GoTo" unconditional control structure
If we want processing to jump to another line (either forward or backward), we can use the GoTo statement:
GoTo <line label>
The above statement will cause processing to proceed at the line beginning with the specified label. The line label can be any alphanumeric value, and can begin with a numeral (numbers are commonly used). For example:
GoTo 100
There must be a line beginning with the label "100", as in:
100: (the ":" character is required where the label is applied)
Example:
We bypassed this line!
Example:
This makes more sense, but we've created an "infinite loop".
Conditional Control Structures
Conditional control structures use logical expressions involving relational and/or logical operators. Logical expressions result in either True or False.
Relational Operator Group Example
= Equal to x = y
<> Not equal to x <> y
> Greater than x > y
>= Greater than or equal to x >= y
< Less than x < y
<= Less than or equal x <= y
The relational operator group has precedence below the arithmetic operator group.
Relational operators are all at the same level of precedence (will be evaluated left-to-right).
Logical Operator Group Example
Not Not [exp1] Not x = y
And [exp1] And [exp2] x = 0 And y > 1
Or [exp1] Or [exp2] x = 0 Or y > 1
Using an equal sign ("=") as a relational operator is different from using it as an assignment operator. For instance, "x = 0" in the example above means to compare the value of x to 0. The result of this comparison is either True (if x is equal to 0) or False (if x is NOT equal to 0).
Logical Constants
In VBA we have the following logical constants: True False
Example: x = 3: y=1 x <> y = False
What is the result of the above logical expression?
The logical operator group has the lowest precedence, and are evaluated in this order.
__MACOSX/._ME209-1ClassNotesWeek5.pdf
ME209-1ClassNotesWeek6.pdf
Programming for Mechanical Engineers (ME209)
Class Notes Week 6
Programming for Mechanical Engineers (ME209)
Course Part 4 (continued)
Flowcharting
Flow Control Structures
User-Friendly Programming
Debugging
Example: The following code uses unconditional and conditional control structures to compute the factorial of a positive number.
Spaghetti code!
Spaghetti Code
Although easy to implement, beware of too may GoTo’s, which
can complicate your program very quickly and turn it into infamous "spaghetti code", which means that its logic that is hard to follow.
The If-EndIf Control Structure
We saw how the If-Then statement can conditionally execute a single statement, and used that in the previous example, as in:
If x > 0 Then GoTo 20
The If-EndIf control structure can be used to conditionally execute multiple statements. The syntax is:
If <logical condition> Then
<statements>
End If
Example: This version uses the If-EndIf control structure, requiring fewer GoTo's:
The logic is much easier to follow in this version!
Note: Never use GoTo to jump into an If-EndIf block.
__MACOSX/._ME209-1ClassNotesWeek6.pdf
ME209-1ClassNotesWeek7.pdf
Programming for Mechanical Engineers (ME209)
Class Notes - Week 7
Programming for Mechanical Engineers (ME209)
Course Part 4 (continued)
Flowcharting
Flow Control Structures
User-Friendly Programming
Debugging
If-EndIf Control Structure - Optional Blocks
Here is the full syntax of the If-EndIf control structure:
If [logical exp1] Then
[statements if exp1=True] ElseIf [logical exp2] Then
[statements if exp2=True] ElseIf [logical exp3] Then
[statements if exp3=True] .
.
.
ElseIf [logical expn] Then
[statements if expn=True] Else
[statements if all expi=False] End If
Rules: 1. The ElseIf and Else blocks are
optional. 2. One Else block is allowed, and it
must be last. 3. You can have as many ElseIf
blocks as you need. 4. The first True logical expression
determines which block of statements is executed (if any), then the remainder of the blocks are skipped.
5. If none of the logical conditions is True, then the statements in the Else block (if there is one) are executed.
Class Exercise
Re-write the factorial sub procedure so that it uses ONE If-EndIf block. Use an ElseIf block to handle the case when the user inputs a negative number. Name it Factorial3().
Class Exercise Solution
Control Structure Nesting
The design of many programs requires nesting of conditional control structures.
Consider the logic you use as you approach an intersection with your car. As a good driver, you repeatedly glance at the signal light, and decide how to proceed. Here is the flowchart for that logic:
Get light color
START
EXIT Stop Car
In inter-
section ?
Is light
green ?
Is light
yellow ?
Time for safe
stop ?
No No
No
NoYes
Yes
Yes
Yes
If-EndIf Nesting Example
Here is a logic outline for this in VBA, using nested If-EndIf blocks:
Sub SafeDriver()
10: <proceed with driving> If Not <in intersection> Then
<get signal color> If <color is green> Then
GoTo 10
ElseIf <color is yellow> Then
<determine time to stop> If Not <time to stop> Then
GoTo 10
End If
End If
<stop the car> End If
End Sub Three nested If-EndIf blocks
Class Exercise
Recall our quadratic equation example, which could solve for real roots of 𝑎𝑥2 + 𝑏𝑥 + 𝑐 = 0 (for certain values of a, b, and c).
Modify your procedure to enable it to solve for the roots, given any combination of a, b, and c. In general, the roots of the quadratic equation are given by:
𝑥𝑟1,𝑟2 = −𝑏 ± 𝑏2 − 4𝑎𝑐
2𝑎
For this in-class exercise, use the InputBox function to get inputs for a, b, and c, and use the MsgBox function to display the results.
For complex roots, you will write the roots as:
𝑥𝑟 + 𝑖 ∗ 𝑥𝑐 and 𝑥𝑟 − 𝑖 ∗ 𝑥𝑐 (string operations required)
Class Exercise Follow-On
What happens when 𝑎 = 0? The general equation for the roots will not work. The quadratic equation reduces to 𝑏𝑥 + 𝑐 = 0, which has only one root given by:
𝑥𝑟 = −𝑐/𝑏
Furthermore, if the user enters 𝑎 = 𝑏 = 0, the above equation will not work either. In that case, the quadratic equation reduces to:
𝑐 = 0
Which implies that 𝑐 must be equal to zero also, and there are no roots. In other words, if the user enters 𝑎 = 𝑏 = 0, the input is invalid.
We should design our procedure to test for and properly handle these possible conditions.
Writing User-Friendly Programs
As we become proficient programmers, we should learn to write code that is "user friendly" and robust.
Our User Interface Worksheet should ...
…include a brief description of what the program does.
…have user instructions that describe how to use the code, inputs that are required, and valid input values or ranges.
Our program should ...
…get user input by reading from the User Interface Worksheet.
…display results by writing to the User Interface Worksheet.
…be tested for all conditions that may be occur, as determined by all potential user inputs.
…are free of run-time errors, by anticipating conditions that may cause them and writing code that gracefully handles them.
It's worth learning to use the equation editor in the Office applications.
Example User Interface Worksheet
Class Exercise
Modify your quadratic equation solver to make it user-friendly and robust, including a User Interface Worksheet for input/output. Create labeled and named input/output cells for 𝑎, 𝑏, and 𝑐, and use those range names for reading/writing by your Sub procedure.
Your results will display a message indicating the solution type (real roots, complex roots, double root, or NO roots), and will write the roots to the output area of your User Interface Worksheet.
Class Exercise Solution
Debugging
Types of Program Errors
There are four levels of errors in VBA:
1. Syntax errors 2. Compiler errors 3. Runtime errors 4. Logic errors
Syntax Errors
These errors are caused by not following the language "rules", such as:
= a + b 'assigned variable missing
a = * 2 'operand missing
Fortunately, the VBE is very helpful in pointing these out as you write your code.
Errors must be corrected in this order.
Compiler Errors
The compile step is when the code is made ready to run. Errors found in this stage are caused by such things referencing variables or procedures that do not exist, or not having required statements like "End If" closing an "If-Then" flow control block.
Examples:
Option Explicit
Function MyCode(a, b)
c = a ^ b
MyCode = c - 2
End Function
or
Function MyCode2(a, b)
MyCode = a * factoral(b)
End Function
VBA will catch the failure to declare variable "c".
VBA will catch that function "factoral" (mis-spelling of "factorial") has not been defined anywhere.
Runtime Errors
Once the code begins to run, these types of errors may be encountered. Runtime errors are caused by such things as an assignment of the wrong data type to a variable, array index going our of bounds, or an overflow error (e.g., division by zero).
Sub MyCode3()
Dim i%, str$
i = str
End Function
or
Sub MyCode4()
Dim ratio#, num#, denom#
num = 17.2
denom = 0
ratio = num / denom
End Function
Assigning a string data type to an integer data type causes a "type mismatch error".
Division by zero causes an "overflow error".
Logic Errors
Logic errors produce incorrect results. Finding and resolving these is the hardest:
• Use debugging methods.
• Verify steps in the program.
• Use hand calculations to check intermediate results.
• Double-check the equations and logic structures.
Running and Debugging Procedures
You can run a Sub procedure from the VBE by pressing the run button on the standard tool bar, or press F5.
We may want to execute just one statement at time. We do that by repeatedly pressing F8. With statement highlighted in yellow will be executed next. We can check variable values anywhere in the code by hovering over them with our mouse.
Using Breakpoints
In a large code, stepping through it might be very inconvenient. We can set a "breakpoint", by clicking in the left margin. When the execution reaches a breakpoint, it stops and we can then use F8 to step from that point, or F5 to continue running.
Breakpoints can be "toggled" on and off by clicking in the left margin. Note that function procedures cannot be run using F8. To access the debugging tools, set a breakpoint in the function.
Using Add Watch
The "Watches" window can be used to displayed variable values in real time.
You can add to the Watch List using "Debug -> Add Watch...".
"Quick Watch" will let you add many variables to the Watches window at one time.
Or, right-click on the variable you want to add, and select "Add Watch...".
The "Add Watch" window let's you confirm or modify the type of watch being added.
The Add Watch window lets you specify the watched variable, or an expression involving one or more variables.
If the expression is a logical condition, the "Watch Type" can be set to halt when the condition is True.
If the expression is a logical condition, the "Watch Type" can be set to halt when the condition is True.
__MACOSX/._ME209-1ClassNotesWeek7.pdf
ME209-1ClassNotesWeek8.pdf
Programming for Mechanical Engineers (ME209)
Class Notes - Week 8
Programming for Mechanical Engineers (ME209)
Course Part 5
Loop Control Structures
Loop Control Structures
A loop is a block of code that is repeated until a proscribed condition is met. We will use three kinds of loops in ME209:
• For-Next loop • Do-Loop • Do-While-Loop
For-Next Loop
For <loop variable> = <begin> To <end> Step <increment> <block of statements>
Next
Here's how it works:
1. The "loop variable" is initialized to the "begin" value.
2. If the "loop variable" is outside the range "begin" to "end", the loop ends and processing continues with whatever statement follows "Next".
3. Otherwise, the "block of statements" are executed.
4. Then "loop variable" is changed by "increment" (which can be positive or negative), and the cycle repeats with step 2 above.
If "Step <increment>" is left off, the default step is 1.
Examples:
For i = 1 To 5 Step 1
MsgBox "i = " & i
Next
MsgBox "Now i = " & i
If the initial value is already outside the loop range, the loop never executes. For example:
For i = 6 To 5 Step 1
MsgBox "i = " & i
Next
MsgBox "Now i = " & i
But if the increment is negative, this loop will execute:
For i = 6 To 5 Step -1
MsgBox "i = " & i
Next
MsgBox "Now i = " & i
In VBA, the loop variable is always outside the loop range after the loop is finished.
There's no way to get from 6 to 5 in a step of +1, so the loop is skipped.
Example: Sum all numbers from 1 to 100
Note that we added the loop variable onto the Next statement. This is optional, and is good practice. That will help you keep track of your For-Next blocks of code, particularly when we start "nesting" For-Next blocks.
What will be the value of loop variable i when the loop ends?
Recall our Factorial procedure exercise:
We used "GoTo" to loop through values of x.
Exercise
Modify your Factorial procedure to use a For-Next loop instead of the GoTo.
Does your solution look something like this?
One more GoTo bites the dust! One more to go.
Do-Loop
The Do-Loop is similar to the For-Next, but looping continues indefinitely until a conditional control statement tells VBA to exit the loop. In general, it can look something like this:
Do
<statements> If <logical expression> Then Exit Do
<statements> Loop
Or...
Do
<statements> If <logical expression> Then GoTo <label> <statements>
Loop
Caution: If your logic isn't correct, your program could run indefinitely, thus "hanging up" your Excel program. This can result in loss of work. Always save your work before testing a program with a "Do-Loop".
Example
Dim x!
x = 10.5
Do
If x < 0 Then Exit Do
x = x – 1
Loop
MsgBox "x = " & x
This procedure will exit the loop when x < 0, proceeding with the statement following Loop. If the condition is not included, the loop would repeat until x reached a value of approximately -1038, at which time there would be an overflow run-time error.
How many times will the If statement be executed?
Do-While Loop
A convenient variation of the Do-Loop is the Do-While. The condition for continuing the loop is specified in the Do statement:
Do While <logical condition> <statements>
Loop
Thus, the previous example above could be written this way:
Do While x >= 0
x = x – 1
Loop
Exercise
Modify your Factorial procedure to use a Do-Loop to eliminate the last GoTo statements (label 10 no longer needed).
Does your solution look something like this?
No more GoTo's (good riddance!)
Programming for Mechanical Engineers (ME209)
Course Part 6
Arrays
Note: Use the Part 6 Workbook posted to Moodle for the examples and exercises
Arrays
Arrays are the most common data structure in engineering. You have worked with arrays in mathematics:
In VBA, an array is a group of variables of the same type. You can reference one member of an array by specifying its index numbers (e.g., row and column numbers).
You declare an array using a Dim statement. Here's the syntax for declaring a one-dimensional array (or vector):
Dim array_name(start To end) as datatype
where "start" and "end" are the lower and upper bounds of the index number.
0. 0.25 38 210
A = 12.7 0.25 32.8 107. A one-dimensional array
B = 12.7 0.25 32.8 107. A two-dimensional array 18 43.25 32 0.
For example, declaring array A that can hold 4 Double type values:
Dim A(0 To 3) as Double
The lower bound specification can be eliminated. Thus, the following creates the same array:
Dim A(3) as Double
By default, VBA assumes the lower bound is "0". To minimize confusion, you may want to force VBA to assume that 1 is the lower bound by adding the following option statement in the top of your VBA modules (where we have Option Explicit):
Any integer values can be used for the bounds, as long as the lower bound is less than or equal to the upper bound. For example...
Dim A(-3 To 5) As Long
...defines a vector that can hold 9 Long integers.
You access a single element of the array by specifying its index number. For example (select worksheet "Vector"):
Exercise: Modify this example to use For-Next loops to read in and write out the values.
Declaring a two-dimensional array looks like this...
Dim B#(1 To 4, 1 To 3)
or, if "Option Base 1" is specified in the module:
Dim B#(4, 3) 'This array can hold 12 values
By convention, the first dimension of a multi-dimension array is the row number, and the second is the column number. Programmers are not required to adhere to that convention, however we will see that VBA uses it when copying data between arrays and worksheet ranges.
An array can have up to 60 dimensions, and you can define them to represent whatever you need.
Accessing an element of a multi-dimensional array is similar, but we have to specify all indices. For an n-dimensional array, n indices must be specified. For example (select worksheet "2D Array"):
Dynamic Sizing of Arrays
Often, we do not know ahead of time what the size of an array needs to be. VBA provides a way to declare variable-sized arrays, and a way to change their size "on the fly":
Option Base 1
Dim A() As Double 'Note absence of index bounds
Array "A" is now a "dynamic array". Before we use this array, we can tell VBA what its size is:
ReDim A(5) 'Now A can hold 5 Double values
We can resize the dynamic array as many times as needed:
ReDim A(2,10)
Now A has 2 rows, 10 columns of Doubles.
You cannot ReDim an array without first declaring it using Dim, and you cannot reference an element of it before ReDim'ing it.
Reading/Writing Data Arrays in a Single Statement
If we declare a variable as Variant, we can assign a range of worksheet cell values to it with one statement (select worksheet "Read_Write Entire Array"):
Here, VBA casts "a" as an array, dimensions it and gives it a data type to fit the data being read in, and then reads the data from the specified input range.
Here, VBA fills the specified output range with data from array "a" (this works for any 2D array; does not have to be type Variant).
Exercise: Find out what happens if "a" holds more or less values than the specified output range.
If you use this method of writing data out from arrays, be careful that you don't accidentally over-write cell data you don't want to loose! Ctrl-Z won't let you fix it.
Note that Excel made "a" two-dimensional as though we had used this statement:
Dim a(1,9) 'And VBA made the index base 1
Even though there is only one row, we must specify both the row and column index to access a single value in array "a". That's the way Excel maps worksheet ranges to VBA arrays.
Exercise - Weighted Score Calculation Using an Array
Write a procedure to calculate a student's weighted average score for three exams, using the data on the worksheet below. Use a two-dimensional integer array for the data. Write the resulting weighted score to cell E9 (select worksheet "Exam Scores"):
𝑾𝒆𝒊𝒈𝒉𝒕𝒆𝒅 𝑺𝒄𝒐𝒓𝒆 = σ𝒊=𝟏 𝒏 𝒘𝒊∗𝒔𝒊 σ𝒊=𝟏 𝒏 𝒘𝒊
Wherever you see a σ it means "For-Next loop"!
Here's how far we got in class. This reads the data into the array:
To complete this, use For-Next loops to calculate the terms needed to compute the weighted score, and then write the result to the worksheet output cell.
Recognize that we have multiple For-Next loops with the same Start/Stop values. When that happens, always try to combine them.
Now, modify your solution so that it uses only ONE For-Next loop.
__MACOSX/._ME209-1ClassNotesWeek8.pdf
ME209-1ClassNotesWeek9.pdf
Programming for Mechanical Engineers (ME209)
Class Notes - Week 9
Programming for Mechanical Engineers (ME209)
Course Part 6 (continued)
Arrays
Note: Use the Part 6 Workbook posted to Moodle for the examples and exercises
Example - Indefinite Data List
Let’s say you have columns of data on a worksheet, and want to process the data after reading the values into arrays.
Here's the structure of the data on the worksheet. The user has entered the data records in a table, and the number of students (number of data records).
We will create a Sub procedure to read the student numbers and grades into two one- dimensional integer type arrays, and we want our Sub to work for any size class specified by the user (select worksheet "Indefinite Data List")
Does your solution look like this?
Automatically Getting Array Size
We needed to know how many grades are on the list, so we required our user to enter the number of grades, but we want to design your codes to be more "user friendly".
Let's examine ways that we could determine the length of the list automatically:
• Using "Ctrl-Down" method.
• Using "Shift-Ctrl-Down" method.
• Using a Do-While loop.
Select worksheet "Class Statistics".
Array Sizing Using "Ctrl-Down"
We know the list of grades starts in Cell B4. In this method, we will mimic the keystroke that selects the last item in the grade column, and then use the "Row" number of the selected cell to compute the number of grade items.
How did we learn this syntax? We recorded a "macro" (Developer ribbon function) while doing it manually, which gave us the code to mimic the action in VBA. VBA puts the code into a new code module, which we incorporated into our procedure.
Array Sizing Using "Shift-Ctrl-Down"
This is like the previous example, but selects the entire column of data, and uses the "Count" property of the current selection to compute the number of grade items.
You can record macros as a way of learning the VBA code to mimic anything you can do manually to worksheet objects, but I don't recommend these methods for counting the items in a range. The syntax is too hard to remember!
See the next method, which used a Do-While loop.
Array Sizing Using a Do-While Loop
Here we will use a Do While loop to examine the contents of each cell until we find one that is empty:
Now the loop counter "n" is equal to number of grade items.
Class Exercise
Select worksheet "Class Statistics".
Write a Sub procedure that reads the data and writes the statistics of the class grades in the labeled cells on the right.
Use one Do-While-Loop to determine the number of grades.
Use one For-Next loop to read the scores into a dynamic array, calculate the sum of the scores, and determine the high and low scores.
Write the resulting statistics into the output area in column F.
Add a run button.
Class Exercise
Does your solution look like this?
Does your solution look like this?
More about Multidimensional Arrays
Higher order arrays are used frequently in engineering. Consider the temperature distribution in a plate, which is defined by 5 nodes in the x-direction and 5 nodes in y-direction, as represented in the figure below.
We could store this data in a 2D array (assuming Option Base 1):
Dim temp(5, 5) As Double
What if we wanted to store the temperature distribution in a 3D cube, using this grid spacing?
The array would be declared as:
Dim temp(5, 5, 5) As Double
x
y
Excel Array Functions
We have passed single values as function arguments and function results. Excel VBA provides ways to pass two-dimensional arrays to functions and return results (in some cases arrays of results) to worksheet ranges. These are called "Array Functions", and Excel has a number of them built-in.
For example, let’s use Excel's MMULT() function to multiply arrays (select worksheet "MMULT"):
Entering function MMULT() in Cell G3 as shown yields the result of 110 (dot product of Matrix A Row 1 and Vector B).
Note that MMULT() accepts Range inputs to specify matrices to be multiplied. We got the first element of result vector C, but how do we tell Excel that we want to complete the vector C, following the rules of matrix multiplication?
We do that by selecting the entire output range (G3:G5), and entering the function formula with the "magic" key combination "Ctrl-Shift+Enter".
Excel puts the function in each of the selected cells, and yields the correct results, like this:
Notice the curly brackets around the formulas. That tells you that you have successfully entered an "Array Function".
Note that Excel associates the three cells in column C as an array output range, and won't let you change any of those cells individually. You have to select the entire range to make any changes.
Let's learn more about how Excel and VBA pass matrices.
Passing a Worksheet Array to a User Defined Function (select worksheet "Determinant2x2"):
Create a Function named "Determinant2x2()" that accepts a 2x2 Range as an argument, and returns the determinant of that array.
For a 2x2 matrix:
The determinant is given by:
Here's how the range will be passed to our function:
The function look like this:
The input argument is an Excel Range object, and we can use the "." object seperator to check its parts, such as the Rows and Columns, and their Count methods.
Notice that we can refer to the rows and columns of the input range like it's an array (Base 1).
Reading or Writing a Data Array in a Single Statement
If we declare a variable as Variant, we can assign a range of worksheet cell values to it with one statement (select worksheet "Read_Write Entire Array"):
Here, VBA casts "a" as an array, dimensions it and gives it a data type to fit the data being read in, and then reads the data from the specified input range.
Here, VBA fills the specified output range with data from array "a" (this works for any 2D array; does not have to be type Variant).
Exercise: Find out what happens if "a" holds more or less values than the specified output range.
If you use this method of writing data out from arrays, be careful that you don't accidentally over-write cell data you don't want to loose! Ctrl-Z won't let you fix it.
Note that Excel made "a" two-dimensional:
MsgBox "a(1,7)=" & a(1, 7)
Even though there is only one row, we must specify both the row and column index to access a single value in array "a".
That's the way Excel maps worksheet ranges to VBA arrays. They are all 2D to Excel, even if they only have one row or one column.
