Course Overview and Aim

This two day, practical Excel Visual Basic Application (VBA / Macros) course, is designed to be an excellent introduction for Advanced Excel users to enable them to read, write, edit and use Excel VBA Macros in their day to day role.

This course will take an absolute beginner, with no prior programming experience, from recording a basic macro to reading and building practical working macro solutions which will include up to intermediate level understanding. It will enable attendees to gain the appropriate knowledge and necessary proficiency to harness the power of Excel VBA to automate repetitive tasks such as analysing and manipulating data.

Also and more importantly, attendees on completion of this course, will be able to read and create VBA code to significantly save themselves and others time by streamlining the production and management of Excel data. They will acquire a good foundation to enable them to custom
design their own VBA Macros to simplify complex manual tasks.

This course contains the reading and using of Excel VBA code within each module, to support each of the topic areas being covered and also includes a number of practical related exercises.

The attendee will access, use and retain during the course a significant number of training files which they will retained after the course as on-going personal Excel VBA reference library.

The attendee will additionally receive practical guidance on programming structure and design techniques.

This course will provide the attendee with an excellent introduction to base their own further development of their practical VBA skills.

Attendees

As a pre-requisite for attending this course, attendees must have a very good practical working knowledge of Excel. Therefore the attendee must have attended courses up to Excel Advanced level training or have equivalent practical experience prior to attending this training course.

Course Type

In-House, Expert Instructor led and guided.

Please note: Microsoft Excel 2016 is used as the primary application during the course for demonstration purposes. However all training content is usable in Excel 2010 / 2013.

Duration

2 day course – Start time 9.00 a.m. Finish 5 p.m.

It is recommend all attendees to aim to arrive, at least 15 mins, before the start of the course, to ensure all pre-course administration and set-up is completed in a timely manner.

Course Content

What is VBA?
What is a Macro?
What can Macros do?
To use or Not to use Macros?
Macro – The 5 Tenets
What is the Excel Object Model?
Your Personal.XLSB file
Need to check your security options
How do I access VBA?
Macro Security Settings
Displaying and reviewing the Developer Tab in the Ribbon

Opening a Macro-Enabled Workbook
Opening and Using VBA Editor
Opening and Closing VBA Editor
Explanation of the VBA Screen Layout / Elements
Using the Project Explorer – Ctrl + R
Working with the Properties Window – F4
Using the Editor Work Pane
Introducing the Immediate Pane – Ctrl + G
VBA Help – F1
Explanation of a Module
Running Code – F5
Stepping through code – F8
Setting Breakpoints in Code – F9 (toggle on / off)
Editing, Copying and Deleting a Macro
Notation of code – why important and how to annotate
Structuring your code to be readable
Indent and Outdent
How to review a Macro and its code
Practical – Opening a “Real Life Example” File / reviewing it
Why record a macro?
How to name and record a macro?
How to review / test / run a recorded macro?
Commenting the code?
What are the limitations of recording a macro?
Can I record code to get code?
Practical – Recording a Macro and all that this involves
Saving a Macro-Enabled Workbook (.xlsm)

Program design and concepts
A Good Spreadsheet Application
Code Format / Layout
To Dim or Not to Dim? In other words Why Dim?
How to Declare a Variable / Dim / Private / Public
Understanding Constants and how to Declare them
Option Explicit
Run Timing Test Macro
Where / How to Dim
Modules – Understanding how to Create, Name, Edit, Copy and Remove
Practical on Modules
Procedure aka Subroutine aka Sub
Sub Naming conventions
Creating and Calling other Sub(s)
This module contains a practical – Company Expenses Extract and Public Variable Call

Understanding Objects
Understanding Object hierarchy
Referring to Objects
Application Objects – Practical in file review
Objects, Properties, Methods, Events
Working with Properties
Working with Methods
Working with Events
Reference to Opening Workbook Events

Using Expressions / Statements
What is a Variable and how to assign one?
Working with Variables
Variable Naming
Declaring (Dim / Private / Public) Variables
Creating and using Variables
Understanding and using Data Types
Practical – Using Locals Window to find Variable Data Types

Working with the ranges and selections
How to use the cells property to select a range
How to reference Range(s) and a Range Name
How to select a range in Excel
How to use the offset property to refer to a range relative to a starting position
How to use the Activecell Property
How to use the CurrentRegion and Address Properties
Using the columns and rows properties to specify a range
Determining the extent of data – last cell / last row
Copying and pasting cells / data
Improving Performance with ScreenUpdating and DisplayAlerts

Formatting Cells
How to change the:
Background colour of a cell
Cell alignment, Column width, Formatting borders
Font – Style, Size, Colour, Bold, Italic, Underline
Using With … End, With Statement

Working with Strings (prior knowledge of related Excel formulas is required)
Changing case – Upper, Lower and Proper
Trim and Spaces
Len, Replace, Instr
Left, Right, Mid

This module contains two practicals for attendees to try post course to reinforce their learning