Duration
2 - 3 days
Objectives
This course builds on basic knowledge and experience of VBA for Excel, equivalent to the introductory Excel VBA course. The aim is to enable delegates to develop substantial Excel/VBA applications, for analysis, modelling or data management.
The course introduces a range of advanced VBA language features, and also covers the design of applications, and approaches to the testing of code. The VBA language features covered are equally applicable to Access, Word, etc.
Data integration with Access is covered in a separate 1 day module which can be appended to this course.
Prerequisites
Delegates should have attended an introcutory Excel VBA course, and have some hands-on experience of using VBA in an Excel context.
Topics
The following topics cover 3 days; a selection can be chosen for a 2-day course.
- Arrays
- Parameters and Variables
- Optional parameters
- Parameter arrays
- ByRef and ByVal
- Constants
- Building file paths
- Using a Constants module
- Using a Constants add-in
- User Defined Types
- Enumerations
- Record types
- Class modules
- Using Classes
- Data structures
- Worksheet cursors
- Interfaces
- Declaring and implementing an interface
- Using interfaces to share code
- Handling polymorphic data
- Events
- Declaring and raising events
- Handling events
- Handling Application events
- Model-View pattern
- Input/Output
- Accessing files and folders
- Reading and writing text files
- Testing
- Writing test modules
- Testing Functions
- Testing worksheets
- Testing Classes
- Performance
- Memory usage
- Data copying
- Worksheet vs. VBA functions
- Delivery
- Templates
- Add-ins
- Security
- Customising the 2007 Ribbon
- Ribbon Configuration
- Buttons
- Combo boxes
|
