Centre for Continuing Education

Microsoft Excel VBA Course: A Masterclass

Microsoft Visual Basic. Master programming for Windows.

COVID-19 update: arrangement of our courses

We are now delivering courses online and in-person. Please check the delivery format of each class before enrolling.

Please note that course materials (excluding prescribed texts) are shared electronically within 48 hours of course commencement. Printing is not available.


This three-day advanced course shows you how to use a combination of the Microsoft Excel Object Model and VBA to improve your productivity, adding power and flexibility to spreadsheets by creating user-defined functions and macros. Basic knowledge of Microsoft Excel is required, and you should strongly consider completing the one-day Microsoft Excel VBA Course: An Introduction prior to undertaking this course. All technical terms and concepts will be explained simply.


By the end of this course, you should be able to:

  • create, read and improve recorded macro code
  • explore the pattern of Microsoft Excel object model (types, properties and methods) and manipule it with VBA
  • create a custom form complete with controls and event procedures
  • create safer code using VBA’s procedure and variable/object/identifier scopes
  • divide and conquer complex logic with the implementation of “helper” procedures
  • learn more repetition structures in VBA and the circumstances in which to apply them and nest repetition structures
  • discuss a variety of VBA functions
  • create procedures that execute automatically in response to user manipulation of Excel (Event Procedures)
  • write a variety of error handling routines and complete your proficiency in debugging.


This course will provide a brief recap of the course material covered in Microsoft Excel VBA: An Introduction and then cover the following topics:

Complete debugging

  • Printing to the debug window and using it to interact with your variable values
  • The Locals and Watch windows, and using them interactively
  • Editing how watched variables/identifiers are reported on
  • Changing the executable line


  • Writing algorithms that affect the Excel Object Model
  • Choosing procedure types and the “rules” for choosing the correct procedure type
  • Return types of functions and parameter types
  • Scope of Private vs Public Procedures
  • Hiding a Public Sub from the Excel interface
  • Multiple parameters in procedures
  • Passing Variables to other procedures by reference or by value and when to use those techniques

Primitive/value data types

  • Implicit variable declaration vs enforcing explicit declaration of variables in your code with Option Explicit
  • Working with the VBA data type Variant and Date
  • Functions to convert one type to another type
  • Implicit Data Type Conversion in VBA
  • VBA Functions to check the data type of an identifier
  • The Like Operator
  • Guidelines for Module Level and Global Level Variables
  • Problems with the VBA Input Box
  • The Message Box as a Statement and Function
  • More on Enumerations and Constants (we can’t ignore them, they are everywhere)

Reference types and the VBA Excel Object Model

  • The differences between Value/Primitive Types and Reference Types
  • The VBA Set Keyword
  • Classes, Objects and Variables vs Types and Identifiers
  • The purpose and structure of all object-oriented programming languages
  • Using existing identifiers methods and properties
  • Declaring your own identifiers of types in the object model
  • The Range Property and selecting Ranges
  • Stopping Screen Flicker produced by recorded Macros
  • Collection Object common properties and methods
  • The Add method and the Active object
  • Working with the Workbooks, Worksheets and Cells Collections
  • Methods that Return Objects
  • More Constants and Enumerations in the Excel Object Model
  • The Excel Input Box and its advantages
  • When to use Excel interface functions in VBA code

Repetition (Iteration) structures

  • More repetition structures in VBA including when to nest loops
  • Endless loops and how to deal with them

Sharing code

  • Referencing (using) libraries of code exposed by other systems (Word, Outlook, ADO etc.)
  • Referencing (using) procedures you have written in other Workbooks

Error handling

  • A complete guide to error handling in VBA (telling code what to do if it breaks)
  • Securing your project


  • Building a form with controls including textboxes, dropdown lists, option buttons and command buttons
  • Making a form interact with spreadsheets through the setting of properties and through code
  • Truly automating Excel with Event Procedures

Intended audience

Designed for users who wish to learn how to use the inbuilt programming language in Microsoft Excel to enhance their worksheets and automate processes. Please note, this course has been designed as a follow-on course from the one-day Microsoft Excel VBA Course: An Introduction.

Furthermore, you should have basic knowledge Microsoft Excel and programming principles and practice. If you are not a touch typist, you will be asked to write your answers in a book provided. Please bring a pen.

Delivery style

Modes of delivery

  • Face-to-face, presenter-taught training in a computer lab (Windows environment)
  • Online training via the platform Zoom

Face-to-face classes

These classes run in a computer lab and you do not need to bring your own device.

Please bring a USB flash drive to class if you would like to make a copy of your work or any relevant class materials. Alternatively, you can save these to a cloud storage space or email them to your personal email address.

Online classes

If you are attending an online class, you will need your own device with Microsoft Excel software installed.


Course materials are provided electronically.


  • $50 repeat class - Conditions apply
  • Expert trainer
  • Small class size
  • CCE Statement of Completion

Apply for the IT repeat discount.

Microsoft Excel VBA Course: A Masterclass

<p>{block name:“Course Tagline - Microsoft Visual Basic”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>{block name:“Course Heading -

Microsoft Excel VBA Course: A Masterclass

<p>{block name:“Course Tagline - Microsoft Visual Basic”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>{block name:“Course Heading -


What others say.

  • The tutor was fantastic. Kept things fresh, instead of some people who can be quite monotone. Thanks again!
  • The most engaging lecturer I have ever been taught by. An outstanding professional academic.
  • The tutor delivered the content simply and in a way that was easy to grasp the great complexities of VBA. Thank you. Highly recommend.
We acknowledge the tradition of custodianship and law of the Country on which the University of Sydney campuses stand. We pay our respects to those who have cared and continue to care for the Country.