Course Details

Visual Basic for Applications is the integrated programming language used in Excel. In this course we take an in-depth look at this language, and how it is applied to developing programs & automating operations in Excel.  Learning to program might seem intimidating, but with some patience and with examples in this course many users find that learning VBA makes their work easier and gives them the ability to do things in Office that they did not think were possible.  By the last day of the course, you will be able to write Excel VBA code that interacts with other Office applications such as Word, Access and Outlook.

Take a look at the course outline for specific details.   We encourage you to bring a USB to take away completed examples.

Duration:

3 Days - This course is structured as one day per week over three weeks to give you plenty of time to consolidate the information between sessions.

Audience:

This course had been designed for the non-technical user who needs to design & create applications in Excel.

Prerequisites:

Participants must have a good understanding of Excel spreadsheets in particular working with ranges.

Inclusions:

• Experienced Microsoft Certified Instructor with Excel development experience
• Comprehensive Training Manual
Course exercise files ... with completed code
Unlimited post-course online support to the level of the course
Access to VBA Snippets newsletter

Excel 2016

Introduction to VBA

  • Why use VBA if I can record macros in Excel?
  • Recoding a Simple macro
  • Reviewing the code
  • Familiarisation with the VBA environment
  • Running Code from the VB Editor window
  • Getting help on code
  • Stepping through a procedure
  • Using a Break point
  • Communicating with the User

VBA Terminology

  • Modules and procedures
  • Components of your code
  • Objects, Collections, Properties and methods
  • Using the Excel Object model

Data Types, Variables and Operators

  • Working with variables and constants
  • Using Data types
  • Working with operators and expressions
  • Implicit and Explicit Declarations
  • Variable Scope
    • Procedural, Modular or Public
  • Passing variables by Value
  • Passing variables by Reference
  • Using Arrays

Workbooks and Worksheets

  • Workbook objects and methods
  • Creating a new workbook
  • Adding and Renaming Sheets
  • Saving and Closing a Workbook

Functions

  • Mathematical functions
  • Date and time functions
  • String functions
  • Using Excel functions in VBA code

User Defined Function Procedures

  • Creating User Defined functions
  • Using user-defined functions in a worksheet
  • Declaring Multiple arguments in a function
  • Creating a Function Library

Loops

  • Do Until and Do While loops
  • Looping at least once
  • For Next Loop
  • For Each Loops with collections

Decision Structures

  • Using IF to make decisions
  • Testing for multiple conditions
  • Establishing Flow control
  • Branching
  • Call other procedures

Working with Names

  • Adding Names
  • Deleting Names
  • Creating Hidden Names
  • Checking for the Existence of a Name

Manipulating data

  • Working with the Ranges and Selections
  • Using the Cells Property to Select a Range
  • Using the Offset Property to Refer to a Range
  • Using the Columns and Rows Properties
  • Using the Union Method to Join Multiple Ranges
  • Using the IsEmpty Function
  • Using the CurrentRegion Property
  • Using the Areas Collection

File management techniques in VBA

  • Open and Save files
  • Copy, move and delete files
  • Changing folders
  • Using Excel Open and SaveAs dialog boxes in code

Custom Forms / Dialog boxes

  • Creating a user form
  • Labels, text boxes, combo boxes and list boxes
  • Setting properties for the form and controls
  • Assigning data to combo boxes and list boxes
  • Option buttons, Groups, checkboxes and buttons
  • Creating the event code for controls
  • Initialising the form
  • Closing the form
  • Using RefEdit to allow user interaction

Managing Information with VBA

  • Linking Excel to an Access database
  • Adding a Record to the Database from Excel
  • Retrieving Records from the Database
  • Updating an Existing Record

Excel 2013

Introduction to VBA

  • Why use VBA if I can record macros in Excel?
  • Recoding a Simple macro
  • Reviewing the code
  • Familiarisation with the VBA environment
  • Running Code from the VB Editor window
  • Getting help on code
  • Stepping through a procedure
  • Using a Break point
  • Communicating with the User

VBA Terminology

  • Modules and procedures
  • Components of your code
  • Objects, Collections, Properties and methods
  • Using the Excel Object model

Data Types, Variables and Operators

  • Working with variables and constants
  • Using Data types
  • Working with operators and expressions
  • Implicit and Explicit Declarations
  • Variable Scope
    • Procedural, Modular or Public
  • Passing variables by Value
  • Passing variables by Reference
  • Using Arrays

Workbooks and Worksheets

  • Workbook objects and methods
  • Creating a new workbook
  • Adding and Renaming Sheets
  • Saving and Closing a Workbook

Functions

  • Mathematical functions
  • Date and time functions
  • String functions
  • Using Excel functions in VBA code

User Defined Function Procedures

  • Creating User Defined functions
  • Using user-defined functions in a worksheet
  • Declaring Multiple arguments in a function
  • Creating a Function Library

Loops

  • Do Until and Do While loops
  • Looping at least once
  • For Next Loop
  • For Each Loops with collections

Decision Structures

  • Using IF to make decisions
  • Testing for multiple conditions
  • Establishing Flow control
  • Branching
  • Call other procedures

Working with Names

  • Adding Names
  • Deleting Names
  • Creating Hidden Names
  • Checking for the Existence of a Name

Manipulating data

  • Working with the Ranges and Selections
  • Using the Cells Property to Select a Range
  • Using the Offset Property to Refer to a Range
  • Using the Columns and Rows Properties
  • Using the Union Method to Join Multiple Ranges
  • Using the IsEmpty Function
  • Using the CurrentRegion Property
  • Using the Areas Collection

File management techniques in VBA

  • Open and Save files
  • Copy, move and delete files
  • Changing folders
  • Using Excel Open and SaveAs dialog boxes in code

Custom Forms / Dialog boxes

  • Creating a user form
  • Labels, text boxes, combo boxes and list boxes
  • Setting properties for the form and controls
  • Assigning data to combo boxes and list boxes
  • Option buttons, Groups, checkboxes and buttons
  • Creating the event code for controls
  • Initialising the form
  • Closing the form
  • Using RefEdit to allow user interaction

Managing Information with VBA

  • Linking Excel to an Access database
  • Adding a Record to the Database from Excel
  • Retrieving Records from the Database
  • Updating an Existing Record

Excel 2010

Introduction to VBA

  • Why use VBA if I can record macros in Excel?
  • Recoding a Simple macro
  • Reviewing the code
  • Familiarisation with the VBA environment
  • Running Code from the VB Editor window
  • Getting help on code
  • Stepping through a procedure
  • Using a Break point
  • Communicating with the User

VBA Terminology

  • Modules and procedures
  • Components of your code
  • Objects, Collections, Properties and methods
  • Using the Excel Object model

Data Types, Variables and Operators

  • Working with variables and constants
  • Using Data types
  • Working with operators and expressions
  • Implicit and Explicit Declarations
  • Variable Scope
    • Procedural, Modular or Public
  • Passing variables by Value
  • Passing variables by Reference
  • Using Arrays

Workbooks and Worksheets

  • Workbook objects and methods
  • Creating a new workbook
  • Adding and Renaming Sheets
  • Saving and Closing a Workbook

Functions

  • Mathematical functions
  • Date and time functions
  • String functions
  • Using Excel functions in VBA code

User Defined Function Procedures

  • Creating User Defined functions
  • Using user-defined functions in a worksheet
  • Declaring Multiple arguments in a function
  • Creating a Function Library

Loops

  • Do Until and Do While loops
  • Looping at least once
  • For Next Loop
  • For Each Loops with collections

Decision Structures

  • Using IF to make decisions
  • Testing for multiple conditions
  • Establishing Flow control
  • Branching
  • Call other procedures

Working with Names

  • Adding Names
  • Deleting Names
  • Creating Hidden Names
  • Checking for the Existence of a Name

Manipulating data

  • Working with the Ranges and Selections
  • Using the Cells Property to Select a Range
  • Using the Offset Property to Refer to a Range
  • Using the Columns and Rows Properties
  • Using the Union Method to Join Multiple Ranges
  • Using the IsEmpty Function
  • Using the CurrentRegion Property
  • Using the Areas Collection

File management techniques in VBA

  • Open and Save files
  • Copy, move and delete files
  • Changing folders
  • Using Excel Open and SaveAs dialog boxes in code

Custom Forms / Dialog boxes

  • Creating a user form
  • Labels, text boxes, combo boxes and list boxes
  • Setting properties for the form and controls
  • Assigning data to combo boxes and list boxes
  • Option buttons, Groups, checkboxes and buttons
  • Creating the event code for controls
  • Initialising the form
  • Closing the form
  • Using RefEdit to allow user interaction

Managing Information with VBA

  • Linking Excel to an Access database
  • Adding a Record to the Database from Excel
  • Retrieving Records from the Database
  • Updating an Existing Record