Microsoft Excel 2003 - Visual Basic Application

Course Overview

Microsoft Excel 2003 - Visual Basic Application has been designed for those who wish to understand Visual Basics for Excel 2000 and how to use it to automate spreadsheets. Upon completion of this course you will be able to understand the principles of VBA; understand terminology used with VBA; create VBA procedures and User defined functions; create and use Custom Dialog Boxed; work with Workbook, Worksheet and Range Objects; and Test and debug a macro in Excel.

Delivery

Classroom

Duration

Minimum 3 hours

Schedule

Group bookings or 1:1 training only

Prerequisite

Participants must have attended an Advanced Excel course or have an extensive amount of experience using Excel.

Course Content

Introduction to Visual Basic

• The VBA Environment
• The Visual Basic Editor
• Module Window
• Project Explorer
• Properties Window
• Recording a Macro
• Recording a Macro
• Naming a Macro
• Running a Macro
• Creating a button on a worksheet
• Assigning a macro to a button

The Visual Basic Editor

• Viewing and Editing Code
• Switching between Excel and the VBA Editor

Objects, Properties and Methods

• Defining an Object
• Properties of Objects
• Methods associated with Objects
• The Object Browser

Building a Macro

• Recording a Macro for each task
• Testing a Macro
• Editing a Macro
• The With Statement

Writing Procedures in VBA

• Creating a VBA Procedure
• Looping and Decision Statements

Testing and Debugging a Macro

• The Debug Toolbar
• Stepping through a Macro
• Displaying the Value of Expressions
• Setting Break Points

 Absolute and Relative Recording

• The Difference between Absolute and Relative Recording
• Switching between Relative and Absolute Recording
• Choosing the appropriate recording method for a given task

 Getting User Input

• The InputBox function
• The MsgBox function
• Creating a User Form
• Assigning Macros to Command Buttons

 Creating Custom Functions

• Functions and Procedures
• Writing a Function in VBA
• Using Custom Functions in a Workbook
• Using String Functions to build Character Strings