Excel 2002 (XP): VBA Programming

Course Overview

This course has been designed for advanced users who want to make use of the more powerful facilities of MS Excel XP made available by VBA. This course explores the VBA language, and delegates will be encouraged to write programs to ensure that future editing, amending and updating can be carried out with minimum effort.

Skills Gained

• Record simple macros to automate repetitive tasks
• Use Visual Basic to amend and write macros
• Work with object properties, methods and events
• Use control structures to add logic to code
• Interact with users by adding user forms and message boxes
• Identify and handle errors in code

Prerequisites

Delegates must use Excel constantly with a very good understanding of core features and functions of Excel; ideally they will have attended Intermediate/Advanced Excel courses or used it competently in a business environment.!

Course Outline

• Macros
• Recording macros
• Editing macros
• Running and debugging macros
• Running macros from a button
• Running macros from a keystroke
• Relative and absolute references in Excel macros
• Using the Visual Basic Editor
• Understanding the Visual Basic Environment
• Using the VBE window
• Examining the project explorer
• Visual basic modules
• Editor options
• Editor views
• Examining the object browser
• Objects, methods, properties
• Understanding Object Oriented Programming
• The excel object model
• Using methods
• Modify the properties of an object
• Programming basics
• The structure of procedures
• Understanding data types and variables
• Understanding scope
• Calling a procedure
• Control structures
• Using control structures
• With statements
• Looping structures:- For loops, Do.loops
• If statements
• Using Select Case statements
• Variables
• Understanding data types and variables
• Declaring variables
• Variable scope
• Constants and arrays
• Interaction
• Creating message boxes
• Creating input boxes
• Creating user forms
• Working with form controls and the control toolbox
• Adding code to forms
• Error Trapping
• Errors in code
• Using debugging techniques
• Setting a breakpoint
• Adding a watch expression
• Using step into
• Working with the immediate window
• Viewing variable and array values in break mode
• Handling errors
• Writing error handling code