Centre for Continuing Education

Microsoft Excel VBA Course: A Masterclass

Microsoft Visual Basic. Master programming for Windows.

Learn Microsoft Visual Basic the smart way with Microsoft Visual Basic courses at CCE, The University of Sydney.

Aims

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.

Course outcomes

Upon completion of this short course, you should be able to:

  1. Create, understand and improve recorded macro code.
  2. Gain a deeper understanding of the Microsoft Excel object model (types, properties and methods) and manipulating it with VBA.
  3. Create a custom form complete with controls and event procedures.
  4. Understand procedure and variable/object/identifier scope.
  5. Divide and conquer complex logic with the implementation of “helper” procedures.
  6. Learn more repetition structures in VBA and the circumstances in which to apply them.
  7. Understand the required features of successful nested repetition.
  8. Become familiar with a variety of VBA functions.
  9. Create a user form and manipulate it with code.
  10. Create procedures that execute automatically in response to user manipulation of Excel (Event Procedures).
  11. Write a variety of error handling routines.
  12. Complete your proficiency in debugging.

Content

This training course will provide a brief recap of the course material covered in Microsoft Excel VBA Course: 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.

Procedures

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

Forms

  • 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 and when not to.

Course delivery

This short course is delivered as presenter-taught computer-based training.

Intended Audience

This short course is 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.

Features

  • Free repeat class - Conditions apply
  • Free USB flash drive
  • Expert trainer
  • Dedicated computer for every student
  • Small class size
  • Student notes – yours to keep
  • Certificate of completion
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
If there isn't a class to suit you, please join the waiting list.