Get documents about "The Repetition Structure and the With Statement
Document Sample
9
The Repetition Structure and
the With Statement
Visual Basic for Applications
1
9 Objectives
In this tutorial, you will learn how to:
Perform repetition using the
For…Next statement
Perform repetition using the
For Each…Next statement
Use the With statement to access the
properties and methods of an object
Use the CurrentRegion and Resize
properties in Excel
2
9 Objectives
In this tutorial, 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
3
9 Concept Lesson:
The Repetition Structure
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
4
9 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
5
9 Syntax and an Example of the
For…Next Statement
6
9 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
9 Processing Steps for the Code
Shown in Figure 9-2
8
For…Next Loops that Repeat
9 Instructions for Each Object
in a Collection
9
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 of
the 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
9 Syntax and Two Examples of
the For Each…Next Statement
11
9 Processing Steps for the Code
Shown in Example 1 in Figure 9-5
12
9 The For Each…Next Statement
When you
compare the
examples shown
in Figure 9-5 with
those shown in
Figure 9-4, you
will notice that the
For Each…Next
statement
provides a more
convenient way of
repeating a block
of instructions
for each object in
a collection
13
9 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
14
9 Summary
To use the For…Next statement to code the
repetition structure:
Use the syntax shown in Figure 9-2, where
counter is the name of the numeric variable
used to keep track of the number of times
the loop instructions are processed. The
startvalue, endvalue, and stepvalue items
control how many times to process the
loop instructions
15
9 Summary
To use the For Each…Next statement to code
the repetition structure:
Use the syntax shown in Figure 9-5, 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 Figure 9-8, where
object is the name of the object whose
properties and methods you want to access
16
Excel Lesson:
9 Modifying the DisplaySales
Macro Procedure
To view the
workbook
and the
DisplaySales
procedure,
use the steps
on page 526
of the
textbook
17
9 Modified Pseudocode for the
DisplaySales Procedure
18
9 Modifying the DisplaySales
Macro Procedure
A cell in an Excel
worksheet is a
Range object
Excel does not
have a Cell object
To begin
modifying the
DisplaySales
procedure, use
the steps on
pages 528
and 529 of
the textbook
19
9 The CurrentRegion
and 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
20
9 The CurrentRegion
and 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
21
9 Syntax and Examples of the
Resize Property
22
9 The CurrentRegion
and Resize Properties
You can’t use the
Resize property to
remove rows and
columns from, or add
rows and columns to,
the top and left side,
respectively, of a range
To complete the
DisplaySales
procedure, use the
steps on pages 532
and 533 of the textbook
23
9 The CurrentRegion
and Resize Properties
To test the DisplaySales macro procedure,
use the steps on page 534 of the textbook
24
Word Lesson:
9 Viewing the Registration Form
and Participant List Documents
The PrintAndRecordInfo macro will
require the use of two Word documents:
a Registration Form document and a
Participant List document
To view the files and the
PrintAndRecordInfo procedure, use
the steps on pages 540 and 541 of
the textbook
25
9
Registration Form Document
26
9 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 or
more documents
27
9 Opening, Activating, and
Closing a Document
In the syntax, expression can be either the
Documents collection or a Document object
28
9 Syntax and Examples of the
Close Method
29
9 Referring to the Main Text
Story 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
30
9 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
31
9 Coding the
PrintAndRecordInfo 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
32
9 Pseudocode for the
PrintAndRecordInfo Procedure
33
9 Coding the
PrintAndRecordInfo 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
34
9 Coding the
PrintAndRecordInfo Procedure
To begin coding the PrintAndRecordInfo
procedure, use the steps on pages 547 to 549
of the textbook
35
9 Partially Completed
PrintAndRecordInfo Procedure
36
9 Coding the
PrintAndRecordInfo Procedure
To finish
coding the
procedure,
use the
steps on
pages 549
to 551
of the
textbook
37
9 Coding the
PrintAndRecordInfo Procedure
To test the PrintAndRecordInfo macro,
use the steps on pages 552 and 553 of
the textbook
38
9 Access Lesson:
Creating the PostScores Procedure
Begin by viewing the three reports contained
therein, and the code template for the
PostScores procedure
To view the database’s three reports and the
PostScores procedure, use the steps on
pages 560 to 562 of the textbook
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
39
9 Report100 Report Shown
in Design View
40
9 Report100 Report Shown
in Print Preview
41
9 Creating the
PostScores Procedure
42
9 Creating the
PostScores Procedure
Use the aobToPost AccessObject object variable in the For
Each…Next statement that refers to each of the reports in
the database
To begin coding the PostScores procedure, use the steps
on pages 563 and 564 of the textbook
43
9 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
44
9 Syntax of the DoCmd Object’s
OutputTo Method
45
9 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 figure
A token is a special code that indicates where to insert
output and other information in the outputfile
46
9 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 Figure 9-35
47
9 Syntax and Examples of the
DoCmd Object’s Close Method
48
9 Completing the
PostScores Procedure
According to Steps 2c and 2d in the
pseudocode shown in Figure 9-30, 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
To complete the PostScores procedure, use
the steps on pages 569 to 571 of the textbook
49
9
Completed PostScores Procedure
50
9 Report100.htm File Displayed
in Internet Explorer
51
Related docs
