Advanced Excel VBA

Synopsis

This course has been created for Excel VBA developers who want to learn advanced topics and use Excel VBA macros in the most efficient way. The course deals with object oriented programming, advanced Excel VBA functionality and integration with external libraries and data sources.

Objective

After attending this course, participants will be able to enhance the VBA programming skill effectively.

Course Contents

The Power of VBA: Excel with Classes
Revision of VBA

• Objects and collections
• Methods and properties
• Looping over collections
• Using object variables

Integrating Worksheet Data with Events

• Worksheet Events
• Workbook Events

Working with files

• Using FileSystemObjects
• Working with files and folders
• Using FileDialogs

Working with text files

• Text files and FileSystemObjects
• Avoiding using INPUT/OUTPUT
• Creating and opening

Class modules

• What is a class?
• Designing classes
• Creating properties and methods
• Consuming classes

The Power of Integration: Excel with External Data
Revision Of SQL Language

• Select
• Update
• Insert

Programming Data integration

• ADO versus DAO
• Referencing the right library
• Opening and using recordsets

o Retrieving
o Inserting
o Updating

Pre-requisites

Participants should have a background of advanced Excel, SQL and Excel VBA programming or have already attended Advanced Excel, SQL and Excel VBA courses at NUS Extension.