New Horizons Computer Learning Center
410-597-9722

Excel 2003 - Introduction to Macros and VBA

Course Length: 2 Days

Overview:
In this course, students will use VBA to create macros for automating repetitive tasks in Excel 2003.

Prerequisites:
Excel 2003 - Level 1
Excel 2003 - 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

• The Power of Excel
• Macro Security
• The Visual Basic Toolbar
• Running a Macro
• The Visual Basic Editor

Lesson 2: Recording a Macro - Using Toolbars and Menus

• Using the Macro Recorder
• Running Macros from the toolbar and Excel Tools Menu
• Creating/Running Macros from a Custom Menu
• Creating/Running Macros from a Custom 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 Debugger 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

Lab 1: Record a Macro and Alter to Accept User Input

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

Lab 2: Create a Custom Function and Use in an Excel Workbook

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: Charts

• Creating a Chart with VBA
• Moving from Embedded Charts to Chart Sheets and Vice-Versa
• Controlling Formatting
• Exporting Charts as Images

Lesson 10: Pivot Tables

• Creating a 'simple' Pivot table using Excel
• Building a Pivot Table with Excel VBA
• Eliminating blank cells
• Suppressing Grand Totals or Sub Totals
• Defining Calculated Data Fields
• Running Totals

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