Introduction to VBA for Excel Macros and Visual Basic for Applications



This 2 day course is designed to give proficiency in the Visual Basic Editor (VBE), predominantly making use of Excel objects, basic VBA programming, event handling, control structures and debugging tools.

Aims and Objectives

On completion of this course, delegates will be able to:

  • Record and edit a macro
  • Trigger a macro during design mode
  • User control - keyboard commands, quick access toolbars or buttons, shapes and images on the worksheet
  • Use VBA and examine the Visual Basic Editor
  • Design dialog boxes and simple user forms with controls
  • Drop down boxes, command buttons, text boxes and labels
  • Use decision structures to create procedures that make decisions - use loop structures to perform repetitive tasks
  • Create an error handling routine in case things go wrong with VBA code


Introduction to Macros and VBA

  • Review of Naming Cells and Ranges
  • Review of methods for navigating Excel and creating selections
  • Over view of the Developer Tab
  • Purpose of Macros
  • Where to save Macros
  • Using the Recorder to Record a macro
  • Recording a Macro using Relative vs Absolute References
  • Personal macro workbook
  • Introducing Visual Basic for Applications
  • Running a Macro
  • Saving and Opening Files with Macros
  • Editing a Macro in the Visual Basic Editor
  • Understanding the Development Environment
  • Tips for General Typing in VBA
  • Edition and optimising recorded code
  • Removing Selection statements
  • With...End With
  • Running macros: Assigning to Quick Access Toolbar, Shapes, Pictures and Keyboard shortcuts

The VB Environment

  • The Project Window
  • Module Sheet
  • Properties Window
  • Navigating between Excel and the VB Editor
  • Immediate and Watch Window
  • Menus and Toolbars
  • Setting the VB Options


  • Program design
  • Structure of a sub routine
  • Creating a Sub routine
  • Key Words
  • Navigate a Module
  • Edit a Module
  • Commenting / Uncommenting
  • Running and Stepping code
  • Exiting Procedures
  • Calling Procedures, run sub routines together
  • Working with Module sheets - renaming, exporting, removing, inserting

Objects, Properties, Methods and Events

  • The VBA Language
  • Understanding Objects Properties, Methods and Events
  • Navigating the Object Hierarchy
  • Understanding Collections
  • Using the Object Browser
  • Working with Properties
  • Working with Methods
  • Creating an Event Procedure

Variables and Expressions

  • The role of Option Explicit
  • Variable Declaration
  • Data Types
  • Variable Scope and Lifetime
  • Public and Private
  • Understanding Constants
  • Use of VBA Constants
  • Naming Conventions

User Defined Functions

  • Creating a Function
  • Understanding Arguments

Object Variables

  • Overview of Object Variables
  • Declaring Object Variables
  • Using the SET statement

Program Flow and Control

  • Decision Structures - Branching
  • IF, Nested IF
    • If-Then-Else

Looping Structures

  • Do Loops
  • For Next Loops
  • For Each Next Loops
  • Counter Loops and Collection Loops
  • Conditional Loops

Interactive Macros

  • Using a Message Box
  • Use a Conditional Message Box
  • Use the Inputbox
  • Controlling and Answer to a Message Box

Debugging and Handling Errors

  • Understanding Errors
  • Using Debugging Tools
  • Setting Breakpoints and Using Break Mode
  • Stepping Through Code
  • Trapping Errors with the One Error Statement

Forms and Controls

  • Understanding UserForms
  • Using the Toolbox
  • Designing Forms
  • Working with a Form's Properties, Methods and Events
  • Working with Form and Control, Properties, Methods and Event
  • Working with Form Controls
  • Setting the Tab Order
  • Populating a Control
  • Adding Code to Controls

Show the next months in

Location Dates Time Price exc. VAT   Status
Contact Graham on 01243 216278 or email to book this course
Share This