Excel Visual Basic Applications (VBA) Training

Introduction

This is a 2-day course that should be conducted for a small group of between 5 to 10 participants. Participants will be exposed to the overall concept and applications of VBA in Excel throughout the entire course. The course is 20% lecture and 80% hands-on, which would provide ample opportunity for the participants to immediately apply what they have learned. Participants must have prior knowledge of Microsoft Excel.

Pre-requisites

• Ability to use a computer keyboard and a mouse
• Familiarity with the general operations of Microsoft Windows operating system
• Basic knowledge and skill in using Microsoft Excel (any version)

Learning outcomes / Benefits

• Use the macro recorder to create a variety of macros
• Create and work with UDFs (user-defined functions)
• Understand the Excel object model and VBA concepts
• Work with the three main components of the VBA Editor window
• Create procedures
• Write code to manipulate Excel objects
• Create and use variables
• Create a custom form complete with controls and event procedures
• Use a range of common programming techniques
• Code to drive a user form

Key content

Module 1: Overview

Module 2: Recorded macros

• Understanding Excel macros
• Setting macro security
• Run a recorded macro
• Record your own macro
• Saving a file with a macro in it
• Edit / view a macro
• Delete a macro
• Assigning your macro to the ribbon
• Assigning your macro a keyboard shortcut

Module 3: The VBA editor

• Opening / closing the editor
• Standard & edit toolbar
• The Project Explorer
• The Properties Window
• The Immediate Window
• The Watch Window
• The Code Window
• Running code from the editor
• Stepping through code
• Setting break points

Module 4: Procedures

• Understanding procedures
• Create a new procedure
• Using the edit toolbar
• Indenting code
• Commenting statements
• Bookmarking in procedures

Module 5: Variables

• Understanding variables
• Implicit / explicit variables
• Data types
• Creating / using variables
• The scope of variables

Module 6: Excel objects

• The Range object

Module 7: Programming techniques

• The MsgBox function
• The InputBox function
• Using If (single and multiple conditions)
• The Select Case statement
• For loops
• The Do...Loop statement

Module 8: Excel objects

• The Application object
• The Workbook object
• The Worksheets object

Module 9: Functions in VBA

• Understanding functions
• Using a VBA function in a worksheet
• Using multiple arguments
• Creating VBA functions
• Setting function data types
• Modifying a VBA function
• Using a function in VBA code

Module 10: Programming user forms

• Handling form events
• Closing a form
• Running form procedures
• Running a form from a procedure
• Initializing a form
• Transferring data from a form
• Running a form from the toolbar

Module 11: Creating custom forms

• About custom forms
• Adding text boxes to a form
• Adding label controls to a form
• Creating a custom form
• Adding a combo box control
• Adding command buttons
• Adding option button

Module 12: Mini projects

• Combine data from multiple sheets
• Automating multiple PivotTables