End User Applications Category Banner Image

VBA for Microsoft Excel (VBAE)

  • Length 2 days
  • Price  NZD 1390 exc GST
Course overview
View dates &
book now
Course locations >>

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 Solutions Partner - Cloud - Training Services Logo

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.


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.


Request Course Information

Awaiting course schedule

If you would like to receive a notification when this course becomes available, enter your details below.

Personalise your schedule with Lumify USchedule

Interested in a course that we have not yet scheduled? Get in touch, and ask for your preferred date and time. We can work together to make it happen.



Loading