98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsExcel VBA Advanced Training Courses
Face to face / Online public schedule & onsite training. Restaurant lunch included at STL venues.
Designed for Excel 365
From £495 List price £650
- 1 day 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 and Outlook
- The course will give the delegates a good understanding of how arrays can be used to handle large datasets, optimising VBA codes and execution speed.
Course Syllabus
The Excel Object Model
Exploring the Range object in detail
The versatile CurrentRegion object
Working with collections of Workbooks and Worksheets
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
PivotTables & Charts
Manipulating Charts through VBA
Manipulating PivotTables through VBA
Linking with Office
Connecting to other Office applications
Working with other Office application
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
Online 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
Legal & General
Francesco Lanni,
Analyst
More exercise on array and in-memory coding
More handling large dataset and real life examples
One additional day devoted to exercise only would be a great addition to the advanced course
Excel VBA Advanced
Equifax
Jennifer Atherton,
Analyst
Happy with the course, I have no improvements.
However, we use quarterly reports which use data to generate charts - could have been useful to work through (although we are self taught on how to do these)
Excel VBA Advanced
CUBIC
Kaye Clark,
Asset Performance Manager
Jens is a most enthusiastic and knowledgeable trainer. He was happy to help with any query and made understanding the modules easy.
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...