Introduction to Engineering ComputingGEEN 1300

Lecture 715 June 2010

• Review for midterm

2

Double-click the Compute Re button.This will cause the following Sub wrapper to pop up in a code module:

In here, we must add codeto carry out the necessary calculations and display theReynolds Number result

Hooking VBA code to the “Compute Re” button…

3

This variable is the name you created for your textbox

This is a variable you create and use in your calculation.

Do NOT use the textbox name on the LHS of your calculation – it will not work!!

4

How to run and remove user forms

insert a new module and add this Sub to start the UserForm

double-click the Quit button and add the code to remove(“unload”) the UserForm

run the StartReynoldsNumber macro…

…and entervalueswith unitsselected

5

UserForms

UserForms can be loaded automatically when the workbookis opened. (This is called an “event handler.”) And they can also be unloaded automatically when the workbook is closed.

1. Go to VBE and double click on thisworkbook item in Project Explorer. (If Project Explorer doesn’t show up, use view)

2. Change left field at top from General to Workbook.3. Type in ReynoldsNumber.Show

(this is not on any exam; it’s just a useful FYI)

6

Things to Remember!!!

• Create your own variables to carry out calculations, NOT the variables in the user form

• Make sure the variables from the user form that you assign to your created variables are the same names as in your user form

• These variables are those used from your text boxes and your option buttons, NOT your labels

• You should not have the same name for your text box as for your label

7

This variable is the name you created for your option button

FormatNumber – number of digits after the decimal point. Does not round.

8

If you get a Reynolds number of 2134.3721 and the program says MsgBox(FormatNumber(Re,1)), what

will the message box say?

a) 2134

b) 2135

c) 2134.3

d) 2134.37

e) 2134.4

9

MsgBox Function

MsgBox(“prompt”,buttons,”title”,helpfile,context)

optional

10

MsgBox Function

MsgBox(“prompt”,buttons,”title”,helpfile,context)

optional

Button codesvbOKOnly vbExclamationvbOKCancel vbInformationvbAbortRetryIgnore vbDefaultButton1vbYesNoCancel vbDefaultButton2vbYesNo vbDefaultButton3vbRetryCancel vbDefaultButton4vbCritical vbSystemModalvbQuestion

11

MsgBox Function

Putting line breaks into the message displayed:

MsgBox result is button clicked by user

Announcements:

• Midterm exam Thursday at 8am

• Open book• Open notes• Open Excel/VBE• No cheat sheet (duh)• No collaboration

13

What the exam looks like:

Excel:

Displaying data (surface plots, creating xy grids)

Fitting a model to a set of data points

How good is the model overall?

How good are the coefficients?

Transforming equations to regression-friendly forms

14

What the exam looks like:

VBE:

Defining functions for use in spreadsheet

Defining subs to manipulate spreadsheet

Tracing through a function or sub (control flow)

Calling a sub or function from another sub

Fixing functions or subs with bugs

Making a UserForm

15

How to study:

• Review all lecture notes

• Review all lab & homework problems

• Re-read any textbook sections & lecture notes that you need in order to fill in or clarify any shaky/fuzzy topics

• Think about Excel/VBA strengths & weaknesses

• Email and/or come to help hours with any questions

16

Excel syntax

• Precedence order, parentheses

• Cell references & cell addressing

• Formula copy

• How to translate equations & word statements to/from Excel

Plotting

Different types of models:

• Building them (see next slides)

• Evaluating them (R2 & co.)

• Using them

17

Trendline & data regression

• What does each one do? What’s the difference between them?

• Which one do you use for what kind of problem?

• What do you feed into them and what do they return?

• How to transform equations so that these tools will work on them (also know when to do that and when you can’t)

• How to fit linear models if you don’t have these tools?

18

Excel, cont.:

Goal Seek & Solver

• What does each one do?

• What’s the difference between them?

• Which one do you use for what kind of problem?

• What do you feed into them and what do they return?

19

Excel, cont.:

Matrices

• Matrix math in Excel: naming, addition, subtraction, scaling, multiplication, inverse, determinant, transpose

• The identity matrix

• Systems of linear equations:

• How they correspond to matrix problems

• How to translate sets of linear equations to/from the equivalent matrix equation

• How to solve that matrix equation

20

Excel, cont.:

Iteration

• How & when to use iteration

Bisection

• What it does (including what a root is)

• How the algorithm works

• How to implement it in Excel

21

VBA:

The environment

• VBA “world” & Excel “world”

• The VBE

• Modules: what they are & how you insert them

• The debugger:

• How you use it

• What it lets you find out

• How that’s different between Subs & Functions

22

VBA, cont.:

Basic Syntax

• Write/evaluate VBA statements

• Know the basic functions (arithmetic, logic, relational)

• Understand the precedence rules

Subs & Functions

• Know the differences

• Know how to write & use both

23

VBA, cont.:

Decisions

• Know how to decode them and how to write them

• Including working with logical and relational operators

Loops

• Know how all five loop structures work

• Be able to write one for a prescribed task

• Be able to figure out what a given loop will produce

Flowcharts

• Be able to translate simple flowcharts to/from code

24

VBA, cont.:

Modular programming

Variables

• When, why, & how do you declare variables?

• When should you use different kinds of variables?

• Scoping

Dialog boxes

• MsgBox & InputBox

• Understand them and use them

User forms