Chapter 7 (Part 2): Introduction to VBA

1
Chapter 7 (Part 2) Introduction to VBA
2
Objectives

• Passing parameters and returning values
• Writing Functions
• Using Access Built-in Functions
• Using Immediate Window

3
Passing Parameters

• Both functions and subroutines can receive
  parameters
• Only functions can return values (if return type
  not specified, the function returns a variant)
• Samples and details Page 278 - 279

4
Testing Procedures/ Functions

• Procedures
• Click the event procedure
• Run Sub/Userform (F5)
• Functions
• Use the Immediate Window
• ? Function()
• E.g. ? Left("This is a test", 5)

5
DoCmd Object

• Execute Macro Actions
• Syntax DoCmd.ActionName arguments
• Example
• DoCmd.OpenReport strRepName, acPreview
• Optional Parameters
• E.g., DoCmd.OpenForm frmorders
• Named Parameters
• E.g., DoCmd.OpenForm FormName frmorders

6
Built-In Functions

• VBA Functions
• Format, UCase
• InStr, InStrRev
• Left, Right, Mid
• DatePart, DateDiff, DateAdd, MonthName
• Replace, StrRev
• Chap7Ex.mdb basBuiltIn
• Details Pages 281 - 284

7
Object Browser

• View Object Browser
• F2
• Upper Part
• Select project or Library
• Lower Left (Classes)
• Select Class
• Lower Right (Members of)
• Select property or method
• Details Pages 284 - 286

8
Constants

• Symbolic
• User-defined for readability
• E.g., Const COLOR_RED 255
• Intrinsic
• Part of MS Access
• Prefixed with ac (Access), vb (VBA), etc.
• System-defined
• True, False, Null
• Details Pages 286 - 289

9
VBE Tools/ Windows

• Right-click on name of object
• List properties/methods, Constants, parameters,
  etc. (Pages 289-293)
• VBE Windows (Pages 294 302)
• Code Window
• Project Window
• Properties Window
• Customize VBE (Tools Options)

10
Whats Next?

• Lab 2 (under Labs web page)
• The corrected mdb files are posted in this html
  page.
• Objective To know how to read VBA code and
  become familiar with VBE
• Study all code samples
• Chap7Ex.mdb
• Chap7.mdb (Pages 302 306)