Audience
This course is designed for those who have knowledge of the workings of Excel and who wish to extend its capabilities to automate frequently used tasks.
Prerequisites
Good working knowledge of Excel.
No previous programming experience is required.
Duration
3 days. Hands on.
Course Objectives
On completion of the course, delegates will understand:
- How to create and edit code procedures and Functions.
- How to use Events.
- How to provide and control user access.
- How to create and protect applications.
- How to use Excel objects to achieve results.
- How to create and use Custom Forms and dialogue boxes.
- How to access and manipulate external data.
Course Content
Visual Basic for Applications
Introduction.
Definitions.
Objectives.
Programming in Excel
Procedures.
Relative and Absolute References.
Availability of Procedures.
Personal Macro Workbook.
Editing Recorded Procedures
Viewing Code.
The Visual Basic Editor.
Reading Code.
Using Visual Basic Procedures.
Interactive Procedures.
Control Structures.
Using Help.
Customising Visual Basic.
User Defined Functions
Creating and using User Defined Functions.
Visual Basic Procedures
Using Procedures Together.
Calling Procedures.
Optional Arguments.
Passing Arguments.
Project Organisation.
Protecting Procedures.
Protecting Modules.
Visual Basic Objects
Using Objects.
Referencing and Using Properties.
Common Properties.
Using Methods.
Collections.
Object Browser.
Visual Basic Code
Declaring Variables and Arguments.
Data Types.
Function Data Types.
Assigning Objects to Variables.
Scope and Life of Variables.
Arrays.
Control Structures
Conditional Structures.
Repetition.
Nesting structures.
Testing and Debugging
Debugging Tools.
Calls Stack.
Watch Expressions.
Immediate Pane.
Error Handling
Error Handler Design.
Error-Handling Hierarchy.
Testing Error Handling.
Object Models
Object Model Overview.
Using Objects to Automate Tasks.
Application Objects
Properties.
Methods.
Events.
Data Access Objects
DAO Objects.
Accessing ODBC Data.
DAO with ODBCDirect.
Using ODBCDirect.
ActiveX Controls and Dialogue Boxes
Custom Dialogue Boxes.
Controls.
Optimising for Size and Speed
Optimisation Strategies.
User Access
Menus.
Toolbars.
Command Buttons.
Configuration Settings
Writing to the Registry.
Reading from the Registry.