1 / 48

Chapter 7

Chapter 7. VBA Modules, Functions, Variables, and Constants. 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

charde-alexander
Download Presentation

Chapter 7

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. Chapter 7 VBA Modules, Functions, Variables, and Constants

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. Creating and Using Sub Procedures Table 7-1 Data types in VBA and their corresponding types in Access tables

  13. Creating and Using Sub Procedures Table 7-1 Data types in VBA and their corresponding types in Access tables (continued)

  14. Passing Arguments by Reference and by Value • When arguments are processedby reference: • The memory address containing the value to be used is “referenced” by the argument • When arguments are processedby value: • A copy of the value, instead of a reference to the memory location containing the value, is supplied to the procedure

  15. 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

  16. 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

  17. 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

  18. Event Procedure Arguments Table 7-2 Event procedures with single arguments

  19. 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

  20. Event Procedure Arguments Table 7-3 Event procedure with multiple or special arguments

  21. Event Procedure Arguments Table 7-3 Event procedure with multiple or special arguments (continued)

  22. 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

  23. 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

  24. Property Procedures – Let, Set, and Get Figure 7-2 Property procedures for frmIDSearchWindow

  25. 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

  26. Declaring Variables Figure 7-3 Part of txtPaymentNo_BeforeUpdate in frmProcessPayment

  27. 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

  28. 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

  29. 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

  30. The Variant Data Type Table 7-4 Values returned by VarType and variable testing functions

  31. The User-Defined Type • VBA provides a way to create auser-defined typewith aType statement • Allows you to define a complex data structure • Useful when the values of many similar variables need to be copied to other variables

  32. Single- and Multiple- Dimension 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

  33. 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

  34. Scope, Life, and the Dim Statement Table 7-5 Variable and constant scope prefixes

  35. Scope, Life, and the Dim Statement Figure 7-4 Procedure that facilitates the reuse of values

  36. 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

  37. 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

  38. 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

  39. 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

  40. Intrinsic and Symbolic Constants Figure 7-5 Public constants in the Declarations section of the basUtilities module

  41. 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

  42. 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

  43. 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

  44. 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

  45. 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

  46. 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

  47. 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

  48. 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

More Related