Training Courses

search courses

VBAE: VBA For Excel

Guaranteed to Run Scheduled Fully Booked
Location
Christchurch
Wellington
Auckland
Oct 2019
22
31
Nov 2019
20
14
11
Dec 2019
18
12
9
Jan 2020
23
Feb 2020
20
20 27
10
Mar 2020
9 26
12
Apr 2020
14
30
6
May 2020
28
Jun 2020
9 18
Jul 2020
19
Aug 2020
6
Sep 2020
6 13 24
Oct 2020
25
Nov 2020
12
Dec 2020
13
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