ES 2 Computing in Engineering
Download from Trunk the file:
ES 2 Computing in Engineering 1 Academic Integrity
6. Do not represent the work of someone else as your own. If
you get significant help from someone on an assignment,
reference that person in the work you pass in.*
Consult - to ask the advice or opinion of
Collaborate - to work jointly with others or together especially in
an intellectual endeavor
Copy - an imitation, transcript, or reproduction of an original
The above definitions were copied from Merriam-webster.com
You may consult with another student but not collaborate or copy.
Students who submit assignments considered to be copies will be
reported to the Dean of Students.
* ES 2 2013 Syllabus
ES 2 Computing in Engineering 3 Checking the Validity of Input Data
IsNumeric( x ) True or False?
ES 2 Computing in Engineering 4 Testing User Input in a Sub Procedure
Use Variant for the data type of data being read from a
worksheet cell or form. Then test for validity –
Is the input data a number or text?
If a number, is it in the appropriate range?
If text, does it exceed maximum number of characters?
IsNumeric(x) is a
Option Explicit VBA standard function
Dim w as Variant
If IsNumeric(w) Then
ES 2 Computing in Engineering 5 Testing User Input with Sub, Modularize Version
Create a Sub to check variable if it is a positive number.
Sub PositiveNumber(x, vname, DataError)
' determine if variable x is a number > 0,
' if it is not, then it sets error = True
If IsNumeric(x) = False Or x <= 0 Then
MsgBox vname & " must be a positive number!"
DataError = True
Use of the PositiveNumber in a program code to check variable
s = ActiveCell.Value
'Check channel slope
Call PositiveNumber (s, "Channel slope", DataError)
Is error = False? If so end program
ES 2 Computing in Engineering 6 Evaluating If User Input is Valid
Input Design and Variable Typing (Dim)
ES 2 Computing in Engineering 7 Evaluating If User Input is Valid
Define input values as Variants so that they can be
tested for validity by the program,
Option Explicit These will all be
Sub Demo1() Variants
Dim name, h, w, bmi, zip, ugrad
Dim jacket As Double
Since this is a
name = ActiveCell.Value value we can
ActiveCell.Offset(1, 0).Select declare it a
h = ActiveCell.Value Double
w = ActiveCell.Value
- - etc. - -
ES 2 Computing in Engineering 8 Len(string)
Evaluating If User Input is Valid
VBA function returns
Input As Variants, with error checking the number of
characters in a string.
' get input data and test for validity
name = ActiveCell.Value
If Len(name) > 20 Then
MsgBox "error, Name must be 20 characters or less"
End If The End statement
will fully terminate
ActiveCell.Offset(1, 0).Select program here.
h = ActiveCell.Value
Call GoodNum(h, "height", 48, 84, error)
w = ActiveCell.Value Use If
Call GoodNum(w, "weight", 90, 280, error) statement to
check the value
of error after
ES 2 Computing in Engineering 9 Evaluating If User Input is Valid
Zip is text but needs to be evaluated as a number
Dim zip ‘ a variant to allow any input
Dim zipnum As Integer ‘ used for when zip needs
‘ to be treated as a number
---- numbers in a string to
zip = ActiveCell.Value
zipnum = Val(zip) ‘ converts zip to number
Call GoodNumber(zipnum, "zip", 0, 99999, error)
Download Testing_user_input.xlsm from Trunk
ES 2 Computing in Engineering 10 Using a Sub to Check Input
Start by assuming
Main Sub() there are no errors.
Error = False
Call Sub to get data Sub to get data (variables)
IF Error Then End Get 1 data item
Call Sub to check data Sub to check data
Get 2 data item
Call Sub to do calculations
Call Sub to check data Sub to check data
Call Sub to output results
Sub to check data
Is data item a number within range?
If not Error = True
ES 2 Computing in Engineering 11 Using a Sub to Check Input
Sub to check input
Sub GoodNumber(x, vname, a, b, error)
' x = variable, vname = desricption, a & b = lower ‘
‘ and upper limits, error = true if bas data
If IsNumeric(x) = False Or x < a Or x > b Then
MsgBox vname & " must be a number " & vbCrLf _
& "greater than " & a & vbCrLf & "less than" & b
error = True ‘ bad data
ES 2 Computing in Engineering 12 Use Variant for Input of User Text Data
Accepts numbers and/or text
Use program to validate values then convert to appropriate data
type if necessary.
Dim x As Variant or Dim x
IsNumeric(x) to test if a variable contains a number
Val(x) to convert from text to a number. E.g., “3” to 3
Instr(1,x,”.”) to test if decimal point is present
Write a VBA statement that will test if num is a
number greater than zero and also an integer?
ES 2 Computing in Engineering 13 VBA Variable Names
Must begin with a letter.
Cannot contain spaces (use capitalization or _ )
E.g., BeamWeight, TotalRain, Total_Rain
Are not case sensitive (unlike Mathcad).
Limited to 255 characters.
Cannot be the same as an Excel keyword or object
E.g., Sheet, ActiveCell, Call, Sin, Abs, If, …)
If not sure, type variable name in lowercase and see if VBA
Use descriptive names.
E.g., FirstName, ChemName, StartTime, nGames
i, j, k, often used as index or counter.
n, often used as total number.
Declare data type of variable with Dim statement.
ES 2 Computing in Engineering 14 Tips for Implementing a Program
Determine program input, output, and method for handling both.
Worksheet cells, input box, message box, form, data file.
Determine primary variables that will be needed and give them a
name and data type.
Determine how to compute output from input. Focus on most
ambiguous component of the program. Create a flow chart for
the flow of program logic if compute sequence is not simple.
Subdivide program logic into individual pieces that can be
programmed as individual Functions or Sub procedures.
Start coding program segments testing each as you proceed.
Develop plan and code for most difficult portion first.
Document your code as you go.
Test interaction of code segments.