Excel VBA

Macros / VBA

• What are Macros? What is VBA?
• How to record macros How to run macros

VBA Editor

• Modules Procedures Project Explorer Toolbars / Options

Creating / Editing Code

• Create Procedures Subs Comments

Invoking Macros

• Hot Keys
• Quick Access Toolbar Call Command

Compiling Code

• Stepping through code Reset
• Run
• Preventing fatal errors and    crashes Using the macro recorder
• Give macro a name Add a shortcut key
• Store macro in workbook vs. separate file Adding a description

Running a macro

• Adding a macro to the quick access toolbar Button form control
• Clipart
• Shapes and Word Art 
• SmartArt Graphics

Visual Basic Editor (VBE)

• How to access (Alt + F11) 
• Layout of the window
• Project explorer
• MSE Objects How to rearrange the display window Debugging VBA code
• Add breakpoint
• Step into, over, continue

Problems with the recorder

• Unnecessary code added Difficult to troubleshoot
• Uses absolute references to cells

Introduction to Object-Oriented Programming (OOP)

Using Message Boxes

Variables

• Declarations Option Explicit Data Types Working with the Object Model Worksheets
• Ranges: Rows, Columns, Cells

Creating & Naming Objects

• Naming conventions Housekeeping
• Navigation / Selection Techniques

Formula R1C1 Reference Style Offsets

• Range Names
• Logic Statements IF Statements
• Looping Statements Do Loops
• Interactive Code
• Input boxes Message boxes
• Error Handlers VBA

 Macros VBA Editor

• Project Explorer Properties Window Modules

Creating / Editing Code

• Functions
• Events
• Invoking Macros
• Worksheet Buttons Customize Ribbon
• Compiling Code
• Stepping through   code Setting  Breakpoints

Variables

• Declarations
• Option Compare Text Option Compare
• Binary Lifetime and Scope

Working with The Object Model Application

Creating & Naming Objects

• Naming conventions Housekeeping

Navigation / Selection Techniques

• Absolute versus Relative Reference Offsets

Logic Statements

• Nested IF Statements Case Statements

Functions

• Intrinsic Functions User Defined Functions

Looping Statements  For Each Next Loops

Interactive Code

• User Forms – dashboards Message Boxes

Error Handlers

• Error numbers 
• Error descriptions

Arrays

• Declaring single-dimension and multi-dimension arrays Propagating an array
• Retrieving data from an array
• Reasons and examples of why to use arrays Dynamic arrays
• Passing arrays
• Reference
• Argument Ranges and Selections
• Named ranges
• Range references Relative ranges
• Cells, offset, resize, columns, rows, union, intersect, regions Referencing tables