Advanced Repetition Structure and String Functions Unit 10

Advanced Repetition Structure and String Functions (Unit 10) Visual Basic for Applications

Objectives § In this unit, you will learn how to: § Perform repetition using the Do…Loop statement § Manipulate a string using string functions § Search for a string within another string § Sort a table in Word using the Sort method

Concept Lesson: More on Repetition Structure § You also can use the repetition structure to repeat one or more instructions until some condition is met § You can use the VBA Do…Loop statement to code that type of repetition structure

The Do…Loop Statement § You can use the VBA Do…Loop statement to code a repetition structure that repeats instructions either while some condition is true or until some condition becomes true § VBA also has another version of the Do…Loop statement, where the {While|Until} appears in the Loop clause rather than in the Do clause § The Do…Loop statement begins with the Do clause and it ends with the Loop clause § The {While | Until} indicates that you can select only one of the keywords appearing within the braces

Syntax and Examples of the Do…Loop Statement Exhibit 10 -1: The syntax and examples of the Do…Loop statement

The VBA String Manipulation Functions § VBA provides a set of functions that makes string manipulation an easy task § In this lesson, you will learn how to use four of the most frequently used string manipulation functions: § Left § Right § Mid § Instr

The Left and Right Functions § The Left and Right functions return one or more characters from a string, starting at either the left or right end of the string Exhibit 10 -2: The syntax and examples showing the Left and Right functions

The Mid Function § The Mid function, which has the syntax Mid(String: =string, Start: =start[, Length: = length]), returns length number of characters from the string, beginning with the start character Exhibit 10 -3: The syntax and examples of the Mid function

The Instr Function § You can use the Instr function to search a string to determine if it contains another string § The syntax of the Instr function is Instr(start, string 1, string 2 [, compare]) Exhibit 10 -4: The syntax and examples of the Instr function

Summary To use the Do…Loop statement to code the repetition structure: § Use the syntax shown in Exhibit 10 -1, where condition, which must evaluate to either True or False, can contain variables, constants, functions, mathematical operators, comparison operators, and logical operators To return characters from a string: § Use the Left, Right, or Mid functions § The Left function, the syntax of which is Left(String: =string, Length: =length), returns the left-most length number of characters from the string

Summary To return characters from a string: § The Right function, the syntax of which is Right(String: =string, Length: =length), returns the right-most length number of characters from the string § The Mid function, the syntax of which is Mid(String: =string, Start: =start[, Length: =length]), returns length number of characters from the string, beginning at position start

Summary To search a string if it contains another string: § Use the Instr function, the syntax of which is Instr(start, string 1, string 2[, compare]). The Instr function does not support the use of named arguments § Start is a numeric expression that sets the starting position for the search § If string 2 is contained within string 1, then the Instr function returns the starting position of string 2

§ Excel Lesson: Viewing the Consultants Worksheet and the Break. Name. Apart Procedure Before creating the macro that will separate each consultant’s full name into his or her first and last name, view the workbook, and the code template for the Break. Name. Apart procedure

Pseudocode for the Break. Name. Apart Procedure Exhibit 10 -5: The pseudocode for the Break. Name. Apart procedure

Viewing the Consultants Worksheet and the Break. Name. Apart Procedure § The Break. Name. Apart procedure will use the int. Location variable to store the value returned by the Instr function, and it will use the sht. Consult variable to store the address of the Consultants worksheet

§ Word Lesson: Viewing the Guest Document Before creating the procedure that will sort the list and then remove any duplicate names, view the document and the code template for the Sort. And. Remove. Duplicates procedure

Sorting a Table § You can use the Table object’s Sort method to sort the entries in a table in either ascending or descending order § The Sort method’s Exclude. Header argument, which can be set to either the Boolean value True or the Boolean value False, controls whether the first row in the table is sorted along with the remaining table rows § You can sort the table entries based on the values stored in one, two, or three different columns

Sorting a Table § You use the Sort. Field. Type, Sort. Field. Type 2, and Sort. Field. Type 3 arguments to indicate the type of data contained in the Field. Number, Field. Number 2, and Field. Number 3 columns, respectively Exhibit 10 -6: The syntax and examples of the Table object’s Sort method

Pseudocode for the Sort. And. Remove. Duplicates Procedure Exhibit 10 -7: The pseudocode for the Sort. And. Remove. Duplicates procedure

Coding the Sort. And. Remove. Duplicates Procedure § The Sort. And. Remove. Duplicates procedure will use the int. Row variable in a Do…Loop statement that repeats instructions for each table row that contains guest information § This procedure will use the str. Current. Name and str. Previous. Name variables to store the names entered in the current and previous rows, respectively, in the table

Access Lesson: Viewing the Database and the Locate. Instructor Procedure § Before creating the macro that will display the names of instructors teaching a particular course, you will view the records contained in the database’s Adjunct. Faculty table

Pseudocode for the Locate. Instructor Procedure Exhibit 10 -8: The pseudocode for the Locate. Instructor procedure

Viewing the Database and the Locate. Instructor Procedure § This procedure will use the String variable str. Course. Num to store the course number entered by the user § It will use the String variable str. Found to store the letter “Y”, indicating that the procedure located at least one instructor who teaches the course

Viewing the Database and the Locate. Instructor Procedure § The cnn. Adj. Fac and rst. Adj. Fac object variables will be used to store the addresses of the Connection and Recordset objects, respectively

Viewing the Database and the Locate. Instructor Procedure § You will use the Do…Loop statement along with the Recordset object’s EOF property to code the appropriate loop § The Recordset object’s EOF property returns the Boolean value True if the record pointer, which Access uses to keep track of the current record, is positioned after the last record in the recordset § Because a Recordset is an object rather than a collection, you cannot use the For Each…Next statement that you learned about in unit 9 to code the loop; however, you can use the For…Next statement