• a.    Enabling the developer toolbar
• b.    Understanding file types supported by macros
• i.     Excel Workbook (.xls) – Macros supported
• ii.     Excel Workbook (.xlsx) – Not supported
• iii.     Excel Macro-Enabled Workbook (.xlsm) – Macros supported
• iv.     Excel Binary Workbook (.xlsb) – Macros supported
• c.     Configure Excel to save as .xlsm by default
• d.    Macro security
• i.     Setting trusted locations
• ii.     Enabling Macros by clicking button on toolbar
• iii.     Disable notifications for other locations

Using the macro recorder

• a.    Give macro a name
• b.    Add a shortcut key
• c.     Store macro in workbook vs. separate file
• d.    Adding a description

Running a macro

• a.    Adding a macro to the quick access toolbar
• b.    Button form control
• c.     Clipart
• d.    Shapes and Word Art
• e.    SmartArt Graphics
• 4.    Visual Basic Editor (VBE)
• a.    How to access (Alt + F11)
• b.    Layout of the window
• i.     Project explorer
• ii.     MSE Objects
• c.     How to rearrange the display window
• d.    Debugging VBA code
• i.     Add breakpoint
• ii.     Step into, over, continue

Problems with the recorder

• a.    Unnecessary code added
• b.    Difficult to troubleshoot
• c.     Uses absolute references to cells

Introduction to Object-Oriented Programming (OOP)

Using Message Boxes

Ranges and Selections

• a.    Named ranges
• b.    Range references
• c.     Relative ranges
• d.    Cells, offset, resize, columns, rows, union, intersect, regions
• e.    Referencing tables

Looping / Flow Control

• a.    For / Next loops
• b.    Do loops
• c.     While loops
• d.    For / Each loops

Selection Statements

• a.    If / Then / Else
• b.    Select / Case

A1 vs. R1C1 references

• a.    Toggling style references
• b.    Using Excel formulas
• c.     Using Arrays

 Excel Names

• a.    Global vs. local variables
• b.    Adding / Hiding / Deleting Names
• c.     Checking Name existence
• d.    Comments
• e.    Types of Names

Event Programming

• a.    Event levels
• b.    Using Events
• c.     Workbook / Worksheet / Chart events
• d.    Application-Level events

Arrays

• a.    Declaring single-dimension and multi-dimension arrays
• b.    Propagating an array
• c.     Retrieving data from an array
• d.    Reasons and examples of why to use arrays
• e.    Dynamic arrays
• f.      Passing arrays
• i.     Reference
• ii.     Argument

Classes, Records, and Collections

• a.    Class Modules
• b.    Trapping Events
• c.     Custom Objects
• d.    Using collections
• e.    User-Defined Types
• f.      Custom Properties

User Forms

• a.    Interaction methods
• b.    Creating / Calling / Hiding a Userform
• c.     Programming a Userform / Controls
• d.    Verifying Data Entry
• e.    Filenames

Data Mining with Advanced Filter

• a.    Replacing Loops
• b.    Extracting lists of values
• c.     Criteria Ranges

Pivot Tables

• a.    Introduction and understanding Pivot Tables and versions
• b.    Examples
• c.     Filtering Datasets
• d.    Using the Data Model

19. Charts

• a.    Creating and customizing charts
• b.    Combo charts
• c.     Advanced charts
• d.    Exporting charts as graphics
• e.    Pivot charts

Conditional Formatting

• a.    Data Visualization
• b.    Data Bars, Color Scales, Icon Sets
• 21. Sparklines

Reading from and writing to the Web

Text file processing

Automating Word functions

Handling errors

Customizing the ribbon

Creating Add-Ins