Microsoft Excel Course Titles

Excel Visual Basic for Applications

Duration:  2 days | Book this course
Cost: $895.00 + GST
(This course is guaranteed to run if the minimum class size of 4 is met.)

Course Description

This course aims to cover in detail the Visual Basic for Applications programming language. You will 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

Prerequisites
This course requires that you have an advanced knowledge of Excel . Ideally you should have completed the Excel Advanced Features course or have the equivalent experience.

Who Should Take This Course
Microsoft Excel users who are familiar with the advanced level functions and want to learn how to use the software's high end-user features.

Course Objectives

Course Objectives

Upon completing this course, you will have learned how to:

  1. Understanding Templates and Macros
  2. Looking at the Visual Basic Editor
  3. Recording a macro
  4. Designing Forms
  5. Learning to write code
  6. Understanding Procedures
  7. Using Variables and constants
  8. Understanding Properties, Methods, Events and Objects
  9. Understanding the Range Object (in Excel)
  10. Control structures
  11. Debugging and Error-trapping

Course Outline

Course Outline


Understanding Templates and Macros

  • Workbook projects
  • Template projects
  • Using the Personal.xls in Excel

Looking at the Visual Basic Editor

  • The project explorer
  • The code window
  • The toolbox
  • The properties window

Recording a macro

  • Looking at the code created by the recorder

Designing Forms

  • Controls - command buttons, text boxes and labels
  • Setting control properties
  • Assigning code to respond to controls and click events
  • Creating an event procedure

Learning to write code

  • How code is stored
  • Modules
  • Forms
  • Using comments
  • Writing code that is easy to read and navigate

Understanding Procedures

  • Sub procedures
  • Function procedures
  • Public procedures
  • Private procedures

Using Variables and Constants

  • 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
  • Using Range names
  • 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

Debugging and Error-trapping

  • How to handle errors
  • Designing an error handler
  • Using Break Mode
  • Using the Debugging window
  • Avoiding bugs