New Horizons Computer Learning Center
410-597-9722

Excel 2007 - Introduction to Macros and VBA

Course Length: 2 Days

Overview:
In this course, the student will learn the essentials for recording, writing and editing macros in Microsoft Office Excel 2007. This is all about automating business processes.

Prerequisites:
Excel 2007 - Level 1
Excel 2007 - Level 2

Course Objectives:
Upon successful completion of this course, students will be able to:

• develop macros
• format worksheets
• create an interactive worksheet
• work with multiple worksheets
• perform calculations

Course Content

Lesson 1: Overview of Excel with VBA

• The Power of Excel
• Macro Security
• The Developer Tab in the Ribbon
• Running a Macro
• The Visual Basic Editor

Lesson 2: Recording a Macro - Using the Ribbon

• Using the Macro Recorder
• Running Macros from the Ribbon/Developer Tab
• Creating/Running Macros from a Custom Menu
• Creating/Running Macros from the Quick Access Toolbar
• Hotkeys and Other Ways to Run Macros

Lesson 3: Examining and Modifying Recorded Code

• Reviewing Excel Objects
• Code Basics: Properties and Methods
• Method Structure
• Investigating Optional Parameters and Defined Constants
• Altering the Code to accept User Input

Lesson 4: Using the Debugging Tools

• Stepping through Code
• Setting Breakpoints
• Run to Cursor option
• Stepping Over Subroutines
• Immediate Window

Lesson 5: Referring to Ranges

• The Range Object
• Declaring Range Variables and Referring to Cells
• Named Ranges
• Referencing Ranges in Other Sheets and Relative to Another Range
• Using the Offset Property to Refer to a Range
• Current Region and Other Range Properties

Lesson 6: User-Defined Functions

• Creating Functions
• Examining the Difference Between Functions and Subroutines
• Custom Mathematical Calculations
• Working with Dates, Strings (Text), Sorting
• Using your Functions in an Excel Spreadsheet

Lesson 7: Looping and Flow Control

• Using Variables in the For Statement
• Variations on the For ...Next Loop
• Exiting a Loop Early
• Nested Loops
• Do Loops
• For .Each Statements
• If...Then.Else and Select Case

Lesson 8: R1C1 Style Formulas

• Referring to Cells: A1 vs. R1C1 References
• Switching Excel to Display R1C1 Style
• Explanation of R1C1 Reference Style
• Conditional Formatting - R1C1 required
• Array Formulas

Lesson 9: Event Programming

• Levels of Events
• Using Events
• Workbook-Related Events
• Chart Sheet Events
• Application-Level Events

Lesson 10: Handling Errors

• What happens when an Error occurs?
• Basic Error Handling with On Error Go To Syntax
• Generic Error Handlers
• RunTime Errors

6940 Tudsbury Road, Baltimore, MD 21244
P: 410-597-9722
www.nhbaltimore.com