Chapter 7 - Emunix Emich
Download
Report
Transcript Chapter 7 - Emunix Emich
1
Chapter 7
VBA Modules,
Functions, Variables,
and Constants
7
Chapter Objectives
• Design VBA code that is organized into
standard modules, independent class
modules, and form and report class modules
• Determine when each type of module is
appropriate
• Develop simple sub procedures, functions,
and property procedures
7
Chapter Objectives
• Determine when a sub procedure or a
function is most appropriate
• Declare arguments, variables, and constants
• Invoke procedures and pass arguments
between procedures
• Cancel and manage events through VBA
7
Modules
• Procedure
Collection of VBA statements combined in a
manner to accomplish a processing task
• Module
Group of procedures related to a specific
process
Makes maintaining the application easier in the
long run
7
Modules
• Form and report class modules
Include code for all event procedures triggered by
events occurring on a specific form or report, or the
controls on that form or report
Figure 7-1
Procedures
within
modules
7
Modules
• Standard module
Module you can place sub and function procedures and
declarations in to make available to other procedures
throughout your application
Procedures can be executed whenever the application is
running
• Independent class modules
Not connected to a form or report
Procedures inside an independent class module can be
executed as long as the application is open
7
Selecting Module Types
• Selection of the best module type for a
procedure depends on:
Runtime efficiency of the application
Amount of primary memory expected on the
computer executing the application
Number of forms and reports that use a
procedure
7
Selecting Module Types
• Standard modules and independent class modules
are loaded into primary memory when an
application opens
Use primary memory even when they are not needed,
yet the time needed to load the module occurs once
• Form and class modules are loaded into primary
memory when the form or report opens
Do not use memory until form or report is opened, but
the form or report takes longer to open because module
must be loaded
7
Sub Procedures
• Sub procedures
Containers for programming code
Invoked at any time by writing a statement
within a different procedure to invoke the
necessary sub procedure
Place its name, and if needed, the values for its
arguments on a line within the invoking procedure
7
Functions
• Functions
Return values
Invoked by placing it in an expression or using
it as a value to display in a control
Application can use the value returned by a function
in the expression
7
Property Procedures
• Property procedure
Defines a property that is attached to an object
through dot notation
Activated by assigning or using the value of a
property
Used if the procedure will correspond to
something that could be a property
7
Creating and Using Sub
Procedures
Table 7-1
Data types in
VBA and their
corresponding
types in
Access tables
7
Creating and Using Sub
Procedures
Table 7-1
Data types in
VBA and their
corresponding
types in
Access tables
(continued)
7
Passing Arguments by
Reference and by Value
• When arguments are processed by reference:
The memory address containing the value to be
used is “referenced” by the argument
• When arguments are processed by value:
A copy of the value, instead of a reference to
the memory location containing the value, is
supplied to the procedure
7
Optional Keyword
• Optional keyword
Can be used with arguments
Specifies that the invoking procedure does not
need to provide a value for the argument
• Named arguments approach
Arguments can be placed in any order and
commas do not have to be used to represent
blank arguments
7
Nuances of Argument Use
• A procedure must be invoked using the
same number of actual arguments as formal
arguments in the sub statement, unless
optional arguments are used
The actual arguments will supply values for the
formal arguments in the order in which they are
listed, so the data types must be compatible
7
Event Procedure Arguments
• Event procedures
Special types of sub procedures located in form and
report class modules
Most do not take arguments, but some have one or
more predefined arguments
• Flags
Arguments that contain values that Access evaluates
during and after the procedure executes to determine
how to continue the application’s processing
7
Event Procedure Arguments
Table 7-2 Event procedures with single arguments
7
Event Procedure Arguments
• Intrinsic constants
Represent the legal values for arguments that
specify actions other than canceling events
True
Intrinsic constant that represents value –1
7
Event Procedure Arguments
Table 7-3 Event
procedure with
multiple or
special
arguments
7
Event Procedure Arguments
Table 7-3 Event
procedure with
multiple or
special
arguments
(continued)
7
Creating and Using Functions
• Function Procedures
Similar to sub procedures
Use formal arguments, can be declared as Public or
Private
Can contain variable and constant declaration
statements
• In a function procedure, the function is assigned a
value
Assignment is fundamental difference between sub
procedures and functions
7
Property Procedures –
Let, Set, and Get
• Property Let and Set procedures
Accept information from an invoking
procedure, but do not return a value
• Property Get procedures
Returns a value to the calling procedure
• Property Let and Get usually appear in pairs
7
Property Procedures – Let,
Set, and Get
Figure 7-2 Property
procedures for
frmIDSearchWindow
7
Declaring Variables
• Dim Statements
Define variables that will be used within
procedures
• Variables are frequently used to store
immediate results as execution of the
procedure moves from one line to another
7
Declaring Variables
Figure 7-3 Part of
txtPaymentNo_BeforeUpdate
in frmProcessPayment
7
Declaring Variables
• Private
Used in the Declarations section of a module to
declare variables that are used only within the
procedure contained by the module
• Public
Used in the Declarations section to declare
variables that can be used by any procedure in
any module
7
Declaring Variables
• Static
Used in a procedure to declare variables that
retain their value even after the procedure
finishes executing
May be declared only within procedures
7
The Variant Data Type
• Variant data type
Can contain almost any type of data, including
numbers, strings, and dates
Only data type in VBA that can store null and
empty values
• VarType built-in function
Used to determine the type of data stored in a
Variant
7
The Variant Data Type
Table 7-4
Values
returned by
VarType and
variable
testing
functions
7
The User-Defined Type
• VBA provides a way to create a user-defined
type with a Type statement
Allows you to define a complex data structure
Useful when the values of many similar
variables need to be copied to other variables
7
Single- and MultipleDimension Arrays
• Arrays
Collections of variables given a single name but
indexed by one or more values
• Array indexes begin at zero
• ReDim statement
Can change the size of the array
7
Scope, Life, and the Dim
Statement
• Where and how variables are declared
determine which other procedures can use
the variable
Scope of a variable
• How long the variable actually exists
Life of a variable
7
Scope, Life, and the Dim
Statement
Table 7-5 Variable and constant scope prefixes
7
Scope, Life, and the Dim
Statement
Figure 7-4 Procedure that facilitates the reuse of values
7
Scope, Life, and the Dim
Statement
• Any variable declared in the Declarations section
of a module maintains its stored value as long as:
The application continues to run or
The form or report remains open
• Variables declared with the Dim statement in a
procedure are available to that procedure only as
long as the procedure is running
7
Scope, Life, and the Static
Statement
• Variables declared with the Static statement
in a procedure continue to exist after the
procedure finishes
• Programmers frequently use static variables
to determine whether a newly entered value
is the same as a previous value
7
Intrinsic and Symbolic
Constants
• Constant
Named item that retains the same value
throughout the execution of a program
• Intrinsic constant
Integer values that have some particular
meaning
Makes programming easier for the developer
and facilitates program maintenance
7
Intrinsic and Symbolic
Constants
• Symbolic constant
Constant that is defined by the programmer
Retains a constant value throughout the
execution of a procedure
Similar to a variable except that its value is
specified as soon as the constant is declared
7
Intrinsic and Symbolic
Constants
Figure 7-5 Public constants in the Declarations section of the
basUtilities module
7
Controlling Processing in the
Declarations Section
• Option Compare statement
Automatically included in Declarations section
Defines the default method to use when
comparing text data
• Three types of methods used when
comparing text data:
Binary method
Comparisons will be case-sensitive
7
Controlling Processing in the
Declarations Section
• Three types of methods used when
comparing text data (continued):
Text method
Comparisons will not be case sensitive
Database method
Comparisons are based on character’s relative
positions according to the sort order specified when
the database was created or compacted
7
Controlling Processing in the
Declarations Section
• If the Declarations section of the module
containing the procedure contains an Option
Explicit statement:
Variables must be explicitly defined prior to
their use
• If it does not:
Variables do not need to be declared before they
are used
7
Chapter Summary
• VBA statements are written inside functions
procedures, sub procedures, and property
procedures
• Standard modules
Contain functions and sub procedures
Loaded into memory as soon as the application opens
• Form and Report class modules
Contain all three types of procedures
7
Chapter Summary
• Functions differ from sub procedures in two
ways
A function returns a value
A function has an associated data type
• Functions should be written in cases where
a value is being returned
7
Chapter Summary
• All procedures have arguments
• Argument values can be passed
by reference
Changes made to the value passed into the
procedure are reflected in the environment where
the procedure was originally invoked
by value
Make a copy of the actual argument value available
to the procedure
7
Chapter Summary
• Variables
Can be local or they can be public
Usually only available while the code in which
they have been declared is executing
Deleted from memory unless they have been
declared as static
May be declared as one of a number of
different data types
7
Chapter Summary
• Arrays
Collections of indexed variables with a
common name, and user defined types, which
allow programmers to create their own data
structures
• Constants
Help make the program code more
understandable