Key Training Logo

Microsoft Excel VBA Advanced

Course Code: XLL7

Duration: 1 Day

Course Aims:

This course is aimed at people who already have a very good knowledge of Excel VBA and who wish to improve on this to create more effective, interactive and user-friendly procedures, and extend the reach of their VBA code into areas outside of the Excel application.

Course Pre-requisites:

Students who wish to attend this course need to have significant experience and ability at understanding and using Excel advanced functionality and have attended the Microsoft Excel VBA Introduction and Intermediate courses.

Course Objectives:

On completion of this course delegates will be able to:
▪ Write code that prevents or diminishes the chance of errors and failures occurring when a macro is run
▪ Write procedures that automatically run when an action is initiated either by the user or by the system
▪ Enable interaction between a user and a procedure using a custom user form (dialog box) and ActiveX Worksheet controls
▪ Use VBA to create, structure and format charts
▪ Use VBA to create, structure and update Pivot Tables
▪ Produce code that interacts with and facilitates the use of other Microsoft Office application objects

Course Content:

Introduction and Objectives

Error Handling & Trapping
▪ Use VBA to create, structure and format charts.
▪ Use VBA to create, structure and update Pivot Tables.
▪ Produce code that interacts with and facilitates the use of other Microsoft Office application objects.

Event Procedures
▪ Events Associated with Worksheets
▪ Events Associated with a Workbook

Custom User Forms and Form Controls
▪ User-Defined Forms
▪ Adding Controls to a Form
▪ Form Controls Design Tools and Techniques
▪ Form Control Properties
▪ Programming a User Form
▪ Displaying a User Form
Using ActiveX Controls directly on a Worksheet

Working with Object Variables
▪ Declaring Object Variables
▪ Assigning Objects to variables

Working with Charts
▪ Creating Charts
▪ Sizing and Positioning Charts
▪ Naming Charts
▪ Formatting Charts

Working with Pivot Tables
▪ The Pivot Table Object
▪ Creating a Pivot Table
▪ Manipulating Pivot Table Fields

Interacting with Other Office Applications
▪ Communicating with Word using Excel VBA

Action planning

Review of Programme

Key Affiliations

 
RI Supplier Awards
European Union
Investors in people
Matrix
Disability Confident Committed
Ofsted
REC Logo
ESFA
Inspiring the future
Youth Employment UK
AELP
5 Percent Club
ARC
Skills for health Quality Mark
RI Top 500
Apprenticeships
Team Service Provider
Best Company 2019
Cyber Essentials