Training Courses

search courses

VBAE: VBA For Excel

Guaranteed to Run Scheduled Fully Booked
Location
Wellington
Auckland
Christchurch
Aug 2019
22
Sep 2019
19
9
19
Oct 2019
3 31
22
Nov 2019
14
11
20
Dec 2019
12
18
Feb 2020
20
Mar 2020
9 26
Apr 2020
14
Code: VBAE
Length: 2 Days
Overview

This course aims to cover in detail the Visual Basic for Applications programming language.

Delivery format(s)
On-Site
On-Site
Classroom
Classroom
OBJECTIVES
This course aims to cover in detail the Visual Basic for Applications programming language. You will be learn how to program in VBA in order to customise your work environment in Excel. You will learn to understand programming terminology such as modules, procedures, variables and constants. You will learn form design, control structures and how to debug and test your VBA application before using it in the work environment.
PREREQUISITES

This course requires an advanced knowledge of Excel. We recommend attending our Microsoft Excel - Data Analysis and Reporting course prior to attending, or that you have the equivalent experience.

WHAT YOU’LL LEARN
  • Understanding Templates and Macros
  • Looking at the Visual Basic Editor
  • Recording a macro
  • Designing Forms
  • Learning to write code
  • Understanding Procedures
  • Using Variables and constants
  • Understanding Properties, Methods, Events and Objects
  • Understanding the Range Object (in Excel)
  • Control structures
  • Debugging and Error-trapping
COURSE OUTLINE

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

Enquiry Form