Presentation Style: Tutor-led Classroom Training Course (call to enquire about online course)
Duration: 2 Days
Course Time: 09:30 - 16:30hrs
Course Location: Althorp, Northampton
Cost:

Public Courses: £1,050 + VAT per delegate, £1,000 each + VAT for two delegates on the same day (call for group discounts). Minimum 3 candidates required per course.
Closed Courses: please enquire
Click a date to book: Call 01604 655900 for available dates

 

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. 

Who should take this VBA Training Course?

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, delegates 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 delegate will access and use a significant number of training files which they will retain after the course as an on-going personal Excel VBA reference library.

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

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

Prerequisites

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

Course Content

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

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

 

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

Declaring (Dim / Private / Public) Variables

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, 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 model contains two practical’s 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 practical’s 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.

Additional Information

  • Buffet lunch included and free parking (only for courses held at Paradise Training Centre, Northampton)
  • Joining instructions will be sent prior to attending the course