1 / 26

Tutorial 11 Using and Writing Visual Basic for Applications Code

Tutorial 11 Using and Writing Visual Basic for Applications Code. Objectives. Learn about Function procedures (functions), Sub procedures (subroutines), and modules Review and modify an existing subroutine in an event procedure Create a function in a standard module

gage
Download Presentation

Tutorial 11 Using and Writing Visual Basic for Applications Code

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. Tutorial 11Using and Writing Visual Basic for Applications Code

  2. Objectives • Learn about Function procedures (functions), Sub procedures (subroutines), and modules • Review and modify an existing subroutine in an event procedure • Create a function in a standard module • Test a procedure in the immediate window • Create event procedures • Compile and test functions, subroutines, and event procedures • Hide text and change display colors New Perspectives on Microsoft Access 2010

  3. VBA Code Window New Perspectives on Microsoft Access 2010

  4. Introduction to Visual Basic for Applications • Visual Basic for Applications (VBA)is the programming language provided with Access and other Office programs • VBA has a common syntax and a set of common features for all Microsoft Office programs • Event-driven language • Object-oriented language New Perspectives on Microsoft Access 2010

  5. Events New Perspectives on Microsoft Access 2010

  6. Procedures • A Function procedure,or function, performs operations, returns a value, accepts input values, and can be used in expressions (recall that an expression is a calculation resulting in a single value) • A Sub procedure, or subroutine, performs operations and accepts input values, but does not return a value and cannot be used in expressions New Perspectives on Microsoft Access 2010

  7. Modules New Perspectives on Microsoft Access 2010

  8. Modules • Declarations section • Standard module • Public procedure • Class module • Local procedure • Private procedure New Perspectives on Microsoft Access 2010

  9. Using an Existing Procedure New Perspectives on Microsoft Access 2010

  10. Creating a New Standard Module • Click the Create tab on the Ribbon • In the Macros & Code group on the Create tab, click the Module button. A new Code window opens in the Visual Basic window New Perspectives on Microsoft Access 2010

  11. Creating a Function • Each function begins with a Function statement and ends with an End Function statement • Parameter • Argument New Perspectives on Microsoft Access 2010

  12. Testing a Procedure in the Immediate Window • In the Code window, click View on the menu bar, and then click Immediate Window to open the Immediate window • Type a question mark (?), the procedure name, and the procedure’s arguments in parentheses. If the argument contains a string of characters, enclose the argument in quotation marks • Press the Enter key and verify the displayed answer New Perspectives on Microsoft Access 2010

  13. Testing a Procedure in the Immediate Window New Perspectives on Microsoft Access 2010

  14. Event Procedure New Perspectives on Microsoft Access 2010

  15. Creating an Event Procedure New Perspectives on Microsoft Access 2010

  16. Designing the Event Procedure New Perspectives on Microsoft Access 2010

  17. Adding an Event Procedure to a Form or Report • Open the form or report in Design view, select the control whose event property you want to set, open the property sheet for the control, and then click the Event tab in the property sheet • Click the event property box, click its Build button, click Code Builder in the Choose Builder dialog box, and then click the OK button • Enter the subroutine statements in the Code window • Compile the procedure, fix any statement errors, and then save the event procedure New Perspectives on Microsoft Access 2010

  18. Adding an Event Procedure to a Form or Report New Perspectives on Microsoft Access 2010

  19. Compiling Modules • The process of translating modules from VBA to a form your computer understands is called compilation; you say that you compile the module when you translate it • When you run a procedure for the first time, Access compiles it for you automatically and opens a dialog box only when it finds syntax errors in the procedure New Perspectives on Microsoft Access 2010

  20. Adding a Second Procedure to a Class Module New Perspectives on Microsoft Access 2010

  21. Adding a Second Procedure to a Class Module New Perspectives on Microsoft Access 2010

  22. Changing the Case of a Field Value • StrConv function • wvProperCase constant • vbUpperCase constant • vbLowerCase constant New Perspectives on Microsoft Access 2010

  23. Hiding Controls and Changing a Control’s Color New Perspectives on Microsoft Access 2010

  24. Hiding Controls and Changing a Control’s Color New Perspectives on Microsoft Access 2010

  25. Helpful Hints • None New Perspectives on Microsoft Access 2010

  26. Additional Examples • None New Perspectives on Microsoft Access 2010

More Related