Leila Gharani – Unlock Excel VBA & Excel Macros

Referring to Ranges & Writing to Cells in VBA

Most Useful Range Properties & Methods

4 Methods to Find the Last Row of your Range

Copying & resizing a variably sized range

Properly Referencing Worksheets

Properly Referencing Workbooks

Activity: Save a Hardcoded copy of Workbook

Project: Save Hard-coded Copy & Macro-free version of Workbook

Conclusion: Key Takeaways for Referencing Ranges

Data Types for Better Performance

Declaring Variables, Arrays & Constants (Role of Option Explicit)

Using Object Variables (Set statement)

Scoping Variables

Activity: Revisit Hard-coded Workbook Project & Use Variable Assignment

Test Your Knowledge on Variables and data types

Key Takeaways for Working with Variables

With & End With for Easier Code Writing

For Each to Loop Through Collections (sheets, ranges etc.) in one go

IF Then (Else, ElseIF) for Conditional Outcomes

Select Case as Alternative for Many IF Statements

Goto Statement to Change Program Flow

Activity: Get the Number of Formulas on the worksheet

Project: Get the Number of Formulas on the Worksheet

Bonus: Unhide All Sheets in One Go

Key Takeaways for Collections & Decision Making in VBA

Most Useful VBA Functions

Message Box (also with Yes, No buttons)

Input Box (VBA InputBox Function)

Input Box that can Select Ranges (Excel InputBox Method)

Activity: Show Top 3 Values in a Message Box Based on Range Selected

Project: Show Top 3 Values in a Message box based on Range Selected

Key Takeaways Built-in Functions

Debugging Options: Watch, Locals, Immediate Windows & More

Error Handling: Different Methods

Faster Code by Suppressing Pop-ups & Flickering Screen

Procedure Scope & Running one Sub from Another

Passing Arguments to Sub Procedures (By Ref, By Val)

Activity: Get the Total Number of Formulas (or Comments) Used in a Workbook

Project: Get the Total Number of Formulas (or Comments) Used in a Workbook

Key Takeaways Debugging, Error Handling & Course Map Status

TOC Project: The Basics

TOC Project: Adding Hyperlinks

TOC Project: Testing the VBA Code

TOC Project: Adding Error Handling & More Checks

TOC Project: Adding to Personal Macro Workbook (Make it Available to all Files)

First Milestone Completed!

For Next Counter Loops (Simple Example)

For Next Nested Loop (Loop through Text & Cells)

For Next Reverse Loop (Delete Filtered/Hidden Rows)

Do Until & Do While Loop

Practical Example of Do Loop (with Inputbox)

Find Method for Quicker Lookup Results

Find Method with Do Loop – Multiple Matches

Add a Timer to Test & Speech to Inform

Activity: Document all Comments in Workbook in a Sheet

Project: Document all Comments in Workbook in a Sheet

Useful VBA Statements & Key takeaways for Looping in VBA

One Dimensional Arrays (and transferring back to sheet)

Dynamic Arrays (size adjusts depending on a condition)

Preserving Dynamic Arrays (adding more elements to the existing array)

Two Dimensional Arrays

Variant Arrays: Quick Method to Write to Cells

Activity: Create a New Worksheet per Company with Specific Information

Project: Create a New Worksheet per Company with Specific Information

Key Takeaways Arrays

DIR Function: Check if Files or Folders Exist

GetOpenFileName Method: User Selects a File

FileDialog Property: Loop Inside a Folder

Create CSV File from Data in Excel

Printing & Writing to Text Files for More Control

Activity: Export Data to Text file (fix Delimiter to Semicolon)

Project: Export Data to Text file (fix Delimiter to Semicolon)

Key Takeaways Files & Folders

Using Excel Formulas in VBA

Excel Tables: Use the Right Syntax

Pivot Tables & VBA: What you Need to Know

Activity: Update Existing Formulas with VBA by Adding IFERROR

Project: Update Existing Formulas with VBA by Adding IFERROR

Key Takeaways (Pivot) Tables & Formula

Project Overview: Regional Sales Reporting Tool

Importing Data from Multiple Files using MultiSelect

Setting up a Template as the Basis for Different Reports

Exporting Reports as Excel files (Pivot Table & Hard Coded Reports)

Fine-tuning Report Export Procedure (Double-check if user wants to overwrite)

Setting up Automatic Export of Text File (Fix delimiter in CSV to Semicolon)

Track the List of Tasks Completed with VBA

Final Touches & Creating Interface to Hide Specific Tabs with a Password

Key Takeaways & Tips for VBA based Excel Tools

Second Milestone Completed!

Project PDF: Save Specific Sheets as a PDF File

Project Email: Automatically Create Personalized Emails with Attachments

Project Microsoft Word: Create Personalized Letters in Word from Excel

Project Microsoft PowerPoint: Export Excel Sheets to PowerPoint Presentation

Key Takeaways when Working with other Applications

Workbook Events, Toggle Full Screen (Activate, Deactivate, Open, Close)

Worksheet Selection Change: Dynamic Font Color of Selected Range

Refresh PivotTables Automatically & Concept of Range Intersection

Resetting a Dependent Drop-down when the first Drop-down is Changed

Activity: Link Page Header to Cell Value (BeforePrint Event)

Project: Link Page Header to Cell Value (BeforePrint Event)

2 Useful Tips when Working with Events & Key Takeaways

ActiveX Controls: What you Need to Know

ActiveX Check Box to Fix Scroll Area & Toggle Screen Settings

ActiveX to Show and Hide Help

ActiveX Combo Box for Easy Sheet Navigation (Great for Larger Workbooks)

ActiveX to Show or Hide Other ActiveX controls (eg. Actual & Budget reporting)

UserForm Basics: What You Need to Get Started

UserForm for Data Entry (Input & Check Boxes)

Practical UserForm to Document External Links & Comments (Combo Box & Option button)

Bonus: ListBox to Print Multiple Pages in ONE Go

UserForm Checklist & Course Map Status

Setting up an Input UserForm to Collect Master Data

Working with Multi-Column ListBoxes

Edit Master Data with UserForm

Searchable ListBox (with Selection Change event)

Enhanced Searchable ListBox: Write Selection back to Excel Table

MultiPage UserForm to Create Invoices & Email (Setup)

Export PDF & Excel Workbook for Each Invoice based on ListBox Selection

Create Email in Outlook & Attach PDF Invoice

Final Updates & Debugging

Last Milestone Completed!

Setting up a Simple Function (including Optional Arguments)

Function to Calculate Age (Result in Years & Months)

Functions that Split Text and Numbers from Cell Value

Using Functions in Sub Procedures

Activity: Function That Sums based on Cell Color

Project: Function That Sums based on Cell Color

Key Takeaways Function Procedures in VBA & Course Map Status

Create and Modify a Chart with VBA

Animated Charts: Different Methods

Show Charts inside UserForms

Key Takeaways: Charts