Visual Basic for Applications (VBA) is a version of Microsoft Visual Basic that is used with the Microsoft Office Applications, most notably Access and Excel. The course is designed to teach the students How to:
- Develop customised solutions using VBA
- Apply the object models to program effectively in the Microsoft Office System
- Take full advantage of extensive built-in VBA functionality
- Troubleshoot code using VBA's debugging tools
- Secure your code
Introduction
- Course Overview
- Macros and VBA - A Demonstration
- Using Excel Online Help
- Using Visual Basic Online Help
- Locating Information Across Help Files
Macros - the Quick Solution
- Overview
- Macro Virus Protection
- Recording a Macro
- Running a Macro
- Reviewing and Organizing Recorded Code
- Changing a Macros Functionality
- Controlling Access to Macros
- Simplifying Access to Macros
- Summary
The VBA Programming Language
- Overview
- Objects and Collections
- Methods; Properties and Events
- Procedures and Functions
- Executable Statements
- Assignment Statements
- Declaration Statements
- Variables and Constants
- Scope
- Lifetime and Data Type
- Summary
The VBA Development Environment
- Overview
- Windows
- Command Bars
- The Code Window
- Margin Indicator Bar
- Code Interpretation
- Type and Status Formatting
- Entering Keywords
- Syntax Assistance
- The Project Explorer
- The Properties Window
- The Object Browser
- Using the Object Browser
- Summary
Development and Troubleshooting
- Overview
- Running Code and Debug Printing
- ‘Step Into’, Break Mode and the Call Stack
- ‘Step Over’, ‘Step Out’ and ‘Run to Cursor’
- Breakpoints
- Variable Values and the Data Tips Window
- The Quick Watch Dialog Box and Watch Window
- The Locals Window
- Tracking Object Variables
- Application Modes
- Exploring Application Modes
- Design Mode & Running Mode Demonstrations
- Run Time Editing and Compiling
- Summary
Using Functions
- Overview
- Using User Defined Functions
- Changing Function Categories
- Creating New Function Categories
- Formulae and the Worksheet Function Object
- VBA and Excel Function Differences
- Summary
Code Production and Management
- Overview
- Automated Placement of Statements
- Accessing Code
- Using Project References
- Saving Code
- Password Protection
- Summary
Cell Referencing
- Overview
- The Range Property and Selecting Cells
- Specifying Multiple Cell Ranges
- Qualifying Ranges Returned and Offsets
- Other Range Returning Properties
- Labels and Names
- Ranges in Other Workbooks
- Shorthand Notation and 3-D Ranges
- Summary
Control Structures
- Overview
- With Control Structure
- Then...Else Control Structure
- Select Case Control Structure
- For...Next Loops
- For Each... Next Loops
- Do While...Loop and Do Until...Loop
- Do...Loop While and Do...Loop Until
- Summary
Interacting With Users
- Overview
- The Message Box
- The Message Box Constants
- The Input Box
- Object Positioning
- Quiz Example
- Protecting Users
- Summary