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

Course Overview

NTUC LearningHub Course Code: MSEVBA

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