VBA for Excel Training Course

This two-day workshop is designed for Intermediate to Advanced Excel users who want to expand their knowledge by being able to read, write, edit and use Excel VBA Macros in their day-to-day role.

Delegates should have a very good practical working knowledge of Excel. You will ideally have taken training of at least intermediate level or have equivalent practical experience prior to attending this training course.

We can customise the training to match your team's experience and needs - with more time and coverage of fundamentals for new developers, for instance.

This Excel VBA course is available for private / custom delivery for your team - as an in-house face-to-face workshop at your location of choice, or as online instructor-led training via MS Teams (or your own preferred platform).

Get in touch to find out how we can deliver tailored training which focuses on your project requirements and learning goals.

Introducing Visual Basic for Applications

• 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

VBA Editor & Recording Macros

• 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)

• Module 3 - Modules and Procedures

• 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

• Run Timing Test Macro

• 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)

Understanding Objects, Properties, Methods and Events

• 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

Module 5 - Using Expressions and Variables

• Using Expressions / Statements

• What is a Variable and how to assign one?

• Working with Variables

• Variable Naming

• Creating and using Variables

• Understanding and using Data Types

• Practical - Using Locals Window to find Variable Data Types

Manipulating Data

• 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 and Working with Strings

• 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 delegates to try post course to reinforce their learning

Workbooks and Worksheets

• Working with Workbooks

• Creating, Saving, Switching and Closing

• Working with Worksheets

• Adding Worksheets in VBA code

• Naming and renaming Worksheets

• Deleting Worksheets

• Copying and moving Worksheets

• This module also contains reference on how to create your own VBA Objects

Controlling Program Execution / Decision Structures

• Understanding Control-of-Flow Structures (If…Then… End If and Loops)

• Using the following:

• If Statement, If...End If, Single / Multiple Condition, If Else Statement,

• If...Else … End If, Conditions, If Elseif Statement, If...Elseif … End If, Conditions, Nested Ifs

• Select Case...End Select Statement

• Do...Loop, Do...Until, Do…While Statements

• For...To...Next Statement, For Each...Next Statement

Using Message Boxes, Input Boxes and Running Macros

• Creating and Using Message Boxes – MsgBox and Input Boxes – InputBox

• Running a Macro from within Excel

• Assigning a Keyboard Shortcut to a Macro

• Assigning and launching a Macro from a TextBox, a Toolbar Icon

• Practical Application

• A number of practicals will be undertaken throughout the course and of note at start of Day Two a Practical Exercise task is to be completed by the attendees to reinforce and put into practice what they have learnt on Day One.

• Microsoft Excel Help - The official Microsoft Excel support website offers a wealth of articles, tutorials, and how-to guides.

• Microsoft Excel Blog - Stay up-to-date with new features, tips, and best practices from the official Microsoft Excel blog.

• MrExcel.com Forums - A very active and helpful community forum where you can ask questions and find solutions to Excel challenges.