1 / 35

Using the selection structure (Unit 7)

Using the selection structure (Unit 7). Visual Basic for Applications. Objectives. In this unit, you will learn how to: Perform selection using the If…Then...Else statement Write instructions that use comparison operators and logical operators Use the UCase function

newkirk
Télécharger la présentation

Using the selection structure (Unit 7)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using the selection structure(Unit 7) Visual Basic for Applications

  2. Objectives • In this unit, you will learn how to: • Perform selection using theIf…Then...Else statement • Write instructions that use comparison operators and logical operators • Use the UCase function • Use the Nest If…Then…Else statement • Use the PublishObjects collection and PublishObject objects in Excel to publish datato a Web page • Use the TableOfContents collection and the TableOfContents objects in Word

  3. You use the selection structure, also called the decision structure, when you want a procedure to make a decision or comparison and then, based on the result of that decision or comparison, select one of two paths You can use the VBA If…Then…Else statement to include a selection structure in a procedure Concept Lesson: Discussing the selection structure Exhibit 7-1: The syntax of the If…Then…Else statement

  4. Using the If…Then…Else Statement • The items appearing in square brackets ([ ]) in the syntax are optional • Words in bold, however, are essential components of the statement • Thus, the words, If, Then, and End, If must be included in the statement • Items in italics indicate where the programmermust supply information pertaining to thecurrent procedure • As mentioned earlier, the If…Then…Else statement’s condition can contain variables, constants, functions, arithmetic operators, comparison operators, and logical operators

  5. Comparison Operators • You use comparison operators, sometimes referred to as relational operators, to compare two values • When a condition contains more than one comparison operator, the comparison operators are evaluated from left to right in the condition • Comparison operators are evaluated after any arithmetic operators

  6. Most Commonly Used Comparison Operators Exhibit 7-2: The most commonly used comparison operators

  7. Comparison Operators Exhibit 7-3: The evaluation steps for a condition containing arithmetic and comparison operators Exhibit 7-4: Some examples of the If…Then…Else statement

  8. UCase Function • As is true in many programming languages, string comparisons in VBA are casesensitive, which means that the uppercase version of a letter is not the same as its lowercase counterpart • One way of handling the string comparison problem is to include the UCase function,whose syntax is UCase(String:=string), in your string comparisons • You also can use the UCase function in an assignment statement

  9. Examples of If…Then…Else Statements Whose ConditionsContain the UCase Function Exhibit 7-5: some examples of If…Then…Else statements whose conditions contain the UCase function

  10. The two most commonly used logical operators are And and Or You use the And and Or operators to combine several conditions into one compound condition Logical Operators Exhibit 7-6: The most commonly used logical operators

  11. All compound conditions containing a logical operator will evaluate to an answer of either True or False only Logical Operators Exhibit 7-7: The truth tables for the And and Or logical operators

  12. Examples of If…Then…Else Statements Whose ConditionsContain Logical Operators Exhibit 7-8: Examples of If…Then…Else statements whose conditions contain logical operators

  13. Logical Operators When a condition contains arithmetic, comparison, and logical operators, the arithmetic operators are evaluated first, then the comparison operators are evaluated, and then the logical operators are evaluated

  14. A nested If…Then…Else statement is one in which either the Then clause or the Else clause includes yet another If…Then…Else statement Nesting If…Then…Else Statements Exhibit 7-9: The syntax of a nested If…Then…Else statement

  15. Two Examples of Nested If…Then…Else Statements Exhibit 7-10: Two examples of nested If…Then…Else statements

  16. Summary To use the If…Then…Else statement to code the selection structure: • Use the syntax shown in Exhibit 7-1, where condition can contain variables, constants, functions, arithmetic operators, comparison operators, and logical operators To compare two values: • Use the comparison operators(=, >, <, >=, <=, < >)

  17. Summary To return the uppercase equivalent of a string: • Use the UCase function, whose syntax is UCase(String:=string) To create a compound condition: • Use the logical operators (And and Or) To nest If…Then…Else statements: • Use the syntax shown in Exhibit 7-10

  18. Excel Lesson: Using the selection structure in Excel View the Calculator worksheet and the code template for the PublishCalculator procedure.

  19. Contained within each Workbook object is a PublishObjects collection, made up of individual PublishObject objects Each PublishObject object represents a workbook item that has been saved to a Web page The PublishObjects Collection and PublishObject Objects Exhibit 7-11: The PublishObjects collection and PublishObject objects shown in the Excel object model

  20. The Add Method • The Add method’s SourceType argument identifies the type of data the PublishObject object will represent, and it can be any of the seven intrinsic constants • The Add method’s Filename argument specifies the location and name of the file to which the data will be published • The Add method’s Sheet argument specifiesthe name of the worksheet that contains thedata you want to publish, and the Source argument identifies which item in the worksheet is to be published

  21. Syntax and Two Examples of the PublishObjects Collection’s Add Method Exhibit 7-12: The syntax and two examples of the PublishObjects collection’s Add method

  22. The Add Method • The Add method’s HtmlType argument, which can be any of the four intrinsic constants listed in Exhibit 7-12, specifies whether the published item is interactive or static in the Web page • Interactive data can be manipulated bythe user • Static data can be viewed only and can’t be manipulated by the user in any way

  23. XlHtmlType Enumeration [Excel 2007 Developer Reference] http://msdn.microsoft.com/en-us/library/bb241321.aspx

  24. The Add Method • After using the PublishObjects collection’s Add method to create a PublishObject object, you then use the PublishObject object’s Publish method to write the HTML necessary to create the Web page • The syntax of the Publish method is expression.Publish Create:=booleanValue, where expression is a PublishObject object and Create argument is a Boolean value, either True or False, that controls how the data represented by the PublishObject object is saved to the HTML file specified in the Add method’s Filename argument

  25. Procedure for the PublishCalculator Procedure Exhibit 7-13: The pseudocode for the PublishCalculator procedure

  26. Coding thePublishCalculator Procedure • Exhibit 7-13 contains a PublishObject object variablenamed pubCalc • A PublishObject object represents a workbook item that is saved to a Web page

  27. Word Lesson: Using the selection structure in Word View the personal assessment document.

  28. The TablesOfContents Collection • When you create a table of contents in a Word document, a special code, called a field code, is inserted into the document • The field code contains the instructions used to generate the table of contents, referred to as the field results

  29. You use the TablesOfContents collection’s Add method to add a TableOfContents object toa document The TablesOfContents Collection and TableOfContents Objects Exhibit 7-14: The TablesOfContents collection and TablefContents objects shown in the Word object model

  30. In the Add method’s syntax, docObject is the name of a Document object variable, and range, which must be a Range object, represents the area where you want the table of contents to appear in the document After adding a table of contents to a document, you can use the TableOfContents collection’s Format property to format the table of contents to one of the predesigned formats available in Word The Add Method Exhibit 7-15: The syntax and three examples of the TablesOfContents collection’s Add method

  31. The syntax of the Format property is docObject.TablesOfContents.Format =constant, where docObject is the name of a Document object and constant is one of the intrinsic constants Each constant represents one of the predesigned formats available for tables of contents in Word The Format Property Exhibit 7-16: The valid constants for the TablesOfContents collection’s Format property

  32. Coding theCreateToc Procedure Exhibit 7-17: The pseudocode for the CreateToc procedure

  33. Variables Used by the CreateToc Procedure Exhibit 7-18: The variables used by the CreateToc procedure

  34. Access Lesson:Using the selection structure in Access Before modifying the PaymentUpdate procedure, open the Trip database and view the Payments table

  35. Modified Pseudocode for the PaymentUpdate Procedure Exhibit7-19: The modified pseudocode for the PaymentUpdate procedure

More Related