Microsoft Excel 2007 and Excel Visual Basic for Applications (VBA)

Overview: All the basic skills required to create, and edit a basic working sheet that will organize, and calculate common business information. All the skills required to use an Excel spreadsheet as a database.
Prerequisites: Windows Operating System: Introduction or equivalent knowledge.

Microsoft Excel 2007 -  Level 1

Course Fee: $249.00
Course Time: 1 day, 9am - 4pm, or 2 evenings, 6pm - 9pm

The Working Spreadsheet.

 All the basic skills required to create, and edit a basic working sheet that will organize, and calculate common business information.

• Getting help
• Basic spreadsheet concepts 
• Navigating in a spreadsheet
• Opening and saving your work
• Data entry
• Using AutoFill
• Cut, copy and paste
• Formatting
• Using Format Painter
• Creating Custom Styles
• Working with Names ranges
• Setting Up Pages
• Printing in Excel
• Creating and Editing charts
• Formatting charts
• Analyzing with charts
• Drawing features

Microsoft Excel 2007  – Level 2

Course Fee: $249.00
Course Time: 1 day, 9am - 4pm, or 2 evenings, 6pm - 9pm

The Spreadsheet as a Database

 All the skills required to use an Excel spreadsheet as a database.

• Basic formula creation
• Automatic totaling
• Conditional Sum Wizard
• Cell References
• Auditing and error tracking
• Linking cells
• Using Outlines and Subtotals
• Locking and protecting cells
• Consolidating multi worksheets
• Creating Templates
• Protection & Security
• Goal Seeking and Scenarios

Microsoft Excel 2007  – Level 3

Course Fee: $249.00
Course Time: 1 day, 9am - 4pm, or 2 evenings, 6pm - 9pm

Advanced Excel Functions

• Database Concepts
• Creating an Excel list
• Excel Forms
• Filtering and Sorting data
• Data Validation
• Importing data
• Analyzing with Pivot Tables
• If Statement functions
• Vlookup Function
• Common Statistical Functions
• Payment Calculation Function
• Text Calculations
• Data Validation
• Introduction to Macros
• Customizing toolbars and Menus

Microsoft Excel 2007 VBA – Day 1

Course Fee: $249.00, (Excel VBA Also Advertised as a 2 Day class for 2 x 249 = 498)
Course Time: 1 day, 9am - 4pm, or 2 evenings, 6pm - 9pm

 VBA Fundamentals and Developing a Macro

• The VBA Toolbar in Excel and in VB Editor
• The Macro Recorder and Personal.xls
• Create, Run and Edit Macros
• Basic Debugging Procedures
• Resources: - Help-Files, the MSDN Visual Developer Library, Web based resources
• Customizing the interface of Excel to use Macros

Introducing the Excel Object Model

• A Tour around VBA in Excel: - Design mode and Run mode
• VB Objects: Properties, Methods and Events
• Using the Object Hierarchy to Refer to Objects
• Referencing Objects by CodeName
• Using the Macro recorder to find out about Objects.
• Using the Object Browser
• Using the Excel Help file

Creating Simple UserForms

• A Naming Convention for UserForms, Controls and Macros
• Understanding UserForms and Controls; The Controls Toolbar
• Labels, Spin Buttons, Textboxes, List and Combo boxes, Scrollbars
• Linking controls to Cells and Named Ranges
• Adding Code to a Command Button
• Dialog boxes and Input menus, data validation

 The Excel Object Model (continued). Formatting Worksheets.

• Workbooks object
• Worksheets object
• Range object
• Chart object
• Active and Selected Objects: ActiveSheet, ActiveCell, and Selection
• Changing the Active Object or Selection
• Absolute and Relative Addressing
• From Cells to a Variables and Back again.

Microsoft Excel 2007  VBA – Day 2

Course Fee: $249.00
Course Time: 1 day, 9am - 4pm, or 2 evenings, 6pm - 9pm

 Creating Simple UserForms (continued)

• Advanced dialogs.
• Controls’ properties.
• Special Controls - Listbox, Multipage, RefEdit.
• Form Events

 Introduction to Visual Basic language structure

• Variable Naming Convention and Variable Types: - Variant, Integer, Single, Double, String, Constants
• Declaration and Scope of Variables, Option Explicit
• Enumerations
• Arrays - Getting data from cells into arrays and back again
• Operators in Visual Basic

 Control Structures and User Functions

• Logical Operations and Variables
• Logical structures - If ... Then, Select Case
• Loop and control structures – For ... Next, Do loops, For Each ... Next, With ... End With
• Creating Your Own Functions
• Passing arguments by Value and by Reference

Object Declarations and programming

• Building Your First Class
• Private and Public properties and methods
• Declaring and Using Object Variables
• Programming object properties and methods
• Using the Application Object

Debugging and Error Trapping

• More Debugging Tools - the Debug windows
• Handling Undetected Errors at Run Time - Error Trapping
• General Error Handler, Errors in Procedures, The Error Object
• Using the Immediate and Locals Windows.