Microsoft Excel 2003 Visual Basic

2 days

Course Overview

Learn Excel Visual Basic for Applications (VBA) in this two day course, which covers recording macros through advanced form design. This course will show you how to write object-oriented programs, how to create program control structures and how to handle errors effectively.

Course Objective

Use the power of the Visual Basic language to harness and to automate Excel worksheets and applications. Learn how to make Excel do the most for your business or organization.

Target student

Advanced users of Microsoft Excel will receive the most benefit from this course. The user who works with large amounts of data in need of automation or who must collaborate with others in a team may have need to learn how to program features and functions to increase data access and functionality.

Prerequisites

Thorough knowledge of Excel is essential for success in this course. The Systematix course Microsoft Office Excel 2003: Level 3 Advanced or equivalent knowledge or experience is highly recommended.

Course Outline

Getting started with Macros

  • Recording and Modifying Macros
  • Assigning Macros to Toolbar, Buttons, Shortcut key

Visual Basic for Applications Environment

  • Visual Basic Editor and its options
  • Component Management
  • Shortcuts

Introduction to Procedural Programming

  • Procedures: Functions and Sub-routines
  • Data Types

Excel Model Object

  • Application Object
  • Workbook Object and Workbooks Collection
  • Worksheet Object and Worksheets Collection
  • ThisWorkbook, ActiveWorkbook, ActiveCell, ….
  • Selection Object
  • Range Collection
  • Cells Collection
  • Using Status Bar
  • Screen Updating and Optimizing
  • Timer and Time Measuring
  • Events Handling in VBA

Control Flow

  • If …Then….Elseif….Else….Endif
  • Case
  • Do…Loop , While
  • For… Next
  • Exit
  • VBA and Sheet Data Exchange (Cells, Range Methods)
  • Getting and Printing Data from Users (InputBox, MsgBox)

Variable Declaration

  • Scope and LifeTime of Variables and Objects
  • Operators and Their Priorities
  • Modules Options
  • Creating Worksheet Functions
  • Objects, Classes, Methods and Properties
  • Locking Project for Viewing

Procedures, parameters, variables

  • Passing parameters by reference and by value (ByRef or ByVal)
  • Optional and default parameters
  • Passing an array of arguments to a procedure

Debugging

  • Run Time, Design Time, and Break Mode
  • Breaks and Watches
  • Local Window
  • Immediate Window

Error Handling

  • Errors Types
  • On Error GoTo
  • On Error Resume Next

VBA Forms

  • Creating and Using Forms
  • Invoking Forms in VBA Code
  • Using VBA Forms in Worksheet

Arrays * (Any items marked with * will be covered if time allows.)

  • Dynamic arrays
  • Arrays optimization
  • Multidimensional arrays
  • Arrays and objects

Collections *

  • Creating and using collections
  • Adding and remove elements
  • Using index and key

Dealing with files *

  • Reading text files
  • Using CSV files