Download our guide to end user applications

VBA for Microsoft Excel (VBAE)

Length 2 days
Price NZD 1390 exc GST

Course overview

View dates &
book now

Why study this course

This course aims to cover in detail the Visual Basic for Applications (VBA) programming language, and in particular using it with Microsoft Excel.

VBA is effective and efficient for automating repetitive tasks, prompting user action, interacting between Office applications, and finding alternative solutions to various problems.

You'll learn how to program in VBA in order to customise your work environment in Excel. You'll learn to understand programming terminology such as modules, procedures, variables, and constants. You'll learn form design, control structures, and how to debug and test your VBA application before using it in the work environment.

Request Course Information

What you’ll learn

After completing this course, students will be able to:

Understand Templates and Macros
Look at the Visual Basic editor
Record a Macro
Design Forms
Write code
Understand Procedures
Use Variables and Constants
Understand Properties, Methods, Events, and Objects
Understand the Range Object (in Excel)
Use control structures
Use debugging and error-trapping

Microsoft Apps at Lumify Work

Lumify Work has been delivering effective training across all Microsoft products for over 30 years. We are proud to be both Australia's and New Zealand’s first Microsoft Gold Learning Solutions Partner and the winner of the Microsoft MCT Superstars Award for FY24, which formally recognises us as having the highest quality Microsoft Certified Trainers (MCTs) in ANZ. Join more than 5,000 students who attend our quality Microsoft courses every year.

View all Microsoft Apps at Lumify Work courses

Who is the course for?

This course is intended for those wanting to utilise VBA to enhance their use of Microsoft Excel.

Course subjects

Understanding Templates and Macros

Workbook projects
Template projects
Using the Personal.xls in Excel
The project explorer
The code window
The toolbox
The properties window
Looking at the code created by the recorder

Controls - Command Buttons, Textboxes, and Labels

Setting control properties
Assigning code to respond to controls and click events
Creating an event procedure

How Code is Stored

Modules
Forms
Using comments

Writing Code That is Easy to Read and Navigate

Sub procedures
Function procedures
Public procedures
Private procedure

Data Types

Declaring a variable or constant
Using built in constants

Understanding Properties, Methods, Events, and Objects

Using With…End With to set multiple property values for a single object

Understanding the Range Object (in Excel)

Using cell addresses as range references
Relative referencing
Using the Offset property

Control Structures

Decision structures
- If... Then
- If…Then…Else
- If…Then... Elseif
Select Case
Looping Structures
- Do…Loop
- Do While…Loop
- Do Until…Loop
- Do…Loop While
- Do…Loop Until
- For…Next
- For Each…Next

How to Handle Errors

Designing an error handler
Using Break Mode
Using the Debugging window
Avoiding bugs

Prerequisites

This course requires an advanced knowledge of Microsoft Excel. We recommend attending our Excel - Advanced course prior to this one, or that you possess the equivalent knowledge and skills.

Terms & Conditions

The supply of this course by Lumify Work is governed by the booking terms and conditions. Please read the terms and conditions carefully before enrolling in this course, as enrolment in the course is conditional on acceptance of these terms and conditions.