Lecture 18 - Data Validation

Lecture 18 - Data Validation - Data Validation &...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Data Validation & Introduction to Flow Control Tutorial 1 Creating a Command Button Variables Scope Data Type of Variables Click Event Assignment Expressions Arithmetic Expressions Formatted Output Using What You Learned Private Sub btnCalculateChangeDue_Click() 'Assign value to variable sngAmountReceived = Range("B9").Value 'Calculate Change Due sngChangeDue = sngAmountReceived sngAmountDue `Apply currency format to Amount Received cell Range("B9").Value = FormatCurrency(sngAmountReceived) 'Display Change Due in cell with currency format Range("B10").Value = FormatCurrency(sngChangeDue) End Sub Data Validation Number out of range Non-numeric entry Empty entry Negative number Invalid number of characters Dialog Box Excel uses the Data Validation dialog box to validate input from the user. The dialog box has 3 tabs. Settings: used to set the validation criteria, Input Message: shows a message when the cell is selected Error Alert: displays a message if invalid data is entered Practice Price of Item Quantity Ordered Grade Calculation MsgBox In VBA, the MsgBox function is used to display a message in a dialog box. The message box waits for the user to select an option, and can then execute instructions based upon the button selected. Example MsgBox "Are you sure you want to quit? ", vbYesNo, "Quit" Input Box When input is needed from the user, an Input Box can be used. Input boxes are similar to screen prompts in other languages. In the Input Box, a prompt is displayed requesting input from the user. tQty = InputBox("Enter the order quantity", "Order Quantity Flow Control The flow of a program refers to the order in which a program's instructions are executed. Normal flow, unless directed otherwise, is sequential: each instruction is executed in order within the procedure. Decision statements and repetition statements alter the normal sequential flow of control. Decision statements allow for a selection of which instructions will be executed next. Repetition statements provide the ability to repeat a set of instructions. Decision and repetition statements use relational and logical operators to test conditions. Conditional Operators A condition consists of one or more tests that compare one value to another. A simple expression consists of an operator connecting two variables and/or constant operands: Conditional Operators Can be used with whole numbers, floating point numbers or character data, and must be typed as shown below: < <= > >= = <> is less than is less than or equal to is greater than is greater than or equal to is equal to is not equal to If-Then Statements A decision structure can be implemented by using an If-Then statement written in the following format: If condition Then Instructions when the condition is true End If Simple Example What if you wanted to tell the user something about the number that they entered? If the number was greater than 10 you want them to know that they get a 15% discount. Any Questions? ...
View Full Document

Ask a homework question - tutors are online