Microsoft Excel VBA Intermediate

Designed for Excel 365, 2019, 2016, 2013, 2010

Who is this course for?

This course is suitable for advanced users of Microsoft Excel looking to further increase their knowledge of VBA.

Prerequisites

A beginners knowledge of Microsoft Excel VBA is required, or completion of our Excel VBA Introduction course.

Benefits

By the end of the course the delegate will be fully competent in the fundamentals of VBA, including working with functions, understanding objects, using expressions, variables and intrinsic functions.
This includes mastering forms and controls, and building user defined functions.
There will also be discussion of techniques for improving and optimizing code.

• Course content

  Storing information with variables

  How and why you should declare variables
  Determining which data type to use
  Public v Private scope
  Using variables to trap errors
  Using the Locals window to observe variables

  Creating functions

  Writing your own User Defined Functions (UDFs)
  Working with multiple arguments
  Using your function in Excel

  Message Boxes and Input Boxes

  Displaying a message
  Adding a yes / no user choice
  Getting feedback from the end user

  Handling Errors

  Defining VBA’s Error Trapping Options
  Capturing Errors with the On Error Statement
  Determining the Err Object
  Coding an Error-Handling Routine
  Using Inline Error Handling

  Creating custom dialogue boxes with UserForms

  Drawing UserForms
  Setting UserForm properties, events and methods
  Using text boxes, command buttons, combo boxes and other controls
  Formatting controls
  Applying code to controls
  How to launch a form in code