1 / 38

Repetition Structure and With Statement in VBA

Learn how to perform repetition using For...Next and For Each...Next statements, and access object properties and methods using the With statement in Visual Basic for Applications (VBA).

robertljones
Download Presentation

Repetition Structure and With Statement in VBA

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. Working with the Repetition Structure and the With Statement(Unit 9) Visual Basic for Applications

  2. Objectives • In this unit, you will learn how to: • Perform repetition using theFor…Next statement • Perform repetition using theFor Each…Next statement • Use the With statement to access the properties and methods of an object • Use the CurrentRegion and Resize properties in Excel

  3. Objectives • In this unit, you will learn how to: • Open, activate, and close a Word document using VBA code • Insert text in a Word document using VBA code • Use the DoCmd object’s OutputTo and Close methods in Access • Save a report in Access as an HTML file

  4. Concept Lesson:Understanding the repetition structure and the With statement • Like the sequence and selection structures, you already are familiar with the repetition structure • Programmers use the repetition structure, also called looping or iteration, to direct the computer to repeat one or more instructions either a precise number of times or until some condition is met

  5. The For…Next Statement • You can use the VBA For…Next statement to include a repetition structure in a procedure • The For…Next statement begins with the For clause and ends with the Next clause • You can use the Exit For statement to exit the For…Next loop prematurely • You can nest For…Next statements, which means that you can place one For…Next statement within another For…Next statement • In the syntax,counter is the name of the numeric variable that will be used to keep track of the number of times the loop instructions are processed

  6. The For…Next Statement • The startvalue, endvalue, and stepvalue items control how many times the loop instructions should be processed • The startvalue tells the loop where to begin, the endvalue tells the loop when to stop, and the stepvalue tells the loop how much to add to (or subtract from if the stepvalue is a negative number) the counter each time the loop is processed • The For clause’s startvalue, endvalue, and stepvalue values must be numeric and they can be either positive or negative, integer or non-integer

  7. Syntax and an Example of the For…Next Statement Exhibit 9-1: The syntax and an example of the For…Next statement

  8. Processing Steps for the Code Shown in Exhibit 9-2 Exhibit 9-2: The processing steps for the code shown in Exhibit 9-1

  9. The For Each…Next Statement • You can use the VBA For Each…Next statement to repeat a group of instructions for each object in a collection • In the syntax, element is the name ofthe object variable used to refer to each object in the collection, and group is the name of the collection in which the object is contained • The For Each clause first verifies that the group contains at least one object

  10. Syntax and Two Examples of the For Each…Next Statement Exhibit 9-3: The syntax and two examples of the For Each…Next statement

  11. Processing Steps for the Code Shown in Example 1 in Exhibit 9-3 Exhibit 9-4: The processing steps for the code shown in Example 1 in Exhibit 9-3

  12. The For Each…Next Statement Compare the examples shown in Exhibit 9-5 with those shown in Exhibit 9-4 Notice that the For Each…Next statement provides a more convenient way of repeating a block of instructions for each object in a collection Exhibit 9-5: The processing steps for the code shown in Example 2 in Exhibit 9-3

  13. The With Statement The With statement provides a convenient way of accessing the properties and methods of a single object In the syntax, object is the name of the object whose properties or methods you want to access Exhibit 9-6: The syntax and an example of the With statement

  14. Summary To use the For…Next statement to code the repetition structure: • Use the syntax shown in Exhibit 9-2, where counter is the name of the numeric variable used to keep track of the number of timesthe loop instructions are processed. The startvalue, endvalue, and stepvalue items control how many times to process theloop instructions

  15. Summary To use the For Each…Next statement to code the repetition structure: • Use the syntax shown in Exhibit 9-3, where element is the name of the object variable that will be used to refer to each object in the collection, and group is the name of the collection in which the object is contained To use the With statement to access the properties and methods of an object: • Use the syntax shown in Exhibit 9-6, where object is the name of the object whose properties and methods you want to access

  16. Excel Lesson:Using the repetition structure and the With statement in Excel View the workbook and the DisplaySales procedure. Exhibit 9-7: The Sales worksheet in the Monthly Sales workbook

  17. Modified Pseudocode for the DisplaySales Procedure Exhibit 9-8: The modified pseudocode for the DisplaySales procedure

  18. The CurrentRegionand Resize Properties • You can use a Range object’s CurrentRegion property to return a range that contains the entire region in which the Range object resides • The syntax of the CurrentRegion property is rangeObject.CurrentRegion • A region is defined as a block of cells bounded by any combination of empty rows, empty columns, and the edges of the worksheet • You can use a Range object’s Resize property to resize a range

  19. The CurrentRegionand Resize Properties • In the syntax, rangeObject is the original range, and rowsize and columnsize are the number of rows and columns, respectively, you want in the new range

  20. Syntax and Examples of the Resize Property Exhibit 9-9: The syntax and examples of the Resize property

  21. Word Lesson:Using the repetition structure and the With statement in Word • The PrintAndRecordInfo macro will require the use of two Word documents: a Registration Form document and a Participant List document • View the files and the PrintAndRecordInfo procedure.

  22. Opening, Activating, and Closing a Document • You use the Documents collection’s Open method to open an existing document • When multiple documents are open, only one can be the active document • You can use the Document object’s Activate method to activate another document • The syntax of the Activate method is documentObject.Activate • You use the Close method to close one ormore documents

  23. Opening, Activating, and Closing a Document • In the syntax, expression can be either the Documents collection or a Document object Exhibit 9-10: The two versions of the Open method’s syntax

  24. Syntax and Examples of the Close Method Exhibit 9-11: The syntax and examples of the Close method

  25. Referring to the Main TextStory in a Document • You can use the Document object’s Content property to return a Range object that contains the document’s main text story • The syntax of the Content property is documentObject.Content

  26. Inserting Text in a Document • You can use the InsertBefore and InsertAfter methods to insert text in a document • In the syntax, expression is either a Selection object or a Range object, and string is the text you want to insert either before (InsertBefore method) or after (InsertAfter method)the object Exhibit 9-12: The syntax and examples of the InsertBefore and InsertAfter methods

  27. Coding thePrintAndRecordInfo Procedure • The PrintAndRecordInfo procedure will need to print the Registration Form document and also record each participant’s name and address in the Participant List document Exhibit 9-13: The name and address information shown in the Participant List document

  28. Coding thePrintAndRecordInfo Procedure • The PrintAndRecordInfo procedure will use the intAnother variable to store the value returned by the MsgBox function • It will use the docRegis and docList object variables to store the address of the Registration Form and Participant List documents, respectively

  29. Access Lesson:Using the repetition structure and the With statement in Access • Begin by viewing the three reports contained therein, and the code template for the PostScores procedure • You must open a report in Design View before you can modify one of its controls • The procedure will use the strName String variable to store the value returned by the InputBox function

  30. Creating thePostScores Procedure Exhibit 9-14: The pseudocode for the PostScores procedure

  31. Creating thePostScores Procedure Use the aobToPost AccessObject object variable in the For Each…Next statement that refers to each of the reports in the database

  32. The DoCmd Object’s OutputTo Method • You can use the DoCmd object’s OutputTo method to save a report in HTML format, allowing the report to be published on the World Wide Web and then viewed with a Web Browser, such as Microsoft Internet Explorer • In addition to using the OutputTo method to output a report to an HTML file, you also can use it to output other Access database objects (datasheets, forms, modules, and data access pages) using other formats • You enter the name of the object you want to output in the ObjectName argument

  33. Syntax of the DoCmd Object’s OutputTo Method Exhibit 9-15: The syntax of the DoCmd object’s OutputTo method

  34. The DoCmd Object’s OutputTo Method • You use the OutputFormat argument to specify the type of format you want used to output the data, and it can be one of the intrinsic constants shown in the exhibit • A token is a special code that indicates where to insert output and other information in the outputfile Exhibit 9-16: The syntax and examples of the DoCmd object’s OutputTo method

  35. The DoCmd Object’s Close Method • You can use the DoCmd object’s Close method to close an open report • The Close method’s ObjectType argument specifies the type of object whose window you want to close, and it can be one of the intrinsic constants shown in Exhibit 9-17

  36. Syntax and Examples of the DoCmd Object’s Close Method Exhibit 9-17: The syntax and examples of the DoCmd object’s Close method

  37. Completing thePostScores Procedure • According to Steps 2c and 2d in the pseudocode shown in Exhibit 9-14, you need to save a copy of each report in HTML format and also close each report • You can use the DoCmd object’s OutputTo and Close methods in the procedure to accomplish these tasks

More Related