Using the selection structure Unit 7 Visual Basic

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 § Use the Nest If…Then…Else statement § Use the Publish. Objects collection and Publish. Object objects in Excel to publish data to a Web page § Use the Table. Of. Contents collection and the Table. Of. Contents objects in Word

Concept Lesson: Discussing the selection structure § 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 Exhibit 7 -1: The syntax of the If…Then…Else statement

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 programmer must supply information pertaining to the current procedure § As mentioned earlier, the If…Then…Else statement’s condition can contain variables, constants, functions, arithmetic operators, comparison operators, and logical operators

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

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

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

UCase Function § As is true in many programming languages, string comparisons in VBA are case sensitive, 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

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

Logical Operators § 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 Exhibit 7 -6: The most commonly used logical operators

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

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

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

Nesting If…Then…Else Statements 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 Exhibit 7 -9: The syntax of a nested If…Then…Else statement

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

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 (=, >, <, >=, < >)

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

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

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

The Add Method § The Add method’s Source. Type argument identifies the type of data the Publish. Object 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 specifies the name of the worksheet that contains the data you want to publish, and the Source argument identifies which item in the worksheet is to be published

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

The Add Method § The Add method’s Html. Type 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 by the user § Static data can be viewed only and can’t be manipulated by the user in any way

Xl. Html. Type Enumeration [Excel 2007 Developer Reference] http: //msdn. microsoft. com/en-us/library/bb 241321. aspx

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

Procedure for the Publish. Calculator Procedure Exhibit 7 -13: The pseudocode for the Publish. Calculator procedure

Coding the Publish. Calculator Procedure § Exhibit 7 -13 contains a Publish. Object object variable named pub. Calc § A Publish. Object object represents a workbook item that is saved to a Web page

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

The Tables. Of. Contents 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

The Tables. Of. Contents Collection and Table. Of. Contents Objects § You use the Tables. Of. Contents collection’s Add method to add a Table. Of. Contents object to a document Exhibit 7 -14: The Tables. Of. Contents collection and Tablef. Contents objects shown in the Word object model

The Add Method § In the Add method’s syntax, doc. Object 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 Table. Of. Contents collection’s Format property to format the table of contents to one of the predesigned formats available in Word Exhibit 7 -15: The syntax and three examples of the Tables. Of. Contents collection’s Add method

The Format Property § The syntax of the Format property is doc. Object. Tables. Of. Contents. Format =constant, where doc. Object 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 Exhibit 7 -16: The valid constants for the Tables. Of. Contents collection’s Format property

Coding the Create. Toc Procedure Exhibit 7 -17: The pseudocode for the Create. Toc procedure

Variables Used by the Create. Toc Procedure Exhibit 7 -18: The variables used by the Create. Toc procedure

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

Modified Pseudocode for the Payment. Update Procedure Exhibit 7 -19: The modified pseudocode for the Payment. Update procedure