Course Details
Course Code: VBA-P; Instructor-led
Audience
Any of the Microsoft office users that already knows fundamentals VBA programming. This is not the first VBA course for non-programmer
Prerequisites
Before attending this course, students must be able to demonstrate the following skills:
- Familiarity with the general operations of Microsoft Windows operating system.
- Basic knowledge and skill in using Microsoft office application, such as MS-Word, MS-Excel, etc.
- Basic VBA programming
Training Tools:
- MS Windows XP Professional SP3 under Virtual PC 2007 environment
- MS Office 2003/2007 (Depends on the target audience)
- Host PC: Any PC and OS that support Virtual PC 2007 with at least additional 1 Giga Byte RAM for Virtual PC.
Methodology
This program will be conducted with interactive lectures, PowerPoint presentation, discussion and practical exercise.
Course Objectives
This course is aimed to introduce some of the challenging aspects of VBA programming which is not normally known by new VBA programmer
At the end of the course, participant will be able to write more advance VBA code.
Outlines
Module 1: Building Blocks In VBA
- Modular and Event Driven Concepts
- Modules
- Class Model
- User Form
- Document Model
Module 2: Parameter Passing Mechanisms
- Parameter Passing Mechanisms
- Optional Parameter
- Named Parameter
- Variable Parameter with ParamArray
Module 3: Subroutines, Functions, Event Handlers and Macro
- When to use which?
- Return value from function
- Termination
- Macro: A special form of Sub
- Event handler: A special form of Sub
Module 4: Advanced Array Handling
- Multi-Dimensional Array
- LBound and UBound functions
- Redim
- Redim Preserve
Module 5: Advanced Types
- Variant
- Using Type
- Object
- Predefined Constants
Module 6: Options
- Option Base
- Option Explicit
- Option Compare
- Option Private
Module 7: Error Handling
- On Error
- On Error Goto
- On Error Resume Next
Module 8: Special Constructs
- For Each
- Abnormal termination with Exit keyword
Module 9: User Form
- The purpose
- How to Declare?
- How to call it?
Module 10: Class Module
- The Purpose
- Properties Declaration
- How to use it?
Module 11: Document Model
- Dealing with events
- Event Parameters
Module 12: Advanced Topics
- Using Declare keyword to extend VBA ability using external DLL
- Advanced Techniques
- Security Concerns
- Common Pitfalls
- Good Practices
