Presentation Style: Tutor-led Classroom Training Course
Duration: 2 Days
Course Time: 09:30 - 16:30hrs
Course Location: Northampton
Cost: Public Courses: £480 + VAT per delegate (call for group discounts)
Closed Courses: please enquire

 

This two day, practical Excel Introduction to Visual Basic Application (VBA / Macros) course, is designed to familiarise Advanced Excel users with how to first understand and then write and edit VBA Macros.

Who should take this VBA Training Course?

This course will take an absolute Excel VBA beginner with no prior programming experience from recording a basic macro to being capable of understanding how macros are constructed and also how to start building practical working macro solutions.

It will enable attendees to gain the appropriate knowledge and necessary proficiency to start harnessing the power of Excel VBA to automate repetitive tasks and simplify complex manual tasks such as analysing and manipulating data.

This course, contains practical exercises in each module to support each of the topics being covered and each of these completed exercise files will be retained by the attendee after the course as permanent personal reference materials.

The attendee will also receive practical guidance on good programming techniques and together the above, will provide the attendee with a sound basis for further developing their 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 well advanced practical user 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
What is the Excel Object Model
To use or not to use Macros
Macro Security Settings
Your Personal.XLSB file
Need to check your options
Macro – The 5 Tenets
How do I access VB
Displaying and reviewing the Developer Tab in the Ribbon
Training

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 screen layout
Using the Project Explorer - Ctrl + R
Working with the Properties Window - F4
Using the Editor Work Pane
Introducing the Immediate Pane - Ctrl + G
Introducing the Object Browser - F2
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
Opening a “Real Example” File / reviewing it
Recording a Macro and all that this involves
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
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 – Option Explicit
Why Dim, Where / How to Dim
Declaring a Variable – Dim
Run Timing Test Macro
Understanding Constants
Modules - Creating, Naming, Editing, Copying and Deleting
Procedures aka Subroutines aka Sub
Sub Naming conventions
Creating and Calling other Sub(s)

Module 4 - Understanding Objects, Properties, Methods and Events

Understanding Objects and Object hierarch
Using the Object Browser – F2
Referring to Objects, Application Objects
Objects, Properties, Methods, Events
Working with Properties, Methods and Events
Objects and Workbook Events
Creating and Running an Event Procedure
Automatically running a Worksheet Double Click Event
Automatically starting when a Workbook opens

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) Variables
Creating and using Variables
Understanding and using Data Types
Using Locals Window to see 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)
How to reference a Named Range
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
Changing case
Trim and Spaces
Len, Replace, Instr
Left, Right, Mid

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

 

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