Microsoft Excel Essentials: Level Three

Introducing The Visual Basic Editor And Recording Our First Macro

Saving Macro-Enabled Workbooks And Security Settings

Moving Code Around

Stepping Out. Well, In Actually – Debugging Made Easy

With And End With

Streamlining You Code Or Get Rid Of What You Don’t Need

Combining Your Code

A Little Privacy Please

Keyboard Shortcuts And Why I Don’t Use Them

Why You Can’t Get By With Just Recording Macros

Introduction To The Coding Section

Getting All The Code For This Section

Changing Your VBE Settings

Protecting Your Code

Objects, Methods And Properties

Understanding The Hierarchy

The Range Object

The Cells Object

The Active Cell Property

The Offset Property

The End Property

Dynamic Range Selection

The Current Region Property

Activate Vs. Select

Between The Sheets

Calling A Sheet By Its VB Name

Sheets Vs. Worksheets

Getting Around The Workbooks

The Value Property – Writing Data

The Value Property – Reading And Writing Data

Copy And Paste

Commonly Used Properties

Coding Exercise: The Rainbow

The Address Property

The Row And Column Properties

Capturing The Column Letter

More Useful Properties

Even More Useful Properties

Opening Another Workbook Programmatically

Closing Workbooks Programmatically

Coding Exercise: Open/Write/Close

Introduction To The Programmers Toolbox

Variables – Local Variables

Variables – Local Variables With A Twist

Variables – Module Level Variables

Variables – Project Level Variables

Bonus – Calling A Sub Stored In A Different Workbook

A Neat Trick To Force Variable Declaration

Variables – All The Techie Bits

An Introduction To Looping

Looping With Do Loop

Looping With For Next

Looping With A Stepped For Next

Looping With While Wend

An Introduction To Logical Testing

Logical Testing – A Simple If Test

Logical Testing – A Simple If Test Using Cells

Logical Testing – If Then Else

Logical Testing – If Then Else Using Cells

Logical Testing – Testing Multiple Criteria

Logical Testing – Testing If One Is True And One Is False

Logical Testing – Testing If Either Value Is True

Logical Testing – Select Case

Maths – Doing Simple Maths In Code

Maths – Writing Formulas To Single Cells

Maths – Writing Formulas To Ranges Of Cells

Maths – Using Excel’s Built-In Functions

Maths – Built-In Functions With Defined Ranges

InputBox – Getting User Input Using The InputBox Function

Manipulating The User Input With Casing

InputBox – Getting User Input Using The InputBox Method

Message Boxes – Simple Message Boxes

Message Boxes – Testing Which Button Was Pressed

Arrays – An Introduction

Arrays – A Simple One Dimensional Static Array

Arrays – A Simple One Dimensional Dynamic Array

Arrays – A Simple Two Dimensional Static Array

Arrays – The Most Efficient Way To Capture An Array

Arrays – Extracting Useful Data Based On User Input

Arrays – Using An Array As A Data Source For A VLookup

Introduction To Report Automation Section

A Special Note For Office 2010 Users

Recording The Bones Of The Code

Streamlining The “Add New Sheet Code”

Deconstructing The “Profit By Day” Code

Building Source Data Strings Dynamically At Runtime

Creating The Run Order and Data Capture Subs

Solving That Naming Problem

Power User – Sizing Your Charts Precisely

Changing The Chart Title (And Why We Do It Separately)

Deconstructing The Pivot Tables (It’s Slightly Different)

Titles, Money And Sorting

Butchering One Table, To Create Another

Adding The Commentary – Building Strings Dynamically At Runtime

Adding The Commentary Using Data From The Sheet We’re On

Power User – How Do You Make Specific Words Bold

Power User – Instr: A Very Useful Function

Instr And Paying Attention To Detail

Tidy Up The Title

Easy As Pie (Chart)

Prettying Up Our Pie Chart

Putting It All Together

Introduction To Web Query Section

Pulling Data From The Internet – Capturing The Data For Rome

Getting To Cancun And London From Rome

Data Clean Up

A Simple Find And Replace

Getting Our Formulas Right

Streamlining The Formulas Code

Power User – Displaying Messages In The Status Bar (Cool)

Putting It All Together

Introduction To The Events Section

Work Book Sheet Activate

Work Book Before Print

Work Book Sheet Change

Work Book Open – Creating An Auto-Back Up

Work Book Open – Creating A Splash Screen

Work Book Open – Calling Other Code

Work Book Before Close

Work Sheet Activate – You Can’t Pick This

Work Sheet Activate – You Might Pick This

Work Sheet Change

Work Sheet Change – A More Useful Use

Work Sheet Activate – Top Secret Classified Information

Work Sheet Events: Bonus – Four New Things To Try

User Defined Functions: What They Are And How You Make Them

Using A User Defined Functions To Return Information

Creating A Countdown Timer With A User Defined Functions

A Custom User Defined Functions For Calculating Volume Discount

A User Defined Functions For Getting All Your Sheet Names

Calling A User Defined Functions From A Different Workbook

The Super CountIf And SuperSumIf User Defined Functions

Introduction To Folder Creation Gizmo

Creating A New Folder With A Single Line Of Code

A Single Level Folder Structure

Folders Within Folders

Introduction To The Emailing Section

Understanding The E-Mail Routine

Deconstructing How We Capture All The Data

The E-Mail Loop

Introduction To The Word Section

Understanding The Word Routine

Deconstructing How We Capture All The Data

Formula Modifications With Unique Values

Efficient Sorting

Building The Text And Wrap Up

Introduction To PowerPoint Section

A Run Through The PowerPoint Base Code

Setting Up The Shell Of The Code

Who’s Presenting This

Adding A Slide With A Logo And Text

Prettying Up The Formatting (More Lego Coding)

Using Slide One To Create Slide Two

Adding A Chart As A Picture

Adding Pivot Tables (And Another Chart)

Final Slide, And Wrap Up

Introduction To Importing Data From A Folder Full Of Files

Looping Through All Excel Files In A Folder

The Folder Picker

A More Useful Loop Through Files

The Data Grabber

Get Rid Of Rows In An Array

Emailing Routine: Adding A Specific Attachment Based On A Criteria

Adding A Date Stamp And Going To The Insertion Point Automatically

Saving An Individual Sheet To A Specific Folder

Saving Multiple Sheets To A Single Workbook In A Specific Folder

Animated Charts With A Little Something Extra

Extracting Specific Data From A Big File To A Bunch Of Little Ones

Finding A Search String In Another Workbook With Multiple Sheets

Extracting Unique Tables To Unique Sheets From A Big Data Set

Protecting Specific Cells And Data Validation

Dynamically Populating A Reusable Array While Looping Through A Table

Sequential PDF Creation With Pictures

File Picker And Report Generator With Intelligent Filing

Help: My File Has Got Huge

Finding Updated Values In One Workbook, And Adding Them To Another Workbook

Gantt Charts With A Little More Sophistication

VLookups (Or Any Application Worksheet Function) Over Ranges

Adding A Date Stamp If A Change Has Been Made