Microsoft Office Excel 2007: Programming Using Visual Basic for Applications

Code: 50432
Course duration: 3 days
Price:

50432: Excel 2007 Programming Using Visual Basic for Applications (3 Days)

About this Course

This three-day instructor-led course provides students with the knowledge and skills to use Microsoft Visual Basic for Applications to control Microsoft Excel 2007 programmatically. The course serves as an introduction to programming using the Visual Basic programming language.

Audience Profile

This course is intended for experienced Excel users who are interested in creating code to automate tasks in Excel.

At Course Completion

After completing this course, students will be able to:

  • Record and edit macros.
  • Use the Visual Basic Editor.
  • Create and call sub and function procedures.
  • Understand objects, properties, methods, and events.
  • Use the Object Browser.
  • Work with variables and understand data types.
  • Use intrinsic functions.
  • Work with control-of-flow structures.
  • Design UserForms and work with controls.
  • Control PivotTables programmatically.
  • Use debugging tools.
  • Add error handling to code.

Course Outline

Module 1: Getting Started

This module explains how to record and run macros. It also describes the Visual Basic Editor and how to use Visual Basic Help.

Lessons

  • Introducing Visual Basic for Applications
  • Displaying the Developer Tab in the Ribbon
  • Recording a Macro
  • Saving a Macro-Enabled Workbook
  • Running a Macro
  • Editing a Macro in the Visual Basic Editor
  • Understanding the Development Environment
  • Using Visual Basic Help
  • Closing the Visual Basic Editor
  • Understanding Macro Security

Lab : Getting Started

  • Displaying the Developer Tab in the Ribbon
  • Recording a Macro
  • Saving a Macro-Enabled Workbook
  • Running a Macro
  • Editing a Macro in the Visual Basic Editor
  • Understanding the Development Environment
  • Using Visual Basic Help
  • Closing the Visual Basic Editor
  • Creating a Trusted Location (Optional)
  • Introduction to the Course Exercises

After completing this module, students will be able to:

  • Record and run a macro.
  • Edit a macro in the Visual Basic Editor.
  • Use Visual Basic Help.

Module 2: Working with Procedures and Functions

This module explains how to create modules, sub procedures, and function procedures. It also describes how to call procedures and work with the Code Editor.

Lessons

  • Understanding Modules
  • Creating a Standard Module
  • Understanding Procedures
  • Creating a Sub Procedure
  • Calling Procedures
  • Using the Immediate Window to Call Procedures
  • Creating a Function Procedure
  • Naming Procedures
  • Working with the Code Editor

Lab : Working with Procedures and Functions

  • Creating a Standard Module
  • Creating a Sub Procedure
  • Using the Immediate Window to Call Procedures
  • Creating a Function Procedure
  • Calling a User-Defined Function from Excel
  • Working with the Code Editor

After completing this module, students will be able to:

  • Create modules, sub procedures, and function procedures.
  • Write code to call a procedure.
  • Call a procedure from the Immediate Window.
  • Work with the Code Editor.

Module 3: Understanding Objects

This module explains Excel objects and object properties, methods, and events. It details how to work with objects in code and how to use the Object Browser.

Lessons

  • Understanding Objects
  • Navigating the Excel Object Hierarchy
  • Understanding Collections
  • Using the Object Browser
  • Working with Properties
  • Using the With Statement
  • Working with Methods
  • Creating an Event Procedure

Lab : Understanding Objects

  • Understanding Collections
  • Using the Object Browser
  • Working with Properties
  • Using the With Statement
  • Working with Methods
  • Working with Events

After completing this module, students will be able to:

  • Refer to Excel objects programmatically.
  • Use the Object Browser.
  • Write code to read and change properties of an object.
  • Use the With…End With statement.
  • Write code to call a method.
  • Create an event procedure.

Module 4: Using Expressions, Variables, and Intrinsic Functions

This module explains how to name, declare, assign values to, and use variables. It also describes the use of user-defined constants and Visual Basic intrinsic constants. The use of Message Boxes, Input Boxes and object variables is also covered.

Lessons

  • Understanding Expressions and Statements
  • Declaring Variables
  • Understanding Data Types
  • Working with Variable Scope
  • Using Intrinsic Functions
  • Understanding Constants
  • Using Intrinsic Constants
  • Using Message Boxes
  • Using Input Boxes
  • Declaring and Using Object Variables

Lab : Using Expressions, Variables, and Intrinsic Functions

  • Declaring Variables Explicitly
  • Displaying the Compile Project Command Button
  • Declaring Variables
  • Using Intrinsic Functions
  • Using Message Boxes
  • Using Input Boxes
  • Declaring and Using Object Variables

After completing this module, students will be able to:

  • Declare variables.
  • Assign values to variables.
  • Use intrinsic functions.
  • Use the MsgBox and InputBox functions.
  • Use intrinsic constants.
  • Declare and set object variables.

Module 5: Controlling Program Execution

This module explains how to use decision structures to control program flow. It also covers how to use looping structures to repeat a block of code.

Lessons

  • Understanding Control-of-Flow Structures
  • Working with Boolean Expressions
  • Using the If...End If Decision Structures
  • Using the Select Case...End Select Structure
  • Using the Do...Loop Structure
  • Using the For...To...Next Structure
  • Using the For Each...Next Structure
  • Guidelines for use of Control-of-Flow Structures

Lab : Controlling Program Execution

  • Working with the If…Then...End If Structure
  • Using the If…Then…Else…End If Structure
  • Using the If…Then…ElseIf…End If Structure
  • Using the Select Case…End Select Structure
  • Using the Do…Loop Structure
  • Finishing the GetNewInventory Procedure
  • Using the For…To…Next Structure
  • Using the For Each…Next Structure

After completing this module, students will be able to:

  • Understand Boolean expressions used in program flow structures.
  • Use comparison operators and logical operators to build expressions.
  • Use If decision structures.
  • Use the Select Case decision structure.
  • Use the Do … Loop looping structure.
  • Use For … Next looping structures.

Module 6: Working with Forms and Controls

This module explains how to create a UserForm. It describes several controls and control properties. Creating event procedures for controls is also covered.

Lessons

  • 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

Lab : Working with Forms and Controls

  • Adding a Userform to a Project
  • Using 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 Forms from Procedures

After completing this module, students will be able to:

  • Create a Userform.
  • Add controls to a Userform.
  • Position, size, align, and group form controls.
  • Set the tab order of controls on a form.
  • Add list items to a Combo Box control.
  • Create event procedures for controls and add code to set control properties.
  • Write code to launch a Userform in a procedure.

Module 7: Working with the PivotTable Object

This module explains how to work with PivotTable objects using VBA code. It also shows how to assign a macro to the Quick Access toolbar.

Lessons

  • Understanding PivotTables
  • Creating a PivotTable Using Worksheet Data
  • Working with the PivotTable Objects
  • Working with the PivotFields Collection
  • Assigning a Macro to the Quick Access Toolbar

Lab : Working with the PivotTable Object

  • Creating a PivotTable Using Worksheet Data
  • Working with the PivotTable Objects
  • Working with the PivotFields Collection
  • Review – Completing the Run Reports Form
  • Assigning a Macro to the Quick Access Toolbar

After completing this module, students will be able to:

  • Create a PivotTable using the Excel interface.
  • Create a PivotTable in code.
  • Assign Page, Row, Column, and Data fields for a PivotTable programmatically.
  • Assign a macro to the Quick Access toolbar.

Module 8: Debugging Code

This module explains the debugging tools in the Visual Basic Editor and their use. It also describes break mode, how to set breakpoints, and how to determine the value of code expressions while in break mode.

Lessons

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

Lab : Debugging Code

  • Setting Breakpoints
  • Stepping through Code
  • Determining the Value of Expressions

After completing this module, students will be able to:

  • Use Break mode and set a breakpoint on a line of code.
  • Step through code one line at a time.
  • Use a variety of tools to determine the value of expressions while in Break mode.

Module 9: Handling Errors

This module explains how to trap errors and write an error handling routine. It also describes the Err object and how to set VBA error trapping options.

Lessons

  • 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
  • Working with Inline Error Handling

Lab : Handling Errors

  • Understanding VBA's Error Trapping Option
  • Writing an Error-Handling Routine
  • Working with Inline Error Handling

After completing this module, students will be able to:

  • Set VBA error trapping options for debugging.
  • Write code to trap errors with the On Error statement.
  • Write an error-handling routine.
  • Write code to handle errors inline.

Prerequisites

Before attending this course, students must have:

  • An intermediate level of experience using Microsoft Excel 2007. Familiarity with PivotTables is helpful, but not required. No prior programming experience is necessary.

Course Reviews

No reviews found for this course.

Be the first to write a review