Excel VBA Macro Training Course

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.

Prerequisites

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. If you are unsure, please do get in touch.

We would be happy to discuss custom / on-site MS Excel VBA training for up to a maximum of eight delegates. We can take into account your existing VBA skills, be able to assist with existing VBA macro files, and specific topics of interest to tailor the most relevant and focussed the course for you.

This can be particularly useful if you need to learn certain key topic areas, or need to include extra topics to help with pre-requisite skills. If you would like to discuss your custom training requirements, please get in touch.

Module 1 - 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

Module 2 - 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)

Module 4 - 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

Module 6 - 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

Module 7 - 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

Module 8 - 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

Module 9 - 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

Module 10 - 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.