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
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.
Prerequisites
Understanding of SharePoint 2007 basics.
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
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
:: Course 50342A ::
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.
:: Course 50342A ::
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 userdefined 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
:: Course 50342A ::
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
:: Course 50342A ::
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
:: Course 50342A ::
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.
Programme scope, delivery arrangements and suitability are confirmed during enquiry.