Introduction to Microsoft Excel VBA

NTUC LearningHub


Course introduction

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



Course Benefits


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



Course 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



Target Audience


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



Available Course Sessions


Please click here to stay updated on upcoming sessions.



Trainer Profile


NTUC Learninghub Pte Ltd (Executive Arm of NTUC LearningHub)

NTUC LearningHub® employs industry experienced and vendor certified instructors. These instructors are required to achieve sufficient qualifications before leading a class. In addition, we work with partners to deliver the latest course materials and computer facilities so that students can learn critical skills and prepare for the examinations.



 

Top