Microsoft Excel VBA Development
Course Duration: 3 Days
 Printer-friendly
version
Course Objective
By the end of the course, delegates
will be able to:
- Develop Excel spreadsheet applications using various
on-sheet controls and dialog boxes by programming in Visual Basic
for Applications
- Adopt a set of standards for application design
- Debug Excel applications
that have errors in them
- Devise standard functions and procedures
to improve productivity in the workgroup
Delegate Profile
Experienced Excel
users seeking to enhance their Excel knowledge and who wish to
develop automated Excel solutions
for themselves and
other users.
Pre-Requisites
Attendance on our Excel Expert course or equivalent knowledge is assumed.
Structure
Each delegate has the exclusive use of a
PC and training includes extensive hands-on activity.
Course Content
Brief discussion
Macros in general
General points on developing with VBA
Recording
Using the recorder to create a macro
Setting the recorder options
Viewing code
Running
Executing the macro via the menu
Setting short-cut keys
Using the Visual Basic toolbar
Attaching macros to custom tool buttons
Attaching Macros to push buttons & objects
Working with code
Editing the VBA code
Adding comments
Removing unnecessary code
Procedures
Different types
Structure
Creating function procedures
Building custom functions
to assist calculations
Using function procedures
Creating sub-procedures
Mixing recorded and written
statements
Using keywords
Syntax
With clauses
Simplifying code using With
Simple (but effective) user interaction
Displaying
messages
Receiving input from users
Sub-routines
Calling one macro from another
Loops
Using if-then-else loops
Using for loops
Object model
Understanding the object model
Including collections, objects, properties and methods
Declaring variables
Identifying named variables
Errors and debugging
Using step mode
Using the watch and immediate panes
On-sheet controls
Using the forms toolbar
Adding drop down lists, option groups and buttons to worksheets
Setting object properties
Dialog boxes
Working with dialog sheets
Designing dialog boxes
Adding dialog controls
Showing the dialog box
Using a macro to set dialog defaults
Validating returned values
Creating a custom interface
Building custom toolbars
Attaching to workbook
Building custom menus
Automatic macros
Running macros automatically by opening/closing
files
Event macros
Running macros when specific events are met  Back
to the top
|
