98.9% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Excel VBA Advanced Training Courses
Face to face / Virtual public schedule & onsite training. Restaurant lunch included at STL venues.
Designed for Excel 365
From £380 List price £699
- 2 days Instructor-led
- Courses never cancelled
- Restaurant lunch
Syllabus
Who is this course for?
This course is aimed at individuals with a basic grounding in Excel VBA and an advanced knowledge of Microsoft Excel, who wish to develop their skills with introduce more sophisticated automation into their workflows.
Prerequisites
Attendance of our Microsoft Excel VBA course, or equivalent knowledge.
Including the understanding of the following:
- Variable types
- Using object variables to represent worksheets and workbooks
- Using count based (For-Next) and conditional based (Do-Until, Do-While) loops
- If-Then-Else-End If and Select Case statements
- With blocks
- Using Range and Cells objects
Benefits
- Upon completion of the course delegates will have a good understanding of the major components of VBA.
- Delegates will expand their reach by being able to use VBA to communicate with other Office applications, such as Word.
- In addition, the course also covers working with data from database applications such as Access, and manipulating and presenting the data.
Course Syllabus
The Excel Object Model
Exploring the Range object in detail
The versatile CurrentRegion object
Working with collections of Workbooks and Worksheets
Manipulating Charts through VBA
Manipulating PivotTables through VBA
Arrays
Efficient variable storage with arrays
Array optimisation
Dynamic arrays
The Array function
Triggers and Events
Running macros automatically
Executing macros on a timer
Associating macros with other Workbook events
Advanced Parameters
Passing parameters by reference and by value
Working with Text Files
Importing text files
Exporting text files
Using the FileStream object
Linking with Office
Connecting to other Office applications
Working with other Office applications
Linking to data sources using ADO
Understanding ActiveX Data Objects
Using Excel to communicate with other data sources
Understanding the connection string
Adding, reading, modifying and deleting data
Add-Ins
How macro security works
Password protecting your code
Distributing macros via an Add-In
Appendix: Classes and Objects
Working with Class Modules
Prices & Dates
What you get
"What do I get on the day?"
Arguably, the most experienced and highest motivated trainers.
Face-to-face training

Training is held in our modern, comfortable, air-conditioned suites.
Modern-spec IT, fully networked with internet access
Lunch, breaks and timing
A hot lunch is provided at local restaurants near our venues:
- Bloomsbury
- Limehouse
Courses start at 9:30am.
Please aim to be with us for 9:15am.
Browse the sample menus and view joining information (how to get to our venues).
Refreshments
Available throughout the day:
- Hot beverages
- Clean, filtered water
- Biscuits
Virtual training

Regular breaks throughout the day.
Learning tools

In-course handbook
Contains unit objectives, exercises and space to write notes
Reference material
Available online. 100+ pages with step-by-step instructions
24 months access to Microsoft trainers
Your questions answered on our support forum.
Training formats & Services
Training Formats & Services
|
Testimonials
Financial Times
Helen Poon,
Corporate Development & Strategy Associate (M&A)
Thank you Jens, a fantastic course, I really appreciate the tailored approach and helpfulness.
Excel VBA Advanced
British Safety Council
Emineh Der Hovsepian,
Business Information Analyst
Very Productive course and very efficient
Excel VBA Advanced
Ultra Electronics Controls
Hasif Toofanny,
Graduate Engineer
When moving from one topic to the other, perhaps a brief pause in the class to separate the chapters would be useful. It would allow delegates to have the separation between the chapters emphasized. This could also be useful for a bathroom break, given that bathroom breaks are much needed given the free tea/coffee/hot choc :)
Please always ensure chocolate chip cookies are available in abundance - they are so good that they quickly ran out :(
Other than that, I'm really satisfied and really enjoyed my courses, thank you! :)
Excel VBA Advanced
Training manual sample
Below are some extracts from our Excel training manuals.
Excel VBA Advanced
Unit: Class Modules
What can be done with Class Modules?
Class modules allow you to create and use your own object types in your application. This implies the following;
- You can easily write code that works with any workbooks that do not have any code.
- Two or more procedures for the event of a command button can be consolidated in one
- The code is easy to use by concealing logic and data.
Why use Class Modules?
Classes make your code:
- Development simpler
- More manageable
- Self-documenting
- Easier to maintain
What is a Class?
A Class is a Blueprint or template of an Object.
In Excel VBA, an Object can mean Workbooks, Worksheets, User forms and Controls etc. Normally an Object has Properties or Methods. A Property stands for Data that describes the Object, and a Method stands for an action that can be ordered to the object.
Properties and Methods of the Object depend on the kind of Object.
For Example;
Worksheet (1).Select
... selects the first worksheet in the workbook. Select is a method of the worksheet object.
How Does a Class Module Work?
A Class Module is a place where a Class is defined. The procedures in a class module are never called directly from other modules like the procedures placed in the standard modules.
In the view of a standard module, the class module doesn't exist.
The thing that exists in the view of a standard module is an instance of the object generated by the class defined by the class module. The methods and procedures of the class are defined within the class module.
Key Elements in a class module
The class module defines all the properties and methods associated with the class. In the example below the "customer" class has two properties associated properties; Name and Main Address.
These are defined by the Property Get and Property let Procedures (see below).
The Customer ID is calculated by taking the leftmost 3 characters from the customer's Name and concatenating that with the 5 leftmost characters from the main Address. This is the result of the method GetCustomerID, and is defined in a function in the class module
Property Get and Let Procedures
A property is implemented using a property let and a property get procedure. When someone sets a value for a property the property let procedure is called with the new value. When someone reads the value of a property the property get procedure is called to return the value. The value is stored as an internal private variable.
Read only properties can be created by implementing a property get procedure without a corresponding property let procedure.
Example of a Class Module
Option Explicit
Private strName As String
Private strAddress As String
Public Property Get Name() As String
Name = strName
End Property
Public Property Let Name(ByVal value As String)
strName = value
End Property
Public Function GetCustomerID()
GetCustomerID = Left(strName, 3) & Left(strAddress, 5)
End Function
Public Property Get MainAddress() As String
MainAddress = strAddress
End Property
Public Property Let MainAddress(ByVal value As String)
strAddress = value
End Property
Referring to user defined Objects in Code
This simply involves creating an instance of the Class in Code and then manipulating it is the way you would any other object.
The following code would be placed in a standard module, and refers to the customer object defined previously.
Option Explicit
Dim aCustomer As Customer (1)
Sub TestCustomer()
Set aCustomer = New Customer (2)
aCustomer.Name = "Evil Genius" (3)
aCustomer.MainAddress = "123 the Hollowed out Volcano" (4)
MsgBox "Company ID is " & vbCrLf & aCustomer.GetCustomerID() (5)
End Sub
Line 1 defines an object variable as a Customer variable, and line 2 sets it as a new customer object. Line 3 assigns a value to its name property and line 4 a value to its Main Address property.
Line 4 uses the GetCustomerID Method to generate the CustomerID value and returns it in a message box.
Using IntelliSense™
Microsoft IntelliSense is a convenient way to access descriptions of objects and methods. It speeds up software development by reducing the amount of name memorization needed and keyboard input required. Once a class is defined in a class module, Intellisense will automatically provide drop down lists showing the methods and properties of objects the names of which have been entered into the VBE.
Working with Collections
A class is a blueprint for an object, and individual objects are "instances" of a class. A collection is simply a group of individual objects with which we are going to work.
For example in the code above we have defined a class called customers, and code to generate a single instance of that class; i.e. one individual customer. In practice we will be working with more than one customer and we will wish to define them as being part of a collection object so we can process them using some of the methods and properties of the collection object.
The Collection Object
The collection object has a number of properties and methods associated with it; of which the most important are:
Method/Property | Description |
Count | A method that returns the number of objects in the collection |
Add | A method that adds an item to the collection |
Remove | Removes an item to a collection |
Items(index) | Refers to an individual item in the collection either by its index number (position in collection) or by its name |
Explicit creation of a collection
We can create a collection in a class module. This simply requires us to define the collections objects and methods in the normal way
Option Explicit
Private FCustomers As New Collection
Public Function add(ByVal value As Customer)
Call FCustomers.add(value, value.Name)
End Function
Public Property Get Count() As Long
Count = FCustomers.Count
End Property
Public Property Get Items() As Collection
Set Items = FCustomers
End Property
Public Property Get Item(ByVal value As Variant) As Customer
Set Item = FCustomers(value)
End Property
Public Sub Remove(ByVal value As Variant)
Call FCustomers.Remove(value)
End Sub
The above code simply defines a collection called customers (class module name). The variable FCustomers is defined as a collection object. The various methods and properties are then defined. For example, the remove method is defined in a procedure that uses the remove method of the collection object to remove a specified item from the collection.
Referring to a collection in a standard module
Once defined, a collection can be employed in the same way as any other collection.
Dim aCustomer As Customer
Dim theCustomers As New Customers
Set aCustomer = New Customer
aCustomer.Name = "Kur Avon"
aCustomer.MainAddress = "132 Long Lane"
Call theCustomers.add(aCustomer)
Set aCustomer = New Customer
aCustomer.Name = "Fred Perry"
aCustomer.MainAddress = "133 Long Lane"
Call theCustomers.add(aCustomer)
Set aCustomer = New Customer
aCustomer.Name = "Jo Bloggs"
aCustomer.MainAddress = "134 Long Lane"
Call theCustomers.add(aCustomer)
For Each aCustomer In theCustomers.Items
Sheets(1).Range("A1").Select
ActiveCell.value = aCustomer.Name
ActiveCell.Offset(0, 1).value = aCustomer.MainAddress
ActiveCell.Offset(1, 0).Select
Next aCustomer
The above code simply defines a "customer" variable and a "customers" variable; assigns three objects to the collection and then writes the name and address to a worksheet in the current workbook, using a "FOR EACH" loop.
Using the Collections Object Directly
It is possible to create a collection using the VBA collection class directly. The code below creates a collection called employees and assigns three instances of the custom object employees to it.
Sub TestEmployeesCollection()
Dim anEmployee As Employee
Dim i As Long
Set anEmployee = New Employee
anEmployee.Name = "Stephen Williams"
anEmployee.Rate = 500
anEmployee.HoursPerWeek = 50
Call Employees.add(anEmployee, anEmployee.Name)
Set anEmployee = New Employee
anEmployee.Name = "Kur Avon"
anEmployee.Rate = 50
anEmployee.HoursPerWeek = 50
Call Employees.add(anEmployee, anEmployee.Name)
Set anEmployee = New Employee
anEmployee.Name = "Bill Bailey"
anEmployee.Rate = 250
anEmployee.HoursPerWeek = 50
Call Employees.add(anEmployee, anEmployee.Name)
Set anEmployee = New Employee
anEmployee.Name = "Alexander Armstrong"
anEmployee.Rate = 250
anEmployee.HoursPerWeek = 50
Call Employees.add(anEmployee, anEmployee.Name)
For Each anEmployee In Employees
MsgBox anEmployee.Name & " Earns " & "£" & anEmployee.GetGrossWeeklyPay()
Next anEmployee
End Sub
Thanks. Your download will begin shortly.
Please help us
Share or create a link to this manual today!
Just follow these simple instructions...