Microsoft Excel VBA Introduction

This course will benefit those who wish to build on their advanced knowledge of Excel. Participants will gain the skills needed to speed up repetitive tasks and write VBA code. On
completion of this course, participants will be able to record and edit macros, write VBA code and create a user friendly interface to automate tasks.

Prerequisites
Participants must have an intermediate/advanced working knowledge of Excel.

Content

What is a Macro?
Concepts
Absolute and relative recording
The Personal Macro Workbook
Assign a macro to a button or keyboard shortcut
Create a custom ribbon and manage grouping

The Visual Basic Editor Window
The Project window
Code windows
Properties window
Locals window
Switching between Excel and the VBE screens
Changing important VBE settings

Understanding Code
Understanding the Excel VBA object model
Working with objects, properties and methods
Code storage and scope
The structure of routines
Common commands

Interactive Code
Understand variables and declaration
Understand constants
Create message boxes
Create conditional message boxes
Manage input boxes

Loops and Conditional Constructs
IF, THEN, ELSE
AND, OR, NOT
Select Case
Using the With statement
Looping

Checking for Errors
Debugging
Step mode
Breakpoints
Diagnosing syntax, compile and runtime Errors

Advanced User Interface
Create user forms
Using different user form controls
Naming conventions
Running code on user forms
Working with workbook and worksheet events

Custom Functions
Create custom Excel functions
Use them in macros