Unlock Excel Vba And Excel Macros

Unlock Excel Vba And Excel Macros
Last updated 11/2022
MP4 | Video: h264, 1280×720 | Audio: AAC, 44.1 KHz

Automate Complex Tasks with Microsoft Excel VBA & Excel Macros (Real-World Projects included). Achieve More. Save time.

What you’ll learn

Enhance your current Excel files & automate your work with VBA.

Master Excel VBA & Macros

Directly apply the Excel VBA example files included to your own work.

Reduce manual routine tasks to the click of a button.

Make your work-life easier.

Create simple solutions to complex problems by levering Excel functionality with Macros.

Formulas & Excel core functionality can’t do everything. Use VBA when you reach a limitation.

Easily adjust VBA code you find online to fit your purpose.

Requirements

Familiarity with Microsoft Excel.

Demonstration is done with Excel 2016 but the techniques work with any version of Excel.

Excel on Windows (some techniques might work on mac, but I will not be able to support you).
Description

Overview

Section 1: Introduction & Scope

Lecture 1 Course Structure & Content

Lecture 2 Important Compatibility & Setup Information

Lecture 3 Course Resources: Download Course Project Files

Lecture 4 Course Expectations

Lecture 5 Course Outline for Quick Reference

Section 2: Your First Macro

Lecture 6 Overview: Macros & Visual Basic Editor (VBE)

Lecture 7 Steps to Your First Macro Recording

Lecture 8 Visual Basic Editor: What You Need to Know

Lecture 9 Project: Dynamic Cell Selection (Special Cells) – Perfect for Flexible Macros

Lecture 10 Project: Copy (Consolidate) Data – Absolute versus Relative Macro Recording

Lecture 11 7 Ways to Run Macros / VBA code (incl. creative & modern buttons)

Lecture 12 OFFICE 365 Update: Change from Excel Comments to Notes

Lecture 13 Activity: Record a Macro that Deletes all Comments

Lecture 14 What’s the Difference Between Macros and VBA?

Section 3: The Object Model

Lecture 15 Overview: VBA Object Model (What you need to become an expert in VBA)

Lecture 16 VBA’s Code Reference Object Library

Lecture 17 VB Color Guidelines & Keyboard Shortcuts

Lecture 18 Excel VBA Object Model

Lecture 19 Object Properties

Lecture 20 Object Methods

Lecture 21 How to Find the Object, Property & Method

Lecture 22 Conclusion: Key Takeaways & Message from Me

Section 4: Referencing Ranges, Worksheets & Workbooks with VBA

Lecture 23 Overview: Referencing Ranges, Worksheets & Workbooks

Lecture 24 Referring to Ranges & Writing to Cells in VBA

Lecture 25 Most Useful Range Properties & Methods

Lecture 26 4 Methods to Find the Last Row of your Range

Lecture 28 Properly Referencing Worksheets

Lecture 29 Properly Referencing Workbooks

Lecture 30 Activity: Save a Hard-coded Copy of Workbook

Lecture 31 Project: Save Hard-coded Copy & Macro-free version of workbook

Lecture 32 Key Takeaways for Referencing Ranges

Lecture 34 Data Types for Better Performance

Section 6: Looping through Collections & Making Decisions

Lecture 40 Overview: Why Collections are a VBA Essential

Lecture 41 With & End With for Easier Code Writing

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

Lecture 43 IF Then (Else, ElseIF) for Conditional Outcomes

Lecture 44 Select Case as Alternative for Many IF Statements

Lecture 45 Goto Statement to Change Program Flow

Lecture 46 Activity: Get the Number of Formulas on the worksheet

Lecture 47 Project: Get the Number of Formulas on the Worksheet

Lecture 48 Bonus: Unhide All Sheets in One Go

Lecture 49 Key Takeaways for Collections & Decision Making in VBA

Section 7: Useful Built-in Functions

Lecture 50 Overview: VBA versus Worksheet Functions

Lecture 51 Most Useful VBA Functions

Lecture 52 Message Box (also with Yes, No buttons)

Lecture 53 Input Box (VBA InputBox Function)

Lecture 54 Input Box that can Select Ranges (Excel InputBox Method)

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

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

Lecture 57 Key Takeaways Built-in Functions

Section 8: Debugging, Error Handling & Procedure Scope

Lecture 58 Overview: Debugging, Handling Errors & Procedure Scope

Lecture 59 Debugging Options: Watch, Locals, Immediate Windows & More
Lecture 60 Error Handling: Different Methods

Lecture 61 Faster Code by Suppressing Pop-ups & Flickering Screen

Lecture 62 Procedure Scope & Running one Sub from Another

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

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

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

Lecture 66 Key Takeaways Debugging, Error Handling & Course Map Status

Section 9: Main Project #1: Create a Tool to Automate Table of Contents

Lecture 67 Project overview: Table of Contents (TOC) with one click

Lecture 68 TOC Project: The Basics

Lecture 69 TOC Project: Adding Hyperlinks

Lecture 70 TOC Project: Testing the VBA Code

Lecture 71 TOC Project: Adding Error Handling & More Checks

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

Lecture 73 First Milestone Completed!

Section 10: Looping in VBA (Controlling the Flow of Code)

Lecture 74 Section Overview: Looping in VBA

Lecture 75 For Next Counter Loops (Simple Example)

Lecture 76 For Next Nested Loop (Loop through Text & Cells)

Lecture 77 For Next Reverse Loop (Delete Filtered/Hidden Rows)

Lecture 78 Do Until & Do While Loop

Lecture 79 Practical Example of Do Loop (with Inputbox)

Lecture 80 Find Method for Quicker Lookup Results

Lecture 81 Find Method with Do Loop – Multiple Matches

Lecture 82 Add a Timer to Test & Speech to Inform

Lecture 83 Activity: Document all Comments in Workbook in a Sheet

Lecture 84 Project: Document all Comments in Workbook in a Sheet

Lecture 85 Useful VBA Statements & Key takeaways for Looping in VBA

Section 11: Working with Arrays

Lecture 86 Overview: Working with Arrays in VBA

Lecture 87 One Dimensional Arrays (and transferring back to sheet)

Lecture 88 Dynamic Arrays (size adjusts depending on a condition)

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

Lecture 90 Two Dimensional Arrays

Lecture 92 Activity: Create a New Worksheet per Company with Specific Information

Lecture 93 Project: Create a New Worksheet per Company with Specific Information

Lecture 94 Key Takeaways Arrays

Section 12: Working with Files, Folders & Text Files

Lecture 95 Overview: Working with Folders & Files

Lecture 96 DIR Function: Check if Files or Folders Exist

Lecture 97 GetOpenFileName Method: User Selects a File

Lecture 98 FileDialog Property: Loop Inside a Folder

Lecture 99 Create CSV File from Data in Excel

Lecture 100 Printing & Writing to Text Files for More Control

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

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

Lecture 103 Key Takeaways Files & Folders

Section 13: Excel Tables, Formulas & Pivot Tables

Lecture 104 Overview: (Pivot) Tables & Formulas

Lecture 105 Using Excel Formulas in VBA

Lecture 106 Excel Tables: Use the Right Syntax

Lecture 107 Pivot Tables & VBA: What you Need to Know

Lecture 108 Activity: Update Existing Formulas with VBA by Adding IFERROR

Lecture 109 Project: Update Existing Formulas with VBA by Adding IFERROR

Lecture 110 Key Takeaways (Pivot) Tables & Formula

Section 14: Main Project #2: Sales Regional Reporting Tool

Lecture 111 What You’ll Learn (& Should be Aware of..)

Lecture 112 Project Overview: Regional Sales Reporting Tool

Lecture 113 Importing Data from Multiple Files using MultiSelect

Lecture 114 Setting up a Template as the Basis for Different Reports

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

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

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

Lecture 118 Track the List of Tasks Completed with VBA

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

Lecture 120 Key Takeaways & Tips for VBA based Excel Tools

Lecture 121 Second Milestone Completed!

Section 15: Interacting with Other Applications (Word, PowerPoint etc.)

Lecture 122 Overview & Important Concepts when Interacting with Other Applications

Lecture 123 Project PDF: Save Specific Sheets as a PDF File

Lecture 124 Project Email: Automatically Create Personalized Emails with Attachments

Lecture 125 Project Microsoft Word: Create Personalized Letters in Word from Excel

Lecture 126 Project Microsoft PowerPoint: Export Excel Sheets to PowerPoint Presentation

Lecture 127 Key Takeaways when Working with other Applications

Section 16: Workbook and Worksheet Events

Lecture 128 Overview & What are Event Procedures?

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

Lecture 130 Worksheet Selection Change: Dynamic Font Color of Selected Range

Lecture 131 Refresh PivotTables Automatically & Concept of Range Intersection

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

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

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

Lecture 135 2 Useful Tips when Working with Events & Key Takeaways

Section 17: Working with Userforms & ActiveX Controls

Lecture 136 Overview: UserForms & ActiveX Controls

Lecture 137 ActiveX Controls: What you Need to Know

Lecture 138 ActiveX Check Box to Fix Scroll Area & Toggle Screen Settings

Lecture 139 ActiveX to Show and Hide Help

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

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

Lecture 143 UserForm for Data Entry (Input & Check Boxes)

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

Lecture 145 Bonus: ListBox to Print Multiple Pages in ONE GO

Lecture 146 UserForm Checklist & Course Map Status

Section 18: Main Project #3: Invoice Generation Tool

Lecture 147 Tool Overview: What You Will Learn

Lecture 148 Setting up an Input UserForm to Collect Master Data

Lecture 149 Working with Multi-Column ListBoxes

Lecture 150 Edit Master Data with UserForm

Lecture 151 Searchable ListBox (with Selection Change event)

Lecture 152 Enhanced Searchable ListBox: Write Selection back to Excel Table

Lecture 153 MultiPage UserForm to Create Invoices & Email (Setup)

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

Lecture 155 Create Email in Outlook & Attach PDF Invoice

Lecture 156 Final Updates & Debugging

Lecture 157 Last Milestone Completed!

Section 19: Create your own Custom Functions

Lecture 158 Section Overview: Function Procedures

Lecture 159 Setting up a Simple Function (including Optional Arguments)

Lecture 160 Function to Calculate Age (Result in Years & Months)

Lecture 161 Functions that Split Text and Numbers from Cell Value

Lecture 162 Using Functions in Sub Procedures

Lecture 163 Activity: Function That Sums based on Cell Color

Lecture 164 Project: Function That Sums based on Cell Color

Lecture 165 Key Takeaways Function Procedures in VBA & Course Map Status

Section 21: Summary & Final Words

Lecture 171 Your Next Steps & more Learning

Lecture 172 BONUS Lecture: More from Leila

Business Professionals who’d like to simplify & automate work flow.,Data analysts.,You currently use Excel and like to improve your skills.