Introduction to Microsoft Excel VBA Programming

2 Days, 9:00-4:00

Course Overview

This instructor-led course, through lecture and hands-on exploration, will introduce students to the Microsoft Excel Visual Basic for Applications (VBA) programming environment to develop custom solutions and automate routine tasks. This course is intended for advanced Microsoft Excel professionals. The course is generally offered as a 2-day course and can be customized when delivered for private groups to include time working through actual client VBA tasks/projects.

Course Objectives

• Understand the Excel object model and VBA concepts
• Create command procedures and use variables
• Create and work with user-defined functions
• Perform loops, controls and other common programming techniques
• Create a custom form complete with controls and event procedures
• Create procedures that start automatically
• Write a variety of error handling routines

Sample tasks that can be done in class:

• Load data from an external data source, like an accounting system.
• Iterate through every row on a sheet to perform a complicated calculation that can't be expressed through formulas.
• Combine every day excel techniques with VBA to automate the creation of formulas in different sheets and cells.

Understanding Excel VBA

• Programming in Microsoft Excel
• VBA Terminology
• Displaying the Developer Tab
• The VBA Editor Screen
• Understanding Objects
• Viewing the Excel Object Model
• Using the Immediate Window
• Working with Object Collections
• Setting Property Values
• Working with Worksheets
• Using the Object Browser
• Programming with the Object Browser

Starting with Excel VBA

• Using the Project Explorer
• Working with the Properties Window
• Using the Work Area
• Viewing Other Panes
• Working with Toolbars
• Working with a Code Module
• Running Code from the Editor
• Setting Breakpoints in the Editor
• Stepping Through Code

Procedures

• Understanding Procedures
• Where to Write Procedures
• Creating a New Sub Routine
• Using IntelliSense
• Using the Edit Toolbar
• Commenting Statements
• Indenting Code
• Bookmarking in Procedures

Using Variables

• Understanding Variables
• Creating and Using Variables
• Explicit Declarations
• The Scope of Variables
• Procedure Level Scoping
• Module Level Scoping
• Passing Variables by Reference
• Passing Variables by Value
• Data Types for Variables
• Declaring Data Types
• Using Arrays

Functions In VBA

• Understanding Functions
• Creating User-Defined Functions
• Setting Function Data Types
• Using Multiple Arguments
• Modifying User-Defined Functions
• Creating a Function Library
• Referencing a Function Library
• Importing a VBA Module
• Using a Function in VBA Code

Using Excel Objects

• The Application Object
• The Workbook Objects
• Program Testing with The Editor
• Using Workbook Objects
• The Worksheets Object
• Using the Worksheets Object
• The Range Object
• Using Range Objects
• Using Objects in a Procedure

Programming Techniques

• Using the MsgBox Function
• Using the InputBox Function and InputBox Method
• The IF Statement for Single and Multiple Conditions
• The Select Case Statement
• Using the Select Case Statement
• For Loops
• Looping with Specified Iterations
• The Do...Loop Statement
• Looping with Unknown Iterations

Creating Custom Forms

• About Custom Forms
• Creating a Custom Form
• Adding Text Boxes to a Form
• Changing Text Box Control Properties
• Adding Label Controls to a Form
• Adding a Combo Box Control
• Adding Option Buttons
• Adding Command Buttons
• Running a Custom Form

Programming UserForms

• Handling Form Events
• Initializing a Form
• Closing a Form
• Transferring Data from a Form
• Running Form Procedures
• Creating Error Checking Procedures
• Running a Form from a Procedure or Toolbar

Automatic Startup

• Programming Automatic Procedures
• Running Automatic Procedures
• Automatically Starting a Workbook

Error Handling

• Understanding Error Types
• The On Error Statement
• Creating a Simple Error Handler
• Using the Resume Statement
• Using Decision Structures in Error Handlers
• Working with Err object
• Error Handling in Forms
• Defining Custom Errors