Ardito Computer Training

Course Description

This class is designed to help students describe the concepts in and uses of Visual Basic for Applications, record and edit procedures, debug visual basic code, assign procedures to menus, objects, and keyboard shortcuts, use function procedures, and objects properties, and methods. In addition, students will learn to use interactive procedures, control procedures, automate procedures, customize menus and command bars, and create user forms.

Prerequisites
Participants should have completed either the Excel Intermediate or Excel Advanced level, or have equivalent experience.

Who Should Take This Course
Microsoft Excel users who are familiar with the advanced level functions and want to learn how to use the software's high end-user features.

Course Objectives

Upon completing this course, you will have learned how to:

1. Describe the concepts in and uses of Visual Basic for Applications.
2. Record and edit basic procedures.
3. Debug Visual Basic code.Assign procedures to menus, objects, and keyboard shortcuts.
4. Use function procedures.
5. Use objects, properties, and methods.
6. Use interactive procedures.
7. Control procedures.
8. Automate procedures.
9. Customize menus and command bars.
10. Create user forms.
    
    

Course Outline

Introduction to Visual Basic for Applications

• Introduction to Visual Basic for Applications
• Running a Procedure
• Running a Procedure Using the Visual Basic Toolbar
• Running a Procedure Using the Tools Menu
• Running a Procedure Using a Keyboard Shortcut

Recording and Editing Basic Procedures

• Recording a Procedure
• Understanding Visual Basic Modules
• The Project Explorer
• Understanding Syntax
• How Excel Displays Code
• How This Manual Displays Syntax
• Editing a Procedure
• Calling a Procedure
• Understanding Variables
• Using Parameters in a Procedure
• Using Multiple Parameters
• Passing by Reference or Passing by Value
• Adding Steps into a Procedure
• Selecting the Recording Mode
• Absolute and Relative References
• Selecting the Recording Mode

Debugging Visual Basic Code

• Stepping Through a Procedure
• Responding to the Microsoft Visual Basic Error Dialog Box
• Using the Break Mode and Watch Expressions
• Entering Break Mode Manually
• Using Breakpoints to Enter Break Mode
• Watch Expressions

Assigning Procedures to Menus, Objects, and

Keyboard Shortcuts

• Assigning a Procedure to a Keyboard Shortcut
• Assigning a Procedure to an Excel Menu
• Assigning a Procedure to a Toolbar
• Assigning a Procedure to a Graphic

Using Function Procedures

• Function Procedure Syntax
• Creating and Calling a Function Procedure
• Creating a Function Procedure
• Calling a Function Procedure
• Inserting a User-defined Function
• Using the Paste Function Button
• Typing a User-defined Function
• Using Objects, Properties, and Methods
• Using Objects, Properties, and Methods
• Understanding Objects, Properties, and Methods
• Looking at Recorded Procedures
• Using Visual Basic Help
• Using the Object Browser
• Setting an Object’s Property
• Property Value Syntax
• Using the With Statement
• Manipulating Ranges and Cells
• The Range Object
• The Cells Property
• The Offset Method
• Using the Formula Property
• Using the Select and Activate Methods
• The Select Method
• The Activate Method
• Using the Close Method

Using Interactive Procedures

• Displaying Excel's Built-In Dialog Boxes
• Displaying a Customized Dialog Box
• Using the StatusBar Property
• Using the Beep Statement
• Creating a Message Box

Controlling Procedures

• Using the If and Then Statement
• Using the Else and If Statement
• Using the If and Then and Else Statement
• Using the Do Loop Statement
• The Do While Loop Statement
• The Do Until Loop Statement
• Using the For Next Statement

Automating Procedures

• Using Automatic Procedures
• Recording an Automatic Procedure
• Writing an Automatic Procedure
• Working with Add-in Applications
• Creating an Add-in Application
• Loading an Add-in Application
• Using the Personal Macro Workbook
• Recording a Procedure into the Personal Macro Workbook
• Saving a Workbook as the Personal Macro Workbook

Customizing Menus and Command Bars

• Creating a Custom Menu
• Creating Command Bars
• Creating a Command Bar
• Adding Buttons to a Command Bar
• Adding Menus to a Command Bar
• Deleting a Command Bar

Creating User Forms

• Creating a User Form
• Inserting a User Form
• Adding Controls to the User Form
• Adding Controls to the User Form
• Modifying User Form Controls
• Control Properties
• Assigning a Procedure to a Control
• Event Procedures
• Assigning an Existing Procedure to a Control
• Using Control Methods and Properties in Code
• Assigning a New Procedure to a Control
• The Unload and End Methods
• Displaying a User Form
• Defining Initial User Form Settings