Microsoft Excel 2003: Visual Basic for Applications (VBA) Programming (4 day)

Scheduled Dates (sort by: location | date)

Prerequisites

Proficient knowledge of Microsoft Excel. Knowledge of PivotTables helpful but not required.

Course Outline

Getting Started

• Introducing Visual Basic for Applications
• Recording a Macro
• Running a Macro
• Editing a Macro in the Visual Basic Editor
• Understanding the Development Environment
• Using Visual Basic Help
• Closing the Visual Basic Editor

Working with Procedures and Functions

• Understanding Modules
• Creating a Standard Module
• Understanding Procedures
• Creating a Sub Procedure
• Calling Procedures
• Using the Immediate Window to Call Procedures
• Creating a Function Procedure
• Naming Procedures
• Working with the Code Editor

Understanding Objects

• Navigating the Excel Object Hierarchy
• Understanding Collections
• Using the Object Browser
• Working with Properties
• Using the With Statement
• Working with Methods
• Creating an Event Procedure

Using Expressions, Variables, and Intrinsic Functions

• Understanding Expressions and Statements
• Declaring Variables
• Understanding Data Types
• Working with Variable Scope
• Using Intrinsic Functions
• Understanding Constants
• Using Intrinsic Constants
• Using Message Boxes
• Using Input Boxes
• Declaring and Using Object Variables

Controlling Program Execution

• Understanding Control-of-Flow Structures
• Working with Boolean Expressions
• Using the If...End If Decision Structures
• Using the Select Case...End Select Structure
• Using the Do...Loop Structure
• Using the For...To...Next Structure
• Using the For Each...Next Structure
• Guidelines for use of Control-of-Flow Structures

Working with Forms and Controls

• Understanding UserForms
• Using the Toolbox
• Working with UserForm Properties, Events, and Methods
• Understanding Controls
• Setting Control Properties in the Properties Window
• Working with the Label Control
• Working with the Text Box Control
• Working with the Command Button Control
• Working with the Combo Box Control
• Working with the Frame Control
• Working with Option Button Controls
• Working with Control Appearance
• Setting the Tab Order
• Populating a Control
• Adding Code to Controls
• Launching a Form in Code

Working with the PivotTable Object

• Understanding PivotTables
• Creating a PivotTable
• Working with the PivotTableWizard Method
• Working with PivotFields
• Assigning a Procedure to a Custom Toolbar

Debugging Code

• Understanding Errors
• Using Debugging Tools
• Setting Breakpoints
• Stepping through Code
• Using Break Mode during Run mode
• Determining the Value of Expressions

Handling Errors

• Understanding Error Handling
• Understanding VBA's Error Trapping Options
• Trapping Errors with the On Error Statement
• Understanding the Err Object
• Writing an Error-Handling Routine
• Working with Inline Error Handling

Microsoft Excel 2003: Visual Basic for Applications (VBA) Programming (4 day) / MEX03-V4