- Level 1
- Level 2
- Level 3
- Booking
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
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
Book a Microsoft Excel 2007 Course