Excel 2007 Introduction to Macros

Microsoft Excel training
Course Code: 
MSEXC2007VBA
Duration: 
Duration: 2 days
Prerequisites: 
A good knowledge of Excel is necessary, ideally up to Advanced level.
Course Content: 

Day 1
Recording Macros

  • What is a macro?
  • Recording a Macro
  • Running a Macro
  • Recording a Macro Using Relative References
  • Saving a Workbook Containing a Macro
  • Opening a Workbook Containing a Macro
  • Saving the Personal Macro Workbook

The Visual Basic Editor

  • Launching the Visual Basic Editor
  • Navigating the Visual Basic Editor
  • Finding a Macro in the Visual Basic Editor
  • Inserting a Module into a Workbook
  • Copying a Module between Workbooks
  • Renaming a Module Sheet
  • Deleting a Module Sheet
  • Importing and Exporting Code as Text

Assigning Macros

  • Running a Macro with Shortcut Keys
  • Adding Custom Buttons to Run a Macro
  • Removing Custom Buttons
  • Creating and Editing a Worksheet Macro Button

Control Structures

  • The If - Then Control Structure
  • The If – Then - Else Control Structure
  • Using ElseIf in a Control Structure
  • The Select Case Control Structure

Looping Structures

  • For Next
  • For Each Next
  • Using a Do - Loop Statement

Data Variables

  • What are Data Variables?
  • Creating Data Variables and Assigning Values
  • The DIM statement
  • Data Variable Types
  • Using a Message Box to Return a Value

 
Interactive Macro’s

  • Message Boxes
  • Input Boxes

Day 2
Variables, Constants and Arrays

  • Declaring Variables
  • Scope and Visibility
  • Constants
  • Creating and Defining Arrays
  • Assigning Values to an Array
  • Filling Arrays Using Loops
  • Dynamic Arrays
  • Preserving Array Values
  • The Array Function

 User Defined Functions

  • Using Excel Worksheet Functions in VBA
  • VBA Functions
  • User-Defined Functions
  • Adding Help to User Defined Functions
  • Declaring Variables in User Defined Functions
  • Using Ranges in User Defined Functions
  • Creating and Managing an Add-in for User Defined Functions

User Forms
Testing and Debugging code

  • Types of Error and Debugging
  • Stepping Through a Procedure
  • Displaying Variable Values
  • Break Mode
  • Exercise
  • Testing and Debugging Code