Ardito Corporate Training

Duration:2 days | Book this course
Cost: $620.00 + GST

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