WHO SHOULD ATTEND?

This course is intended for participants who are familiar with Microsoft Office Excel.

COURSE OBJECTIVES

Upon completion of the course, participants will be able to accomplish the following:

• Understand key concepts that will be used to build custom solutions using Microsoft Excel VBA Macros

• Become familiar with basic macro techniques for recording, writing, running, and maintaining macros

• Understand how Visual Basic for Applications is incorporated, used, and modified in Microsoft Excel

• Learn how to design and lay out a user form and controls to create a customized dialog box to simplify data
  entry

• Understand how to prepare an Excel Workbook containing macros and toolbars for deployment to other
  users and machines

• Learn to customize macros with VBA source code to automate data entry tasks and enable source code
  protection

• Learn to enhance source code with variables, constants, and logical operators

• Learn to handle the three types of errors that can be generated when working with source code

• Learn to access external data by using ActiveX Data Objects

• Learn how to use Excel as an automation server to control Excel objects externally outside of the application

COURSE CONTENT

BUILDING SOLUTIONS

Course Overview; Solution Concepts; Data Entry Concepts; Macro Concepts; Programming Concepts

MACRO BASICS

Planning Macros; Recording Macros; Executing Macros; Maintaining Macros

VBA PROGRAMMING BASICS

Understanding Objects; Using the Visual Basic Editor; Understanding VBA Code; Executing VBA Code; VBA Help Reference

VBA USER FORM BASICS

Planning User Forms; Designing User Forms

DEPLOYMENT BASICS

Preparing for Deployment; Code Security; Creating Custom Toolbars; Creating Custom Macro Buttons

CUSTOMIZING MACROS

Prompting for User Input; Automating Data Entry; Recording Absolute vs. Relative References; Protecting VBA Source Code

ENHANCING VBA CODE

Writing Source Code; Variables and Constants; Writing Tips; Logical Operators; Flow Control

HANDLING ERRORS

Handling Errors; Syntax Errors; Logical Errors; Run-Time Errors

SUPPLIMENTAL - ACCESSING EXTERNAL DATA

External References; ActiveX Data Objects Concepts; Connecting to a Database; Retrieving Records from a Database

SUPPLIMENTAL - EXCEL AUTOMATION

Automation Concepts; Working with Object Models; Excel’s Object Model; Automating Excel with VBScript

Microsoft Office Excel Level 2 or equivalent knowledge.