Microsoft Excel VBA 2010 Level 2

Overview

Students will build on the basic macro building skills acquired in Microsoft Excel VBA 2010 Level 1 to achieve a more advanced understanding and expertise in using macros in Excel.

Course Objective:

You will apply automation to job tasks in Microsoft Office Excel 2010.

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

• navigate a workbook using offsets, range names, and variables
• analyze data using looping structures, in combination with if and case statements
• create user interface structures to capture multiple variable inputs
• control the lifetime and scope of variables
• create automated event processes
• create automation to handle unforeseen errors and events

The Career Center Course Curriculum for Microsoft Excel 2010 VBA Level 2:

Lesson 1: Navigating a Worksheet with VBA

• Topic 1A: Hard Coding Ranges
• Topic 1B: Offset Statement (Recording with Relative Reference)
• Topic 1C: Using Names Ranges as Bookmarks

Lesson 2: Analyzing Data with Looping Structures and Decision Making Code

• Topic 2A: Do While/Until Loop
• Topic 2B: For Each Next Loop
• Topic 2C: Nested Loops
• Topic 2D: If Statement versus IIF Function
• Topic 2E: Nested If Statement
• Topic 2F: And & Or Statements
• Topic 2G: Case Statement

Lesson 3: Using Variables

• Topic 3A: The Option Explicit Statement
• Topic 3B: Declaring Variables and Data Types
• Topic 3C: Defining Variables
• Topic 3D: Input Boxes
• Topic 3E: Understanding Scope and Lifetime of Variables

Lesson 4: Using a User Form to Capture Variable Inputs

• Topic 4A: Toolbox
• Topic 4B: Option Groups
• Topic 4C: Text Boxes and Labels
• Topic 4D: Combo Boxes
• Topic 4E: Command Buttons
• Topic 4F: CBF (Code Begind the Form)

Lesson 5: Error Trapping

• Topic 5A: Handling Errors versus Debugging
• Topic 5B: Error Properties
• Topic 5C: On Error Statement
• Topic 5D: Go To Statement
• Topic 5E: Line Names
• Topic 5F: Exit Sub Statement
• Topic 5G: Message Box Button Values
• Topic 5H: Capturing a Response

Lesson 6: Finishing Touches

• Topic 5A: Controlling Application Settings
• Topic 5B: Streamlining Code
• Topic 5C: Commenting Code as Documentation
• Topic 5D: Set Up and Auto Launch Event
• Topic 5E: Security (Password Protect Code Modules)