详情

Excel Visual Basic Applications (VBA) Training

Course 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 complietion 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 Contents

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 Viba 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

Undertsanding 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 UserForms

Handling Form Events
Closing a Form
Running Form Procedures
Running a Form From a Procedure
Initializing a Form
Transfering 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