Microsoft Excel – Visual Basic for Applications (VBA)
=====
* Course Id : WIND-VBAD
* Duration : 24 Hours
Overview
—–
* Visual Basic is an object-oriented specific Microsoft language
* In this course you will learn to apply VBA concepts on Microsoft Excel to customize Office applications
* The automation of repetitive tasks is the most common use of Microsoft VBA
* You can create custom spreadsheets, workbooks, controls, and even toolbar add-ins for users
Pre-Requisites
——-
* Participants should have the advanced knowledge of Microsoft Excel
Training Objectives
——
* Understand the Microsoft Excel object model
* Understand VBA concepts and the main features of the VBA Editor window
* Learn to create procedures in VBA
* Learn to create and use variables
* Learn to create and work with user-defined functions in VBA
* Write code to manipulate Excel objects
* Use a range of common programming techniques
* Learn to create a custom form complete with an assortment of controls
* Learn to create a code to drive a user form and create procedures that start automatically
* Write a variety of error handling routines
Course Structure
—–
* We provide more focus on hands-on in our technical courses (typically 80% hands-on/20% theory)
* Students get the capability to apply the material they learn to real-world problems
Materials Provided
—–
* PDF of slides and hands-on exercises
* Access to instance with lab environment
Software Requirements
—–
* Any current internet browser
Hardware Requirements
—–
* Processor: 1.2 GHz
* RAM: 512 MB
* Disk space: 1 GB
* Network Connection with low latency (<250ms) to Internet
Course Outline
## Daywise Course Outline
—–
## Day 1
—–
* Unit 1 : Understanding Excel VBA
* Unit 2 : Starting With Excel VBA
## Day 2
—–
* Unit 3 : Procedures
* Unit 4 : Functions in VBA
* Unit 5 : Using Excel Objects
* Unit 6 : Programming UserForms
## Day 3
—–
* Unit 7 : Automatic Startup
* Unit 8 : Error Handling
## Detailed course outline
—–
Unit 1 : Understanding Excel VBA
—–
* Programming in Microsoft Excel
* VBA Terminology
* Displaying the DEVELOPER Tab
* The VBA Editor Screen
* Opening and Closing the Editor
* Understanding Objects
* Viewing the Excel Object Model
* Using the Immediate Window
* Working With Object Collections
* Setting Property Values
* Working With Worksheets
* Using the Object Browser
* Programming With the Object
* Browser
* Accessing Help
Unit 2 : Starting With Excel VBA
—–
* Using the Project Explorer
* Working With the Properties
* Window
* Using the Work Area
* Viewing Other Panes
* Working With Toolbars
* Working With a Code Module
* Running Code From the Editor
* Setting Breakpoints in Code
* Stepping Through Code
Unit 3 : Procedures
—–
* Understanding Procedures
* Where to Write Procedures
* Passing Variables by Reference
* Passing Variables by Value
* Understanding Data Types for Variables
* Declaring Data Types
* Using Arrays
Unit 4 : Functions in VBA
—–
* Understanding Functions
* Creating User-Defined Functions
* Using a User-Defined Function in a Worksheet
* Setting Function Data Types
* Using Multiple Arguments
* Modifying a User-Defined Function
* Creating a Function Library
* Referencing a Function Library
* Importing a VBA Module
* Using a Function in VBA Code
Unit 5 : Using Excel Objects
—–
* The Application Object
* The Workbook Objects
* Program Testing With the Editor
* Using Workbook Objects
* The Worksheets Object
* Using the Worksheets Object
* The Range Object
* Using Range Objects
* Using Objects in a Procedure
Unit 6 : Programming UserForms
—–
* Handling Form Events
* Initialising a Form
* Closing a Form
* Transferring Data From a Form
* Running Form Procedures
* Creating Error Checking Procedures
* Running a Form From a Procedure
* Running a Form From the Toolbar
Unit 7 : Automatic Startup
—–
* Programming Automatic
* Procedures
* Automatically Starting a Workbook
Unit 8 : Error Handling
—–
* Understanding Error Types
* The on Error Statement
* Trapping
* Using the Resume Statement
* Creating a New Sub Routine
* Using IntelliSense
* Using the Edit Toolbar
* Commenting Statements
* Indenting Code
* Bookmarking in Procedures