Excel Programming with VBA

Getting Started

• Introducing Visual Basic for Applications
• Displaying the Developer Tab in the Ribbon
• Recording a Macro
• Saving a Macro-Enabled Workbook
• Running a Macro
• Editing a Macro in the Visual Basic Editor
• Understanding the Development Environment
• Using Visual Basic Help
• Closing the Visual Basic Editor
• Understanding Macro Security

 

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

• 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 Using Worksheet Data
• Working with the PivotTable Objects
• Working with the PivotFields Collection
• Assigning a Macro to the Quick Access 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

Participants will learn how to:

• Develop customized solutions using Visual Basic for Applications (VBA)
• Apply the object models to program effectively in the Microsoft Office System
• Take full advantage of extensive built-in VBA functionality
• Exploit ActiveX controls to create functional and familiar user interfaces
• Retrieve data from PC and server databases
• Troubleshoot code using VBA’s debugging tools
• Secure your code and protect your users from macro viruses
• Use Visual Basic to control Excel, and to automate processes to speed up and simplify their work with Excel data.

• This course is valuable for anyone who wants to automate tasks through VBA programming.
• Some experience using the products in the Microsoft Office System is assumed.
• No previous programming experience is required.