98.6% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Microsoft VBA Excel Course
Face to face public schedule & onsite training. Restaurant lunch included at STL venues.
Designed for Excel 365, 2019, 2016, 2013
Who is this course for?
This course is suitable for advanced users of Microsoft Excel looking to further increase their knowledge of VBA.
A beginners knowledge of Microsoft Excel VBA is required, or completion of our Excel VBA Introduction course.
- By the end of the course the delegate will be fully competent in the fundamentals of VBA, including working with functions, understanding objects, using expressions, variables and intrinsic functions.
- This includes mastering forms and controls, and building user defined functions.
- There will also be discussion of techniques for improving and optimizing code.
Storing information with variables
How and why you should declare variables
Determining which data type to use
Public v Private scope
Using variables to trap errors
Using the Locals window to observe variables
Writing your own User Defined Functions (UDFs)
Working with multiple arguments
Using your function in Excel
Message Boxes and Input Boxes
Displaying a message
Adding a yes / no user choice
Getting feedback from the end user
Defining VBA's Error Trapping Options
Capturing Errors with the On Error Statement
Determining the Err Object
Coding an Error-Handling Routine
Using Inline Error Handling
Creating custom dialogue boxes with UserForms
Setting UserForm properties, events and methods
Using text boxes, command buttons, combo boxes and other controls
Applying code to controls
How to launch a form in code
Prices & Dates
What you get
"What do I get on the day?"
Training is held in our modern, comfortable, air-conditioned suites.
Modern-spec IT, fully networked with internet access
Lunch is provided at a local restaurant or pub. Browse the sample menus:
Breaks and timing
Courses start at 9:30am.
Please aim to be with us for 9:15am.
Joining information (how to get to our venues)
Available throughout the day:
- Hot beverages
- Clean, filtered water
Regular breaks throughout the day.
Contains unit objectives, exercises and space to write notes
Available online. 100+ pages with step-by-step instructions
24 months access to Microsoft trainers
Training formats & Services
Product Stream Engineer
Time saving hints and tips will save me so much time. The practical examples throughout will help me remember what I have learnt and give me examples to reference in future to refresh my memory. We were also shown where to look to work out how to do new things and solve problems.
Excel VBA Intermediate
National Physical Laboratory
Senior Research Scientist
This has been one of the most useful courses that I have attended, I wish I had gone on it years ago. Jens was a fantastic trainer and made the course interesting the whole way through. I have already recommended it to my colleagues.
Excel VBA Intermediate
Ultra Electronics Controls
Very satisfied. Another session where I've learnt a lot. The tutor, Jens, is fantastic!
Excel VBA Intermediate
Training manual sample
Below are some extracts from our free Excel training manuals.
Excel VBA Intermediate
Unit 3 Understanding Objects
An object is an element of an application that can be accessed and manipulated using Visual Basic. Examples of objects in Excel are worksheets, charts and ranges.
Objects are defined by lists of Properties, and Methods. Many also allow for custom sub-procedures to be executed in response to Events.
The term Class refers to the general structure of an object. The class is a template that defines the elements that all objects within that class share.
Properties are the characteristics of an object. The data values assigned to properties describe a specific instance of an object.
A new workbook in Excel is an instance of a Workbook object, created by you, based on the Workbook class. Properties that define an instance of a Workbook object would include its name, path, password, etc.
Methods represent procedures that perform actions.
Printing a worksheet, saving a workbook selecting a range are all examples of actions that can be executed using a method.
Many objects can recognize and respond to events. For each event the object recognizes you can write a sub procedure that will execute when the specific event occurs.
A workbook recognizes the Open event. Code inserted into the Open event procedure of the workbook will run whenever the workbook is opened.
Events may be initiated by users, other objects, or code statements. Many objects are designed to respond to multiple events.
Examining the Excel Object Hierarchy
The Excel Object Module is a set of objects that Excel exposes to the development environment. Many objects are contained within other objects. This indicates a hierarchy or parent-child relationship between the objects.
The Application object represents the application itself. All other objects are below it and accessible through it. It is by referencing these objects, in code, that we are able to control Excel.
Objects, their properties and methods are referred to in code using the "dot" operator.
Some objects in Excel are considered global. This means they are on top of the hierarchy and can be referenced directly. The Workbook object is a child object of the Excel Application object. But since the Workbook object is global you don’t need to specify the Application object when referring to it.
Therefore the following statements are equal:
Some objects in the Excel Object model represent a Collection of objects. A collection is a set of objects of the same type.
The Workbooks collection in Excel represents a set of all open workbooks. An item in the collection can be referenced using an index number or its name.
To view the entire Excel Object model:
- Open the Help window
- Select the Contents tab
- Expand Programming Information
- Expand Microsoft Excel Visual basic Reference
- Select Microsoft Excel Object Model.
The following illustration shows a portion of the Excel object hierarchy. Most projects will only use a fraction of the available objects.
A collection is a set of similar objects such as all open workbooks, all worksheets in a workbook or all charts in a workbook.
Many Excel collections have the following properties:
Refers to the application that contains the collection
An integer value representing the number of items in the collection.
Refers to a specific member of the collection identified by name or position. Item is a method rather than a property
Refers to the object containing the collection
Some collections provide methods similar to the following:
Allows you to add items to a collection
Allows you to remove an item from the collection by identifying it by name or position.
Referencing Objects in a Collection
A large part of programming is referencing the desired object, and then manipulating the object by changing its properties or using its methods. To reference an object you need to identify the collection in which it’s contained.
The following syntax references an object in a collection by using its position. Since the Item property is the default property of a collection there is no need to include it in the syntax.
CollectionName(Object Index Number)
The following syntax refers to an object by using the object name. Again the Item property is not necessary:
Worksheets("Purchases By Month")
Using the Object Browser
The Object Browser is used to examine the hierarchy and contents of the various classes and modules.
The Object Browser is often the best tool to use when you are searching for information about an object such as:
- Does an object have a certain property, method or event
- What arguments are required by a given method
- Where does an object fit in the hierarchy
To access the Object Browser:
In the Visual Basic Editor, do one of the following:
- Open the View menu
- Select Object Browser OR
- Press F2 OR
- Click the Object Browser icon.
The Object Browser dialog box appears.
The following icons and terms are used in the Object Browser:
Indicates a Class (Eg Workbook, Worksheet, Range, Cells)
Is a value representing an attribute of a class (Eg. Name, Value)
Is a procedure that perform actions (Eg. Copy, Print Out, Delete)
Indicates an event which the class generates (Eg Click, Activate)
Is a variable with a permanent value assigned to it (Eg vbYes)
Is a set of constants
Is a standard module
To search for an object in the Object Bowser:
- Type in the search criteria in the Search Text box
To close the Search pane:
Working with Properties
Most objects in Excel have an associated set of properties. During execution, code can read property values and in some cases, change them as well.
The syntax to read an object’s property is as follows:
The syntax to change an object’s property is as follows:
ObjectReference.PropertyName = expression
ActiveWorkbook.Name = "Quarterly Sales 2006"
The With Statement
The With statement can be used to work with several properties or methods belonging to a single object without having to type the object reference on each line.
The With statement helps optimize the code because too many "dots" in the code slows down execution.
The syntax for the With statement is as follows:
You can nest With statements if needed.
Make sure that the code does not jump out of the With block before the End With statement executes. This can lead to unexpected results.
Working With Methods
Many Excel objects provide public Sub and Function procedures that are callable from outside the object using references in your VB code. These procedures are called methods, a term that describes actions an object can perform.
Some methods require arguments that must be supplied when using the method.
The syntax to invoke an object method is as follows:
Workbooks.Open "Sales 2006"
hen calling procedures or methods that have arguments you have two choices of how to list the argument values to be sent.
Values can be passed by listing them in the same order as the argument list. This is known as a Positional Argument.
Alternatively you can pass values by naming each argument together with the value to pass. This is known as a Named Argument. When using this method it is not necessary to match the argument order or insert commas as placeholders in the list of optional arguments
The syntax for using named arguments is as follows:
The example shows the PrintOut method and its syntax:
The statements below show both ways of passing values when calling the PrintOut method. The first passes by Position, the second by Naming:
Workbooks("Quarterly Sales 2006").PrintOut (1,2,2, , , ,True)
Workbooks("Quarterly Sales 2006").PrintOut From:=1, To:=2, Copies:=2, Collate:=True
An event procedure is a sub procedure created to run in response to an event associated with an object. For example run a procedure when a workbook opens.
Event procedure names are created automatically. They consist of the object, followed by an underscore and the event name. These names cannot be changed. Event procedures are stored in the class module associated with the object for which they are written.
The syntax of the Activate Event procedure is as follows:
Private Sub Worksheet_Activate()
Creating An Event Procedure
To create an Event Procedure:
- Display the code window for the appropriate class module
- Select the Object from the Object drop-down list
- Select the event from the Procedure drop-down list
- Enter the desired code in the Event Procedure