Details

---

Excel Visual Basic Applications (VBA) Training

Duration: 2 Days

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

Before sttending this course, students must be able to demonstrate the following skills:

• 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)

The course materials, lectures, and lab exercises are in English. To benefit fully from the instruction, students need an understanding of the English language and completion of the prerequisites.

Learning outcomes / Benefits

Upon completion of this program, participants should be able to:

• 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