Microsoft Excel. Unlock insights and tell the story in your data.

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 all technical terms and concepts will be explained simply.

Outcomes

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.

Content

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

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. 

Prerequisites

It is recommended that you are comfortable with the learning outcomes equivalent to our Microsoft Excel VBA Course: Introduction. You should also have basic knowledge of Microsoft Excel and programming principles and practice.

Delivery modes

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

Delivery style

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. Please bring a pen.

Online classes

You will need your own device with Microsoft Excel software installed.

Materials

Course materials are distributed electronically using Dropbox.