1 / 40

Chapter 4

Chapter 4. The Fundamentals of VBA, Macros, and Command Bars. Chapter Objectives. Specify the fundamental structure of a VBA procedure Open forms and reports with VBA, macros, and command bars Create statements that declare variables and constants

iain
Download Presentation

Chapter 4

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 4 The Fundamentals of VBA, Macros, and Command Bars

  2. Chapter Objectives • Specify the fundamental structure of a VBA procedure • Open forms and reports with VBA, macros, and command bars • Create statements that declare variables and constants • String together WHERE conditions used in the process of opening forms and reports

  3. Chapter Objectives • Write code that uses assignment and If…Then…Else statements • Use the VBA debugging facility • Create custom menus and toolbars

  4. Introduction to VBA • Visual Basic for Applications (VBA) • Programming language used in Access to develop complex applications • Create procedures that open forms and reports in response to a user’s actions • Calculate complex equations and validate field values before storing records

  5. Introduction to VBA • Most programming languages utilize procedures that are similar to recipes Figure 4-1 Recipe for strawberry banana cooler

  6. Introduction to VBA • Dim statements (or dimension statements) • Declare variables • Variables • Named locations in memory that are used to store data of the types declared in the Dim statement

  7. Functions and Sub Procedures • Event procedure • Code inside an event procedure executes when the user or system performs an action Figure 4-2 cmdFilter_Click() event procedure

  8. Functions and Sub Procedures • Other types of procedures includefunctionsandgeneral sub procedures • Not tied to one particular event; programmer specifies when the procedure executes • Primary distinction between them is that a function usually returns a value and a sub procedure usually does not return a value • Built-in functions • Functions provided by Access

  9. OpenForm and OpenReport • OpenForm and OpenReport • Instructions that are included in a procedure and that cause Access to open forms and reports, respectively • In VBA, OpenForm, OpenReport, and almost all other instructions are called methods • Named arguments • Advantage of this approach is that the string of commas is not necessary and a specific order is not required

  10. OpenForm and OpenReport • Continuation character • Space followed by an underscore (_) • Used to indicate that the instruction continues on the next line • Commonly used when an entire line cannot be seen in the VBA window • Intrinsic constant • Keyword that represents some particular value (usually an integer)

  11. Controls • Value • Usually a number or a string • Current value of a text box is displayed on a form, report, or data access page • Current value of a combo box refers to the value of the bound column • Bang notation • Commonly used notation

  12. Variables • Variables • Named locations in the computer’s memory • Key ingredients in a VBA procedure because they store values that are needed to complete the steps in the procedure • Variables are usually declared before they are used • Variable declarations can become complex

  13. Assignment Statements • Variables are useful only when they contain values • Assignment statements • Instructions that tell Access to place a value in a particular location • Once a variable has a value, it can be used in any expression or placed in any argument that requires a value that is consistent with the variable’s data type

  14. Basic wherecondition Arguments • wherecondition arguments • Used to add additional criteria to a record source before the data are displayed or returned Figure 4-3 Selecting Chemical Processing from the combo box

  15. Basic wherecondition Arguments Figure 4-4 Query equivalent to adding a wherecondition to the record source of frmCompanies

  16. whereconditionsThat Do More • If data in a form must obey more than one new criterion, theANDoperator is strung into the wherecondition • If data must obey at least one criterion, theORoperator is used • Domain aggregate functions • Return values or statistical information from the records in a table or query

  17. Conditional Execution • Often blocks of code need to be executed under some some conditions but not executed under others • IF…Then…Else statement in VBA • Supports conditional execution • Comments • Used to document the code • Not executed as a part of the steps necessary to produce the procedure’s goals

  18. The Visual Basic Editor • Visual Basic Editor (VBE) • Allows you to quickly access modules, procedures, possible intrinsic constants, and other objects that could be used in a VBA procedure • Has many tools that help the programmer create syntactically correct statements • Contains debugging tools that help find and fix the errors that will inevitably occur in the process of developing the application

  19. The Visual Basic Editor Figure 4-6 Visual Basic Editor

  20. The Visual Basic Editor • Object Browser • Additional source of help • Displays all objects recognized by VBA, Access, the database engine, and any other application with which you are working

  21. The Visual Basic Editor Figure 4-7 Object Browser

  22. Debugging Tools • Compilation errors • Occur before the code is run • Runtime errors • Cause a running procedure to abort • Logic errors • Do not abort execution, but rather produce incorrect results

  23. Immediate Window • Immediate window • Holds commands that will be executed as soon as the programmer presses the Enter key • Can also display values that are in a form, but that are not referenced in the aborted procedure

  24. Locals Window • Locals window • Once a procedure aborts, used to display information about all variables, forms, reports, or controls used in the aborted procedure • Can also be displayed through the Debug toolbar

  25. Breakpoint • The line of code that Access believes to have caused the error is not always the line of code that actually caused the error • Frequently, it is useful to step through code, line-by-line, until the error occurs • Beneficial when certain lines of code are executing (or not executing) unexpectedly • Setting a breakpoint suspends the execution of a program

  26. Debug Toolbar • Buttons on the Debug toolbar: • Step Into causes the highlighted line to execute and moves the insertion point to the next line that should execute, even if it is in another procedure • Step Over causes the highlighted line to execute • If this line invokes another procedure, that procedure executes in its entirety, and then processing is suspended at the next executable line

  27. Debug Toolbar • Buttons on the Debug toolbar (continued): • Step Out causes the entire procedure to finish executing • Control is suspended again in the procedure that invoked the currently highlighted procedure • Continue causes the entire procedure to run until it is finished, aborts, or encounters another breakpoint

  28. Debug Toolbar • Buttons on the Debug toolbar (continued): • Break causes a running procedure to stop • Reset causes the program, even in its suspended state, to stop running

  29. Macros • Macros are a sequence of actions that automates a task Figure 4-9 Macro equivalent to cmdFilter_Click()

  30. Macro Groups • A macro groups refers to a macro that contains other macros Figure 4-10 mcrExits

  31. Condition Column • Condition column • Similar to a VBA If…Then…Else statement • When the expression in the Condition column evaluates to True: • The action in the Action column executes • The expression placed in the Condition column is identical to the If…Then…Else condition used in VBA

  32. Action Column • The action selected from the combo box located in the Action column tells Access to perform some task • As with VBA methods, most actions require the use of an action argument

  33. Command Bars • Command Bars • Collective name given to menu bars, toolbars, and shortcut menus • Common in many Windows-based applications • Controls can open forms, print reports, move the cursor to a new record, delete records, and perform many other operations

  34. Strategies and Tools for Developing Menu Bars and Toolbars • Rules of good menu bar and toolbar design: • Adhere to industry norms • Develop consistent command bars • Determine common features for all menu bars • Use standard Access menu controls whenever possible

  35. Strategies and Tools for Developing Menu Bars and Toolbars Table 4-1 Properties of menu bar and toolbar controls

  36. Wizards and the Switchboard Manager • Access wizards • Can help develop very simple procedures automatically • Switchboard Manager Wizard • Found under the Tools, Database Utilities menu • Creates a form with buttons that open forms, reports, tables, queries, and macros

  37. Chapter Summary • Access supports both macros and VBA • Although macros are easier to use, experienced Access developers prefer to work with VBA because it is a more capable language

  38. Chapter Summary • VBA procedures and macros require a name, objects to work with, and a set of instructions that cause actions to occur • The name of a VBA procedure is specified in a sub or function statement • Both macros and procedures can refer to values of controls on a form or report • VBA procedures can also declare variables that can be used within the context of the procedure

  39. Chapter Summary • Many macros and VBA procedures use OpenForm and OpenReport actions or methods • These two instructions use a number of arguments • The wherecondition argument restricts data displayed in the form or report to those records that meet the specified criteria

  40. Chapter Summary • The Visual Basic Editor (VBE) • Facilitates the development of VBA procedures • Debugger allows the developer to create breakpoints • Can watch execution of VBA code on line-by-line basis • An access developer must know how to create custom command bars

More Related