98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsMicrosoft VBA Excel Course
Intermediate level
Face to face / Online public schedule & onsite training. Restaurant lunch included at STL venues.
Designed for Excel 365
(260 reviews, see all 100,021 testimonials) |
From £300 List price £350
- 1 day Instructor-led
- Courses never cancelled
- Restaurant lunch
Syllabus
Who is this course for?
This course is suitable for advanced users of Microsoft Excel looking to further increase their knowledge of VBA.
Prerequisites
A good working knowledge of the topics covered in the Microsoft Excel VBA Introduction course is required, or completion of our Excel VBA Introduction course.
Benefits
- 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.
Course Syllabus
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
Creating functions
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
Handling Errors
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
Drawing UserForms
Setting UserForm properties, events and methods
Using text boxes, command buttons, combo boxes and other controls
Formatting controls
Applying code to controls
How to launch a form in code
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
National Physical Laboratory
Olivia Whiteside,
Assisstant Research Scientist
Extremely pleased with the delivery, pace and content of this course. It exceeded my expectations.
The only thing I could suggest is a bit more of an introduction to coding. I have previous experience coding in C but I feel without this recent knowledge I would not have been able to keep up as well with the understanding of the VBA code writing.
Excel VBA Intermediate
Workman LLP
Gemma Bowles,
HR Administrator
Everything detailed on the course was covered, it just didn't seem to have an overarching feel which is what I'm used to on courses. But that may be becuase I didn't do the basic course. We went into depth on a few key areas, so maybe that is the standard for intermediate courses. I could follow everything, I just wasn't always sure where I would use it. But again, that might to do with my job vs other attendees who use excel/coding all day long.
Excel VBA Intermediate
University of York
Matt Walton,
Research Fellow
Great simple guide to the core concepts to take forward to the advanced module. Has helped me understand the syntactic underpinnings of much of the third-party VBA code I deal with on a daily basis in a way that will help me write my own from scratch.
Excel VBA Intermediate
Training manual sample
Below are some extracts from our 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.
Defining Objects
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
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
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.
Events
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.
Application.ActiveWorkbook.SaveAs "Employees.xls"
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:
Application.ActiveWorkbook.SaveAs "Employees.xls
ActiveWorkbook.SaveAs "Employees.xls"
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.
Defining Collections
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:
Application | Refers to the application that contains the collection |
Count | An integer value representing the number of items in the collection. |
Item | Refers to a specific member of the collection identified by name or position. Item is a method rather than a property |
Parent | Refers to the object containing the collection |
Some collections provide methods similar to the following:
Add | Allows you to add items to a collection |
Delete | 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)
Workbooks.Item(1)
Workbooks(1)
Charts(IntCount)
The following syntax refers to an object by using the object name. Again the Item property is not necessary:
CollectionName(ObjectName)
Workbooks("Employees")
Worksheets("Purchases By Month")
Sheets("Total Sales")
Charts("Profits 2006")
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:
Class | Indicates a Class (Eg Workbook, Worksheet, Range, Cells) | |
Property | Is a value representing an attribute of a class (Eg. Name, Value) | |
Method | Is a procedure that perform actions (Eg. Copy, Print Out, Delete) | |
Event | Indicates an event which the class generates (Eg Click, Activate) | |
Constant | Is a variable with a permanent value assigned to it (Eg vbYes) | |
Enum | Is a set of constants | |
Module | Is a standard module |
To search for an object in the Object Bowser:
- Type in the search criteria in the Search Text box
- Click
To close the Search pane:
- Click
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:
ObjectReference.PropertyName
ActiveWorkbook.Name
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:
With ObjectName
End WithWith ActiveWorkbook
.PrintOut
.Save
.Close
End With
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:
ObjectReference.method [argument]
Workbooks.Open "Sales 2006"
Range("A1:B20").Select
Selection.Clear
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:
Argumentname:= value
The example shows the PrintOut method and its syntax:
Sub PrintOut([From],[To],[Copies],[Preview],[ActivePrinter],[PrintToFile],[Collate],[PrToFilename])
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
Event Procedures
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
Thanks. Your download will begin shortly.
Please help us
Share or create a link to this manual today!
Just follow these simple instructions...