Full program description

Introduction to Microsoft Excel VBA

What's In It For Me

  • Understand the Excel object model and VBA concepts
  • Apply VBA concepts effectively

Who Should Attend

Learners who would like to write VBA codes in Microsoft Excel to automate repetitive tasks that would not be possible using Recording of Macros.

Course Duration

2 Days / 14 Hours

Course Outline

  Understanding Excel VBA

o  Programming in Microsoft Excel

o  VBA Terminology

o  Displaying the DEVELOPER Tab

o  The VBA Editor Screen

o  Opening and Closing the Editor

o  Understanding Objects

o  Viewing the Excel Object Model

o  Using the Immediate Window

o  Working With Object Collections

o  Setting Property Values

o  Working With Worksheets

o  Using the Object Browser

o  Programming With the Object

o  Browser

o  Accessing Help

  Starting With Excel VBA

o  Using the Project Explorer

o  Working With the Properties

o  Window

o  Using the Work Area

o  Viewing Other Panes

o  Working With Toolbars

o  Working With a Code Module

o  Running Code From the Editor

o  Setting Breakpoints in Code

o  Stepping Through Code

  Procedures

o  Understanding Procedures

o  Where to Write Procedures

o  Passing Variables by Reference

o  Passing Variables by Value

o  Understanding Data Types for Variables

o  Declaring Data Types

o  Using Arrays

  Functions in VBA

o  Understanding Functions

o  Creating User-Defined Functions

o  Using a User-Defined Function in a Worksheet

o  Setting Function Data Types

o  Using Multiple Arguments

o  Modifying a User-Defined Function

o  Creating a Function Library

o  Referencing a Function Library

o  Importing a VBA Module

o  Using a Function in VBA Code

  Using Excel Objects

o  The Application Object

o  The Workbook Objects

o  Program Testing With the Editor

o  Using Workbook Objects

o  The Worksheets Object

o  Using the Worksheets Object

o  The Range Object

o  Using Range Objects

o  Using Objects in a Procedure

  Programming UserForms

o  Handling Form Events

o  Initialising a Form

o  Closing a Form

o  Transferring Data From a Form

o  Running Form Procedures

o  Creating Error Checking Procedures

o  Running a Form From a Procedure

o  Running a Form From the Toolbar

  Automatic Startup

o  Programming Automatic

o Procedures

o  Automatically Starting a Workbook

  Error Handling

o  Understanding Error Types

o  The on Error Statement

o  Trapping

o  Using the Resume Statement

o  Creating a New Sub Routine

o  Using IntelliSense

o  Using the Edit Toolbar

o  Commenting Statements

o  Indenting Code

o  Bookmarking in Procedures 

 

Course Objectives

Upon course completion, participants will be able to:

  • Understand the Excel object model and VBA concepts
  • Work effectively with the main features of the VBA Editor window
  • Create procedures in VBA
  • Create and use variables
  • Create and work with user-defined functions in VBA
  • Write code to manipulate Excel objects
  • Use a range of common programming techniques
  • Create a custom form complete with an assortment of controls
  • Create code to drive a user form
  • Create procedures that start automatically
  • Write a variety of error handling routines

Pre-requisites

  • Simple typing using the keyboard
  • Navigating using the mouse
  • Managing files and creating folders
  • Working knowledge of Microsoft Excel 2013, which includes writing of Formulas and Functions

Funding Information

Union Training Assistance Programme (UTAP):

  • NTUC members enjoy UTAP* benefit of up to $250 each year when you sign up for courses with NTUC LearningHub.

* UTAP supports 50% of the course fees paid cap at $250 per year. You must be a union member throughout the course duration and at the time of claim. 

Price

  Before GSTW/ GST 
 Course Fee$560$599.20
MSEVBA