Visual Basic for Applications VBA

1
Tutorial 11

• Visual Basic for Applications (VBA)

2
VBA

• Event-driven, object-oriented language

3
Help and Sample Databases
Ask a question
Northwind Database
4
Organization of the Code

• Statement-one instruction
• Procedure-series of statements
• Module-collection of declarations, statements and
  procedures stored together as one named unit

5
Statements
Comment
Statement
6
Procedures
Procedure
7
Module
8
Tonights Mission
Make it work
9
Form Selector
Form Selector
10
Modify this Form
Add label and CAPS
11
Employer Form Code
12
And this Form
If Statement
Add label and highlighting
13
Code for Position Form
14
Event

• Occurs when you take an action, i.e. use the
  mouse, click a button or press a key
• Event Property

15
Procedure

• Contains a series of statements that perform an
  operation or calculate a value.
• Function Procedure -a procedure that returns a
  value and can be used in an expression, i.e.
  Now gives current date, Sum adds, etc.
• Sub Procedure -carries out an operation, but
  cant return a value

16
Method

• A procedure similar to a statement or function
  that operates on specific objects, i.e. OpenForm
  or Print

17
Two Types of Modules

• Standard Module a module in which you can place
  Sub and Function procedures that you want to be
  available to other procedures throughout the data
  base. Not associated with a particular object
• Class Module a module that can contain the
  definition for a new object. Procedures defined
  in the module become properties and methods of
  the object. Class modules can exist alone or with
  forms and reports.

18
Public Procedure in Standard Modules

• More than one object can use this procedure, i.e.
  CapAll

19
Private Procedures with Class Modules

• Event procedures in a Class Module are local or
  private
• Only the form or report for which the class
  module was created can use these

20
Functionsfrom the review assignment
Create an If statement
21
Code for Recruiter Formreview assignment
Salary field for the review assignment
Similar to the Position form in the Tutorial
22
Compiling

• The process of translating the modules from VBA
  to a computer language that the computer
  understands and can execute.
• Automatic compiling occurs when running a
  procedure for the first time
• Or you can select Debug on the Main Menu,
  Compileltdatabase namegtat any time

23
Load Event
24
Load Event Code
Curly Braces vs Parenthesis Pg AC 587
25
DoCmdexecutes the action
DoCmd Statement
26
Credit for Tutorials 10 and 11and T-10 Review

• Demonstrate Switchboards
• Overseas
• Recruiter
• Demonstrate Dialog Box forms
• Demonstrate Command buttons

Lets Check Tutorials Tonight And Reviews Next
Week
27
Homework

• Review/Prepare for Level III Test
• Complete the Final Project
• Tutorial 11 Review assignment is not required

28
Level III Test 3 Review

• Create single macros
• Create command buttons
• Attach macros to command buttons
• Create group macros
• Create a switchboard
• Attach group macros to the switchboard command
  buttons