Face to face / Online public schedule & onsite training. Restaurant lunch included at STL venues.
From £495 List price £650
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.
Attendance of our Microsoft Excel VBA course, or equivalent knowledge.
Including the understanding of the following:
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
Arguably, the most experienced and highest motivated trainers.
Training is held in our modern, comfortable, air-conditioned suites.
Modern-spec IT, fully networked with internet access
A hot lunch is provided at local restaurants near our venues:
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).
Available throughout the day:
Regular breaks throughout the day.
Contains unit objectives, exercises and space to write notes
Available online. 100+ pages with step-by-step instructions
Your questions answered on our support forum.
|
|
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
IPG Mediabrands
David Lanham,
Analyst
Great training, thanks a lot!
Excel VBA Advanced
Courtiers
David Nicholsby,
Trainee Analyst
Fantastic. Very informative, just what I needed to progress my VBA skills. Doug was excellent!
Excel VBA Advanced
Next date | Location | Price |
---|---|---|
Wed 7 Jan | Bloomsbury | £495 |
Wed 21 Jan | Online | £495 |
Tue 3 Feb | Limehouse | £495 |
Fri 20 Feb | Online | £495 |
Wed 4 Mar | Bloomsbury | £495 |
Mon 23 Mar | Online | £495 |
And 24 more dates...
Loading content...
Excellent
HSBC
Project Manager
Alexander Orlov
"I was looking for a specific training for my data analytical team to equip them with more tools to slice and dice the data, building more trust worthy sophisticated propensity models, using SQL, VBA and Excel. I came across with STL training and my research and expectations were match by 100%. Spot on! Great professionals! Highly recommended for all banking and financial professionals that are using Excel on BAU basis."
Tutorials and discussions on MS Office
MS Office tips to save you time
MS Office shortcut keys for all versions
Handy info on industry trends
Latest news & offers
Loading content...
Below are some extracts from our Excel training manuals.
Class modules allow you to create and use your own object types in your application. This implies the following;
Classes make your code:
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.
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.
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
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.
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
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.
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.
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 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 |
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.
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.
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
Call for assistance
We will call you back