Microsoft Excel Macro/VBA (part2) Duration: 1 Day

Course Overview

This 1 day course builds on the MS Excel Macro/VBA (part 1). Here where we look at topics such as Forms, error Handling and Office Automation. Prerequisites: Candidates should have an excellent working knowledge of Excel or should have attended MS Excel Macro/VBA (part 1) training day.

Working with Forms and Controls

• Understanding UserForms
• Using the Toolbox
• Working with UserForm Properties, Events, and Methods
• Understanding Controls
• Setting Control Properties in the Properties Window
• Working with the Label Control
• Working with the Text Box Control
• Working with the Command Button Control
• Working with the Combo Box Control
• Working with the Frame Control
• Working with Option Button Controls
• Working with Control Appearance
• Setting the Tab Order
• Populating a Control
• Adding Code to Controls
• Launching a Form in Code

Debugging Code

• Understanding Errors
• Using Debugging Tools
• Setting Breakpoints
• Stepping through Code
• Using Break Mode during Run mode
• Determining the Value of Expressions
• Using the Immediate Window

Handling Errors

• Understanding Error Handling
• Understanding VBA's Error Trapping Options
• Trapping Errors with the On Error Statement
• Understanding the Err Object
• Writing an Error-Handling Routine

MS Office Automation

• Overview of how Excel VBA can be used to control other programs.
• Setting references to other programs.
• Launching and terminating programs.
• Using “late binding” to make your code work with multiple versions of applications.
• Example of using Excel to control and manipulate MS Word.
• Using “SendKeys” to control applications that don’t support automation.