Excel Macros: The Programming Way

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.



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…Then Statement

Using the ElseIf Statement

Using the If…Then…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