Excel Visual Basic Applications (VBA) Training

Introduction

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