16 Corporate Woods Boulevard | Albany, New York 12211 | Tel: 518.437.1600

Excel 2013/2016: Programming with VBA

Available Dates: Jan 24-26, Feb 5-7, March 28-30, April 9-11, May 16-18, June 25-27
Class Length: 3 day
Cost: $1,295

Class Outline:

Description:
VBA stands for Visual Basic for Applications. It's a programming language that enables you to control just about everything in Excel. You'll learn how to create Macros that can be run from things like a button on a spreadsheet, the Excel Ribbon - in fact, lots of places. Learning Excel VBA will enable you to do a lot more with the software than you can via the normal spreadsheet view.

Course Outline:
Unit 1: Getting Started
Topic A: Introducing Visual Basic for Applications
Topic B: Displaying the Developer Tab in the Ribbon
Topic C: Recording a Macro
Topic D: Saving a Macro-Enabled Workbook
Topic E: Running a Macro
Topic F: Editing a Macro in the Visual Basic Editor
Topic G: Understanding the Development Environment
Topic H: Using Visual Basic Help
Topic I: Closing the Visual Basic Editor
Topic J: Understanding Macro Security

Unit 2: Working with Procedures and Functions
Topic A: Understanding Modules
Topic B: Creating a Standard Module
Topic C: Understanding Procedures
Topic D: Creating a Sub Procedure
Topic E: Calling Procedures
Topic F: Using the Immediate Window to Call Procedures
Topic G: Creating a Function Procedure
Topic H: Naming Procedures
Topic I: Working with the Code Editor

Unit 3: Understanding Objects
Topic A: Understanding Objects
Topic B: Navigating the Excel Object Hierarchy
Topic C: Understanding Collections
Topic D: Using the Object Browser
Topic E: Working with Properties
Topic F: Using the With Statement
Topic G: Working with Methods
Topic H: Creating an Event Procedure

Unit 4: Using Expressions, Variables, and Intrinsic Functions
Topic A: Understanding Expressions and Statements
Topic B: Declaring Variables
Topic C: Understanding Data Types
Topic D: Working with Variable Scope
Topic E: Using Intrinsic Functions
Topic F: Understanding Constants
Topic G: Using Intrinsic Constants
Topic H: Using Message Boxes
Topic I: Using Input Boxes
Topic J: Declaring and Using Object Variables

Unit 5: Controlling Program Execution
Topic A: Understanding Control-of-Flow Structures
Topic B: Working with Boolean Expressions
Topic C: Using the If...End If Decision Structures
Topic D: Using the Select Case...End Select Structure
Topic E: Using the Do...Loop Structure
Topic F: Using the For...To...Next Structure
Topic G: Using the For Each...Next Structure
Topic H: Guidelines for use of Control-of-Flow Structures

Unit 6: Working with Forms and Controls
Topic A: Understanding UserForms
Topic B: Using the Toolbox
Topic C: Working with UserForm Properties, Events, and Methods
Topic D: Understanding Controls
Topic E: Setting Control Properties in the Properties Window
Topic F: Working with the Label Control
Topic G: Working with the Text Box Control
Topic H: Working with the Command Button Control
Topic I: Working with the Combo Box Control
Topic J: Working with the Frame Control
Topic K: Working with Option Button Controls
Topic L: Working with Control Appearance
Topic M: Setting the Tab Order
Topic N: Populating a Control
Topic O: Adding Code to Controls
Topic P: Launching a Form in Code

Unit 7: Working with the PivotTable Object
Topic A: Understanding PivotTables
Topic B: Creating a PivotTable Using Worksheet Data
Topic C: Working with the PivotTable Objects
Topic D: Working with the PivotFields Collection
Topic E: Assigning a Macro to the Quick Access Toolbar

Unit 8: Debugging Code
Topic A: Understanding Errors
Topic B: Using Debugging Tools
Topic C: Setting Breakpoints
Topic D: Stepping through Code
Topic E: Using Break Mode during Run mode
Topic F: Determining the Value of Expressions

Unit 9: Handling Errors
Topic A: Understanding Error Handling
Topic B: Understanding VBA's Error Trapping Options
Topic C: Trapping Errors with the On Error Statement
Topic D: Understanding the Err Object
Topic E: Writing an Error-Handling Routine
Topic F: Working with Inline Error Handling