31 Pages
Unlock Document

Engineering Science
Lee R.Minardi

ES 2 Computing in Engineering Lecture 15 Loops Program Debugging  Download from Trunk the file: Testing_user_input_v1.xlsm 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 work The above definitions were copied from  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?  Etc. IsNumeric(x) is a Option Explicit VBA standard function Sub Demo1() Dim w as Variant Call sub-to-get-data If IsNumeric(w) Then Call sub-to-do-calculation Else MsgBox error-message End If End Sub 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 End If End Sub  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)  Download: Testing_user_input_v1.xlsm 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 Range("b4").Select calculated name = ActiveCell.Value value we can ActiveCell.Offset(1, 0).Select declare it a h = ActiveCell.Value Double ActiveCell.Offset(1, 0).Select w = ActiveCell.Value ActiveCell.Offset(1, 0).Select - - 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. Range("b4").Select ' 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 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) ActiveCell.Offset(1, 0).Select w = ActiveCell.Value Use If Call GoodNum(w, "weight", 90, 280, error) statement to check the value of error after getting input 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 Val(string) Converts leading ---- numbers in a string to a number. 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 Etc. Call Sub to output results Sub to check data Program end 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 End If End Sub 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 capitalizes it.  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.  Test final
More Less

Related notes for ES-0002

Log In


Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.