Introduction

The advantages of VBA

• An easy-to-use and versatile language
• Integrated with off-the-shelf products
• Minimises development time and costs

Macros: the first step

• Recording and executing macros
• Limitations
• Enhancing with VBA code

Getting to Know the VBA Development Environment

The code design tools

• Project Explorer
• Help system
• Code Window
• IntelliSense
• Properties Window
• Object Browser

The debugging tools

• Code stepping
• Immediate Window
• Breakpoints

Writing VBA Code

Event-driven programming

• The role of events in Windows
• How events are triggered
• Responding to events through event procedures

Putting objects to work

• Properties
• Methods
• Events
• Collections
• Using With...End With

Storing data in variables

• How and when to declare variables
• Selecting data types
• Fixed and dynamic arrays
• Constants
• The scope and lifetime of variables

Conditional branching

• If...Then...Else
• Select...Case

Looping through code

• Do...Loop, While and Until
• For...Next and For Each...Next

Building procedures

• Creating Sub and Function procedures
• Calling procedures
• Passing arguments to procedures

Empowering VBA through the Application Object Models

Understanding object model structures

• Exploring the hierarchy using the Object Browser and Help system
• Referencing specific objects

Writing application-specific procedures

• Key objects within Excel, Word and Outlook
• Creating templates and add-ins

Exploiting the power of Automation

• Controlling one Office application while working in another application
• Accessing data in databases

Building Intuitive User Interfaces

Using intrinsic dialogues

• Communicating with the user through the message box
• Gathering user information with the input box

Creating customised dialogues with UserForm objects

• Command buttons
• List and Combo boxes
• Option buttons
• Check boxes
• Labels
• Text boxes
• Frames
• Adding more functionality with advanced ActiveX controls

Modifying menus and toolbars

• Simplifying user interaction with the CommandBar object
• Adding and removing CommandBarControl objects

Providing a Safe and Secure Environment

Handling runtime errors

• The On Error GoTo structure
• Classifying errors with the Err object
• Continuing execution with Resume, Resume Next or Resume label

Implementing security

• The dangers of macro viruses
• Macro security levels in Office
• Applying digital signatures to macros
• Password protecting your VBA code