Microsoft VBA Excel Introduction
|Presentation Style:||Tutor-led Classroom Training Course|
|Course Time:||09:30 - 16:30hrs|
|Cost:||Public Courses: £480 + VAT per delegate (call for group discounts)
Closed Courses: please enquire
|Click a date to book:||No upcoming courses|
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.
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.
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
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 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
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
How to change the
Background colour of a cell
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
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.
- 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