Register Now      202-747-5000

Introduction to Microsoft Excel VBA Programming

365 | 2019 | 2016
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.

Course Outline

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

 

Cost/Investment

Public Course at Softek Private Course at Your Office Private Course at Softek
$800 per student Contact Softek Contact Softek
or 3 vouchers    

Private Group Training

Private group courses are cost effective when you have a group of students who need the same course. Pricing is based on the maximum number of students anticipated per day per course. Volume discounts of 5% to 20% are available when five or more training days are scheduled. For classes conducted at your office, you are responsible for the classroom setup, including student computers, instructor station, LCD projector, and software installation. For courses conducted at Softek, we take care of classroom setup.

Customization

Softek can easily customize a training course that covers the specific topics you require and integrate examples relevant to your work environment. Course customization is billed at $125 per hour and covers time needed to meet, discuss, prepare, and incorporate any specific examples into the training program. Once we have a better idea of what you have in mind, we will be glad to provide a specific price quote for the customization. There is a 2-hour minimum fee for customization.

Why Choose Softek?

  • Live, in-person training
  • Instructors with 10+ years of experience
  • Detailed training guide
  • Certificate of completion
  • Money-back guarantee