Introduction

Learn how to use VBA to take complete control of Excel on this 2-day  Excel VBA training course.

This course is hands-on. There is plenty of time to try out what you are being taught by writing your own VBA code and also to ask questions.

By the time you finish, you’ll be a confident VBA programmer.

✔ A complete VBA course that covers all of the core concepts you need.
✔ Plenty of one to one attention and time to ask questions.
✔ Friendly expert trainers, small groups and a comfortable place to learn.
✔ All the materials and extras that you’ll ever need.
✔ Ongoing support and help with issues you have after the course.

What Will I Learn?

This course will get you confidently writing your own code in no time so that you can return to work and start automating your spreadsheets straight away.

You’ll learn about:

  • Creating recorded macros in Excel.
  • The Excel object model and VBA concepts.
  • The key features of the VBA Editor window.
  • Using a range of common programming techniques.
  • Creating procedures, variables, objects and user-defined functions.
  • Creating a custom form complete with controls.
  • Creating procedures that start automatically.

Am I Ready For This Course?

This course does not require any prior knowledge of VBA or Excel macros but it does assume a knowledge of Excel up to an Advanced level.

We would recommend that you have attended our Advanced Excel course or have an equivalent level of Excel knowledge through your day-to-day work before you attend this course.

Note: This course is designed to concentrate on the programming aspects of Excel, notably macros and Visual Basic for Applications (VBA).

The Training Day

Our courses run from 9.30am to roughly 4.15pm with plenty of refreshments and biscuits throughout the day and a break for a tasty, freshly prepared lunch.

We offer a relaxed, supportive learning environment, fully air-conditioned training facilities and some of the nicest instructors on the planet.

Also, you’ll receive:

✔ A full-colour A4 manual to keep which covers everything in the course.
✔ A USB stick so that you can take the course exercises home with you.
✔ A Certificate of Attendance.

Macros allow non-programmers to automate repetitive tasks in Excel. The macro recorder allows you to automatically generate Excel VBA code. You simply carry out the specific task that you would like to automate and the macro recorder will convert that into Excel VBA code. This lesson introduces macros and shows you how to record a macro.

  • Understanding Excel Macros
  • Setting Macro Security
  • Saving a Document as Macro Enabled
  • Recording a Simple Macro
  • Running a Recorded Macro
  • Relative Cell References
  • Running a Macro With Relative References
  • Viewing a Macro
  • Editing a Macro
  • Assigning a Macro to the Toolbar
  • Running a Macro From the Toolbar
  • Assigning a Macro to the Ribbon
  • Assigning a Keyboard Shortcut to a Macro
  • Deleting a Macro
  • Copying a Macro
  • Practice Exercise
  • Practice Exercise Sample

Now that you are comfortable with macros the course moves on to dealing with Excel VBA directly. This session covers the key concepts including terminology, ways to work in the VBA editor in Excel and the object model that is key to programming in VBA.

  • Programming in Microsoft Excel
  • 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
  • Using the Object Browser
  • Programming With the Object Browser
  • Accessing Help
  • Code Glossary
  • 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

Now that you have had a thorough introduction to Excel VBA and the code editor, this session delves further into how you write code. In it you will learn how to write comments in your code and how to clearly lay out your code so that others can understand what it does.

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

Variables are a key concept in all programming. This session covers their use in Excel VBA. So how to create one, how to use one, when and where you can use a variable that you have created and how you change it’s value.

  • 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 for Variables
  • Declaring Data Types
  • Using Arrays
  • Code Glossary

Advanced users of Excel will know that it contains huge numbers of functions. They make carrying out the specific tasks they are designed for very fast. VBA allows users to create their own custom functions and also insert them into the ribbon.

  • Understanding Functions
  • Creating User-Defined Functions
  • Using a User-Defined Function in a Worksheet
  • 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
  • Code Glossary

This lesson takes a far more detailed look at methods, applications, objects and properties.

  • 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
  • Code Glossary

This lesson covers two key concepts. It starts with a detailed look at how to use message boxes to communicate with users. Either informing them of something or asking them to give input. It then moves to covering IF statements. These allow programs to carry one of two or more pieces of code depending on if a condition, or conditions, have been met.

  • 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
  • Code Glossary

This session focuses on forms. Generally users interact with Excel VBA programs via forms. This means that it is very important to set up and design your forms correctly to make it straightforward for a user to work with your program.

  • 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

Having learned how to create your form in the previous session, this session shows you how to trigger a form in your program, how to transfer the data that the user has input into the form in your code and how to error check user input.

  • 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
  • Code Glossary

This final session in the two day course focuses on automation. Now that you have learned how to create your custom VBA program this will show you how to get the most from it.

  • Programming Automatic Procedures
  • Running Automatic Procedures
  • Automatically Starting A Workbook
  • Understanding Error Types
  • The on Error Statement
  • Simple Error Trapping
  • Using the Resume Statement
  • Using Decision Structures in ErrorHandlers
  • Working With Err Object
  • Error Handling in Forms
  • Coding Error Handling in Forms
  • Defining Custom Errors
  • Code Glossary

About Visual Basic (VBA)

Visual Basic for Applications (‘VBA’) is a programming language, developed by Microsoft, that allows you to extensively manipulate Microsoft Excel (and other Microsoft Office applications).

Programming in VBA does not require any additional software as an editor is built into Excel. It is very accessible for the non-programmer and is often the first programming language that people learn.

Typical areas where Excel VBA is used include:

  • Automating data processing, report generation and chart creation in Microsoft.
  • Customising the Excel ribbon to give access to new user defined functionality.
  • Automating communication between Microsoft Excel and databases.