Microsoft Excel VBA Level 1
(1 day - £295, plus VAT)

Prerequisites

A good knowledge of all of Excel's basic and intermediate features and shortcuts.

Course Goals

To familiarise delegates with the core features of Microsoft Excel VBA. At the end of the training, delegates will be able to create and deploy basic macros.

Course Outline

Basics

Excel VBA overview
Understanding macro steps
Modifying macro steps
Storing values in variables
Adding comments to your code
Stepping through macros
Getting help on macros
Objects, properties and methods
About Excel objects

Range object

The Selection property
The Range property
Targetting cells
Counting the cells in a selection
Using the Offset statement
Retrieving the value of a cell
Retrieving a formula from a cell
Setting the value of cells

Using Visual Basic

Language structure
Using variables
Declaring variables
Data types
Making decisions
If statements
Looping structures

Interactivity

Assigning a macro to a menu
Assigning a macro to a toolbar
Assigning macros to other objects
Built-in dialogue boxes
Using form controls

Back to top

Microsoft Excel VBA Level 2
(2 days - £550, plus VAT)

Prerequisites

Good knowledge of Excel and familiarity with basics of VBA assumed.

Course Goals

To provide Excel VBA programmers with a more in-depth knowledge of Excel VBA objects: application, workbooks, worksheets, ranges, and user forms.

Course Outline

Excel object model

About classes and objects
Objects and collections
The hierarchy of objects
Properties, methods and events
The Locals Window
The Immediate Windo

Function procedures

Sub versus function
User-defined functions
Declaring a function
Calling a function
Private and public scope
Defining arguments
Optional arguments

The Application object

When to use
ScreenUpdating property
DisplayAlerts property
Setting CutCopyMode to false

Dialogue boxes

MsgBox function
InputBox function
Application.Dialogs

UserForms

Creating a userform
Displaying a userform
Adding Label controls
Adding a TextBox control
The CheckBox control
ComboBox and ListBox
Adding CommandButtons
Setting control properties

Workbooks and worksheets

The Workbooks collection
The Sheets collections
The Window objects
Workbook events

Ranges and names

Column and Row properties
CurrentRegion property
The Union method
The Intersect method
Using the Name property
Working with named range

 

Back to top

Microsoft Excel VBA Level 3
(2 days - £550, plus VAT)

Prerequisites

A good knowledge of all of Excel VBA basic and intermediate features. Experience of writing and deploying Excel macros.

Course Goals

To show experienced Excel VBA programmers how to develop Excel applications and work with VBA advanced objects and concepts.

Course Outline

Excel applications

Types of applications
Speeding up your applications
Turning calculation off and on
Controlling screen updating
Turning off alert messages and warnings
Naming conventions
Code commenting
Saving versions

Handling Events

What are events?
Event Parameters
Enabling Events
Workbook events
Worksheet events

Charts

Specifying charts
Specifying an embedded chart
Naming embedded charts
Creating charts
Objects that comprise a chart
The Chart Area—VBA Name: ChartArea
The Plot Area—VBA Name: PlotArea
The Data Series—VBA Name: Series
The Chart Axes—VBA Name: Axis
Data Labels
Chart Title, Legend, and Data Table
VBA Names: ChartTitle,
HasLegend, and HasDataTable
Trendlines and Error Bars
Chart types
Settings for 3d charts
Settings for pie charts
Using events with charts
Exporting charts as images

Pivot Tables

Creating pivot tables
Using the PivotTableWizard
Creating a pivot table from the pivot cache
Connecting to external data
Pivot table methods and properties
Pivotcache methods and properties

File manipulation

Working with text files
Opening Text Files in Excel
Writing a text file
The Dir function
File Exists
Looping Through Files

Working with menus

Creating a custom menu
Deleting and Creating the Custom Menu
Adding a Single Menu Item
Breaking Items into Groups
Adding a fly-out menu
Creating a custom toolbar
Deleting and Creating the Toolbar
Adding Buttons to the Toolbar
Adding Drop-downs to the Toolbar
Remembering a toolbar's position

Class modules

Creating a new class
Adding methods
Creating properties
Read-Only Properties
Defining enumerations
Using raiseevent
Destroying object

Back to top

Microsoft Excel VBA Intensive
(5 days - £1,095, plus VAT)

Prerequisites

Computer literacy: the ability to use a mouse and keyboard; familiarity with common computer software; the ability to open and save files.

Course Goals

If you need to master Microsoft Excel VBA programming in a hurry for an upcoming project, the Microsoft Excel VBA 1 Week Intensive Course allows you to take all three levels of Excel VBA training in a one-week course.

Course Outline

The course comprises the following modules:

Microsoft Excel VBA level 1 (1 day)

Microsoft Excel VBA Level 2 (2 days)

Microsoft Access VBA (2 days)

Book a Microsoft Excel VBA Course

Excel VBA Level 1

Excel VBA Level 2

Excel VBA Level 3

Excel VBA 1 Week Intensive