Setting Up The Shell Of The Code

Microsoft Excel Essentials: Level 3 - Macros And VBA Programming

Introduction To The Course

Why Should I Learn How To Code (10:28)
The 15 Golden Rules Of Coding (6:24)

Introducing Your Personal Built In Translator...the Macro Recorder

Introducing The Visual Basic Editor, & Recording Our First Macro (11:10)
Saving Macro-enabled Workbooks, And Security Settings (3:16)
Moving Code Around (4:38)
Stepping Out. Well, In Actually - Debugging Made Easy (5:50)
With And End With (12:34)
Streamlining You Code, Or, Get Rid Of What You Don't Need (7:53)
Combining Your Code (6:17)
A Little Privacy Please (3:18)
Keyboard Shortcuts, And Why I Don't Use Them (2:03)
Why You Can't Get By With Just Recording Macros (10:52)

The Building Blocks Of Coding...Your Dictionary & Phrase Book Of Success!

Introduction To The Coding Section (5:35)
Getting All The Code For This Section (6:15)
Changing Your VBE Settings (2:47)
Protecting Your Code (2:10)
Objects, Methods And Properties (6:27)
Understanding The Hierarchy (3:36)
The Range Object (3:03)
The Cells Object (2:42)
The Activecell Property (2:15)
The Offset Property (2:18)
The End Property (2:49)
Dynamic Range Selection (2:33)
The CurrentRegion Property (2:05)
ACTIVATE vs. SELECT (1:43)
Between The Sheets (2:40)
Calling A Sheet By Its VB Name (2:54)
Sheets Vs. Worksheets (2:02)
Getting Around The Workbooks (3:10)
The Value Property - Writing Data (5:26)
The Value Property - Reading And Writing Data (2:35)
Copy and Paste (2:58)
Commonly Used Properties (2:52)
Coding Exercise - The Rainbow (2:20)
The Address Property (4:04)
The Row and Column Properties (1:34)
Capturing The Column Letter (2:01)
More Useful Properties (3:05)
Even More Useful Properties (2:55)
Opening Another Workbook Programmatically (6:20)
Closing Workbooks Programmatically (3:41)
Coding Exercise - Open Write Close (3:41)

The Programmers Toolbox...The Techie Stuff, Made Easy (Honest!)

Introduction To The Programmers Toolbox (0:50)
Variables - Local Variables (6:46)
Variables - Local Variables With A Twist (3:40)
Variables - Module Level Variables (3:43)
Variables - Project Level Variables (3:02)
Bonus - Calling A Sub Stored In A COMPLETELY DIFFERENT WORKBOOK (4:18)
A Neat Trick To Force Variable Declaration (1:09)
Variables - All The Techie Bits (9:29)
An Introduction To Looping (1:07)
Looping With Do Loop (5:01)
Looping With For Next (2:33)
Looping With A Stepped For Next (2:02)
Looping With While Wend (7:10)
An Introduction To Logical Testing (1:41)
Logical Testing - A Simple If Test (6:43)
Logical Testing - A Simple If Test Using Cells (5:12)
Logical Testing - If Then Else (3:17)
Logical Testing - If Then Else Using Cells (3:41)
Logical Testing - Testing Multiple Criteria (3:33)
Logical Testing - Testing If One Is True, And One Is False (2:19)
Logical Testing - Testing If Either Value Is True (3:21)
Logical Testing - Select Case (6:16)
Maths - Doing Simple Maths In Code (2:42)
Maths - Writing Formulas To Single Cells (6:56)
Maths - Writing Formulas To Ranges Of Cells (5:19)
Maths - Using Excel's Built-in Functions (3:55)
Maths - Built-in Functions With Defined Ranges (3:25)
InputBox - Getting User Input Using The InputBox Function (5:29)
Manipulating The User Input With Casing (4:20)
InputBox - Getting User Input Using The InputBox Method (5:54)
Message Boxes - Simple Message Boxes (4:06)
Message Boxes - Testing Which Button Was Pressed (4:47)
Arrays - An Introduction (2:37)
Arrays - A Simple One Dimensional Static Array (5:56)
Arrays - A Simple One Dimensional Dynamic Array (4:44)
Arrays - A Simple Two Dimensional Static Array (5:33)
Arrays - The Most Efficient Way To Capture An Array (4:55)
Arrays - Extracting Useful Data Based On User Input (7:41)
Using An Array As A Data Source For A VLookup (2:55)

Automating All Your Reports!

Introduction To Monthly Reporting (2:28)
Automating All Your Reports - 2010 Users (1:20)
Recording The Bones Of The Code (4:25)
Streamlining The Add New Sheet Code (6:11)
Deconstructing The Profit By Day Code2 (2:09)
Building Source Data Strings Dynamically At Runtime (7:53)
Creating Run Order and Data Capture Subs (3:47)
Solving That Naming Problem (3:04)
POWER USER - Sizing Your Charts Precisely (3:17)
Changing The Chart Title (And Why We Do It Separately) (2:37)
Deconstructing The Pivot Tables (It's Slightly Different) (7:36)
Titles, Money And Sorting (4:46)
Butchering One Table, To Create Another (4:33)
Adding The Commentary - Building Strings Dynamically At Runtime (7:31)
Adding The Commentary Using Data From The Sheet We're On (3:56)
POWER USER - How DO You Make Specific Words Bold (4:36)
POWER USER - INSTR...A Very Useful Function (5:03)
INSTR And Paying Attention To Detail (2:54)
Tidy Up The Title (3:01)
Easy As Pie (Chart) (5:53)
Pretty Up Our Pie Chart (2:50)
Putting It All Together (4:50)

Super Web Query - The Data Is Out There...On The Internet, That Is

Introduction To The Web Query section (1:38)
Pulling Data From The Internet - Capturing The Data For Rome (4:18)
Getting To Cancun And London From Rome (5:59)
Data Clean-Up (4:11)
A Simple Find And Replace (2:07)
Getting Our Formulas Right (3:51)
Streamlining The Formulas Code (4:16)
POWER USER - Displaying Messages In The Status Bar (Cool) (3:06)
Putting It All Together (1:55)

Workbook Events You Don't Have To Run Code To Have Code Run!

Intro To The Events Section (2:06)
WorkBook SheetActivate (4:09)
WorkBook BeforePrint (2:47)
WorkBook SheetChange (1:14)
WorkBook Open - Creating An Auto-Back Up (5:17)
WorkBook Open - Creating A Splash Screen (3:55)
WorkBook Open - Calling Other Code (5:41)
WorkBook BeforeClose (3:09)
WorkSheet Activate - You Can't Pick This (1:44)
WorkSheet Activate - You Might Pick This (2:44)
WorkSheet Change (2:32)
WorkSheet Change - A More Useful Use (4:57)
WorkSheet Activate - Top Secret Classified Information (3:31)
BONUS - WorkSheet Events - 4 New Things To Try (8:34)

User Defined Functions...What To Do If The Function You Need Isn't In Excel!

User Defined Functions...What They Are, And How You Make Them (5:39)
Using A UDF To Return Information (1:41)
Creating A Countdown Timer With A UDF (8:30)
A Custom UDF For Calculating Volume Discount (4:07)
A UDF For Getting All Your Sheet Names (3:05)
Calling A UDF From A Different Workbook (1:47)
SuperCountIf and SuperSumIf UDF's (10:18)

Bonus Section Controlling Windows - Folder Creation Gizmo

Introduction To The Folder Creation Gizmo (2:24)
Creating A New Folder With A Single Line Of Code (0:55)
A Single Level Folder Structure (5:27)
Folders Within Folders (5:36)

Bonus Section eMail Automation With Outlook...Why WRITE emails!

Introduction To The Emailing Section (3:10)
Understanding The eMail Routine (4:57)
The eMail Loop (5:06)
Capturing All The Data (10:14)

Bonus Section Word Automation - Controlling Word From Excel

Introduction To The Word Section (0:45)
Understanding The Word Routine (2:17)
Deconstructing How We Capture All The Data (3:02)
Formula Modifications With Unique Values (3:18)
Efficient Sorting (2:56)
Building The Text And Wrap Up (5:16)

Bonus Section PowerPoint Automation - Create Your Presentation In Seconds!

Introduction To The PowerPoint Section (1:12)
A Run Through The PowerPoint Base Code (9:23)
Setting Up The Shell Of The Code (3:13)
Who's Presenting This... (2:31)
Adding A Slide With A Logo And Text (8:20)
Prettying Up The Formatting (More Lego Coding!) (2:03)
Using Slide 1 To Create Slide 2 (4:33)
Adding A Chart As A Picture (6:43)
Adding Pivot Tables (And Another Chart) (9:12)
Final Slide, And Wrap Up (2:12)

Importing Specific Data From Multiple Files

Introduction To Importing Data From A Folder Full Of Files (3:26)
Looping Through All Excel Files In A Folder (3:56)
The Folder Picker (3:11)
A More Useful Loop Through Files (3:20)
The Data Grabber(er) (13:17)

Student Questions Answered

Level 3 SQA 01 - Get Rid Of Rows In An Array (5:49)
Level 3 SQA 02 - Emailing Routine Adding a Specific Attachment Based On a Criteria (3:34)
Level 3 SQA 03 - Adding A Date Stamp, And Going To The Insertion Point AUTOMATICALLY (7:25)
Level 3 SQA 04 - Saving An Individual Sheet To A Specific Folder (11:41)
Level 3 SQA 05 - Saving Multiple Sheets To A Single Workbook In A Specific Folder (27:29)
Level 3 SQA 06 - Animated Charts...With A Little Something Extra! (6:23)
Level 3 SQA 07 - Extracting Specific Data From A Big File, To A Bunch Of Little Ones (7:25)
Level 3 SQA 08 - Finding A Search String in Another Workbook With Multiple Sheets (4:54)
Level 3 SQA 09 - Extracting Unique Tables to Unique Sheets From A Big Data Set (13:06)
Level 3 SQA 10 - Protecting Specific Cells and Data Validation (11:34)
Level 3 SQA 11 - Dynamically Populating A Reusable Array While Looping Through A Table (5:24)
Level 3 SQA 12 - Sequential PDF Creation With Pictures (17:02)
Level 3 SQA 13 - File Picker, And Report Generator With Intelligent Filing (13:09)
Level 3 SQA 14 - Why Is My Workbook So Big (7:26)
Level 3 SQA 15 - Finding Updated Values In One Workbook, And Adding Them To Another Workbook (9:10)
Level 3 SQA 16 - Gantt Charts With A Little More Sophistication (10:30)
Level 3 SQA 17 - Vlookups (Or Any Application.WorksheetFunction) Over Ranges (12:20)
Level 3 SQA 18 - Adding A Date Stamp If A Change Has Been Made (5:36)
Level 3 SQA 19 - Creating a Toggle Custom Keyboard Shortcut (4:36)

Setting Up The Shell Of The Code

Lesson content locked

If you're already enrolled, you'll need to login.

Enroll in Course to Unlock