04 VBA Excel Course

Visual Basic for
Applications (VBA)

Visual Basic for Applications (VBA) introduces functionality through programming solutions that’ll expand your productivity. The skills and knowledge you’ll acquire in this VBA course will be the starting point in learning to create real-life, working VBA applications within Excel.

  • Designed for experienced Excel users.
  • Courses can be customised to incorporate your actual projects.
  • Learn to access the functionality of Excel at the programming level
Request a Quote
About course

Course Details

Course delivery method
This course is designed to be delivered on-site at your premises to individuals or groups.
Course duration
Full course duration is 2 days, but training can be delivered in shorter blocks.
What you will learn

Topics Covered

Understanding Excel VBA
Starting With Excel VBA
Procedures
Using Variables
Functions in VBA
Using Excel Objects
Programming Techniques
Creating Custom Forms
Programming User Forms
Automatic Startup
Error Handling

Understanding Excel VBA

  • VBA Terminology
  • Displaying the Developer Tab
  • The VBA Editor Screen
  • Opening and Closing the Editor
  • Understanding Objects
  • Viewing the Excel Object Model
  • Using the Immediate Window
  • Working with Object Collections
  • Setting Property Values
  • Working with Worksheets
  • Opening and Closing the Editor
  • Opening and Closing the Editor

Starting With Excel VBA

  • Using the Project Explorer
  • Working with the Properties Window
  • Using the Work Area
  • Viewing Other Panes
  • Working with Toolbars
  • Working with a Code Module
  • Running Code from the Editor
  • Setting Breakpoints in Code
  • Stepping Through Code

Procedures

  • Understanding Procedures
  • Where to Write Procedures
  • Creating a New Sub Routine
  • Using IntelliSense
  • Using the Edit Toolbar
  • Commenting Statements
  • Indenting Code
  • Bookmarking in Procedures

Using Variables

  • Understanding Variables
  • Creating and Using Variables
  • Explicit Declarations
  • The Scope of Variables
  • Procedure Level Scoping
  • Module Level Scoping
  • Understanding Passing Variables
  • Passing Variables by Reference
  • Passing Variables by Value
  • Understanding Data Types forVariables
  • Declaring Data Types
  • Using Arrays

Functions in VBA

  • Understanding Functions
  • Creating User-Defined Functions
  • Using a User-Defined Function in aWorksheet
  • Setting Function Data Types
  • Using Multiple Arguments
  • Modifying a User-Defined Function
  • Creating a Function Library
  • Referencing a Function Library
  • Importing a VBA Module
  • Using a Function in VBA Code

Using Excel Objects

  • The Application Object
  • The Workbook Objects
  • Program Testing with the Editor
  • Using Workbook Objects
  • The Worksheets Object
  • Using the Worksheets Object
  • The Range Object
  • Using Range Objects
  • Using Objects in a Procedure

Programming Techniques

  • The MsgBox Function
  • Using MsgBox
  • InputBox Techniques
  • Using the InputBox Function
  • Using the InputBox Method
  • The IF Statement
  • Using IF for Single Conditions
  • Using IF for Multiple Conditions
  • The Select Case Statement
  • Using the Select Case Statement
  • For Loops
  • Looping with Specified Iterations
  • The Do Loop Statement
  • Looping With Unknown Iterations

Creating Custom Forms

  • Understanding VBA Forms
  • Creating a Custom Form
  • Adding Text Boxes to a Form
  • Changing Text Box Control Properties
  • Adding Label Controls to a Form
  • Adding a Combo Box Control
  • Adding Option Buttons
  • Adding Command Buttons
  • Running a Custom Form

Programming User Forms

  • Handling Form Events
  • Initialising a Form
  • Closing a Form
  • Transferring Data from a Form
  • Running Form Procedures
  • Creating Error Checking Procedures
  • Running a Form from a Procedure
  • Running a Form from the Toolbar

Automatic Startup

  • Programming Automatic Procedures
  • Running Automatic Procedures
  • Automatically Starting a Workbook

Error Handling

  • Understanding Error Types
  • The on Error Statement
  • Simple Error Trapping
  • Using the Resume Statement
  • Using Decision Structures in Error Handlers
  • Working with Err Object
  • Error Handling in Forms
  • Coding Error Handling in Forms
  • Defining Custom Errors

Benefits

Learn to create macros and then make the jump to coded macro solutions through the modification of VBA routines.
Learn to use input boxes and message boxes to pass values within your visual basic code and through the spreadsheet.
Learn to create user-defined forms containing Text Boxes, Combo Boxes and Option Buttons, among other tools.
Understand how to use objects, methods and properties to create well-defined code segments that can be reused.
Learn to trap and mitigate problems through error handling techniques so VBA routines run smoothly.

Full course outline

The bulleted items listed above comprise a condensed overview of course topics.

Download course outline (PDF)

Prerequisites

While no Visual Basic For Applications knowledge is assumed, the participant should have an intermediate or higher level of Microsoft Excel knowledge.

Test your teams skills

Get a Free Training Needs Analysis

Request training needs analysis
Courses
Company
On-Site Training
Contact
Services
Copyright © 2020 Excel Dimensions  |  Privacy policy | Articles