IT TRAINING

Microsoft Excel VBA

In this course you will learn about more advanced Microsoft Excel features.

Who needs to attend

Who needs to attend?
This course is aimed at advanced users of Microsoft Excel, if they are responsible for very large and variable amounts of data, or teams, who need to learn how to program features and functions to develop the accessibility and usability of their data.

what you will learn

What you will learn

Upon completion you will know how to:

  • By the end of the Excel VBA training course the delegate will be competent in the fundamentals of VBA, including working with procedures and functions, reading and writing code, and diagnosing errors in code.
  • This includes controlling program execution with decision logic, making code repeat with loops and working with multiple cell ranges.
  • Add a layer of interactivity to your applications by creating UserForms, message boxes and Input Boxes.

In order to ensure minimal downtime while building and rolling out the program, the course also teaches how you can debug code and handle errors.

Prerequisites

Prerequisites

Students need to have:

  • A thorough knowledge of Microsoft Excel is required, or completion of Excel Level 3 course.
Course outline

Course Outline

Introducing Visual Basic for Applications

Why use VBA?
Recording and running macros
Absolute versus relative cell selection
Working with the Visual Basic Editor

Creating your own code

Understanding and creating modules
Defining procedures
Calling procedures
Where to store macros

Making decisions in code

Using logical comparisons
The IF…ENDIF structure
The SELECT CASE…END SELECT structure
When to use IF v SELECT CASE

Debugging errors

Defining errors
Setting breakpoints to pause execution
How to step through code
Working with break mode to spot errors
Identifying the value of expressions

Understanding Visual Basic

What is an Object?
Examining the Excel object hierarchy
Using the object browser
How to explore methods and properties
Getting help in VBA

Repeating code with loops

The DO… LOOP structure
The FOR… NEXT structure
The FOR EACH… NEXT structure
How to debug problems with loops

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

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

Follow on
There are no follow-ons for this course.

Certification programs
There are no certifications associated with this course.