English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 172 lectures (22h 30m) | 8.41 GB
Automate Complex Tasks with Microsoft Excel VBA & Excel Macros (Real-World Projects included). Achieve More. Save time.
Go from Beginner to Microsoft Excel VBA Expert!
I’ll take you step-by-step through engaging video tutorials and teach you everything you need to know to be able to use VBA to improve your work flow, save time and automate your current Excel projects.
What differentiates this course from the other Microsoft Excel VBA & macros courses? It includes:
- Over 50 Microsoft Excel VBA & macro examples. These are solutions my clients have needed through the years.
- They are likely to be useful for you too.
- Using the right VBA syntax is the most difficult part. An important topic which isn’t covered enough in other
- VBA courses. In this course you understand why you write things in certain ways.
- Create 3 Complete Excel VBA Tools. We go from idea to final product and cover each step.
- Over 100 pages of documentation (including useful code) so you can easily refer to it when in need.
- Simple explanations for complex concepts.
- High quality video and different delivery methods to keep things interesting.
- Lots of ideas to automate your current tasks.
Don’t waste your time on poor quality videos and confusing explanations. In this Excel VBA course, complex topics are taught in a simple way by an instructor with 17 years of experience. The content has been carefully crafted and clearly organized. The videos are high quality and include animations to emphasize points. Most importantly you learn VBA by doing.
What you can look forward to in this course:
- Understand how to use the right syntax in VBA
- Useful Excel VBA codes you can directly use for your work
- Use the macro recorder for simpler tasks and improve the code
- Working with the Visual Basic Editor
- Debugging tools and error handling methods
- Different ways to reference ranges and cells
- Different methods to loop
- How to work with data types
- PivotTables in Excel using VBA
- Importing data from other files and exporting data to text files
- Interacting with other applications (Word, PowerPoint, Outlook)
- ActiveX and Userforms to enhance the Excel interface
- Basically all the knowledge you need to automate tasks in Excel
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.
Table of Contents
Introduction & Scope
1 Course Structure & Content
2 Important Compatibility & Setup Information
3 Course Resources Download Course Project Files
4 Course Expectations
5 Course Outline for Quick Reference
Your First Macro
6 Overview Macros & Visual Basic Editor (VBE)
7 Steps to Your First Macro Recording
8 Visual Basic Editor What You Need to Know
9 Project Dynamic Cell Selection (Special Cells) – Perfect for Flexible Macros
10 Project Copy (Consolidate) Data – Absolute versus Relative Macro Recording
11 Ways to Run Macros VBA code (incl. creative & modern buttons)
12 OFFICE 365 Update Change from Excel Comments to Notes
13 Activity Record a Macro that Deletes all Comments
14 What’s the Difference Between Macros and VBA
The Object Model
15 Overview VBA Object Model (What you need to become an expert in VBA)
16 VBA’s Code Reference Object Library
17 VB Color Guidelines & Keyboard Shortcuts
18 Excel VBA Object Model
19 Object Properties
20 Object Methods
21 How to Find the Object, Property & Method
22 Conclusion Key Takeaways & Message from Me
Referencing Ranges, Worksheets & Workbooks with VBA
23 Overview Referencing Ranges, Worksheets & Workbooks
24 Referring to Ranges & Writing to Cells in VBA
25 Most Useful Range Properties & Methods
26 Methods to Find the Last Row of your Range
27 Copying & resizing a variably sized range
28 Properly Referencing Worksheets
29 Properly Referencing Workbooks
30 Activity Save a Hard-coded Copy of Workbook
31 Project Save Hard-coded Copy & Macro-free version of workbook
32 Key Takeaways for Referencing Ranges
Working with Variables
33 Overview Role of Assignment & Variables
34 Data Types for Better Performance
35 Declaring Variables, Arrays & Constants (Role of Option Explicit)
36 Using Object Variables (Set statement)
37 Scoping Variables
38 Activity Revisit Hard-coded Workbook Project & Use Variable Assignment
39 Key Takeaways for Working with Variables
Looping through Collections & Making Decisions
40 Overview Why Collections are a VBA Essential
41 With & End With for Easier Code Writing
42 For Each to Loop Through Collections (sheets, ranges etc.) in one go
43 IF Then (Else, ElseIF) for Conditional Outcomes
44 Select Case as Alternative for Many IF Statements
45 Goto Statement to Change Program Flow
46 Activity Get the Number of Formulas on the worksheet
47 Project Get the Number of Formulas on the Worksheet
48 Bonus Unhide All Sheets in One Go
49 Key Takeaways for Collections & Decision Making in VBA
Useful Built-in Functions
50 Overview VBA versus Worksheet Functions
51 Most Useful VBA Functions
52 Message Box (also with Yes, No buttons)
53 Input Box (VBA InputBox Function)
54 Input Box that can Select Ranges (Excel InputBox Method)
55 Activity Show Top 3 Values in a Message Box Based on Range Selected
56 Project Show Top 3 Values in a Message box based on Range Selected
57 Key Takeaways Built-in Functions
Debugging, Error Handling & Procedure Scope
58 Overview Debugging, Handling Errors & Procedure Scope
59 Debugging Options Watch, Locals, Immediate Windows & More
60 Error Handling Different Methods
61 Faster Code by Suppressing Pop-ups & Flickering Screen
62 Procedure Scope & Running one Sub from Another
63 Passing Arguments to Sub Procedures (By Ref, By Val)
64 Activity Get the Total Number of Formulas (or Comments) Used in a Workbook
65 Project Get the Total Number of Formulas (or Comments) Used in a Workbook
66 Key Takeaways Debugging, Error Handling & Course Map Status
Main Project #1 Create a Tool to Automate Table of Contents
67 Project overview Table of Contents (TOC) with one click
68 TOC Project The Basics
69 TOC Project Adding Hyperlinks
70 TOC Project Testing the VBA Code
71 TOC Project Adding Error Handling & More Checks
72 TOC Project Adding to Personal Macro Workbook (Make it Available to all Files)
73 First Milestone Completed
Looping in VBA (Controlling the Flow of Code)
74 Section Overview Looping in VBA
75 For Next Counter Loops (Simple Example)
76 For Next Nested Loop (Loop through Text & Cells)
77 For Next Reverse Loop (Delete Filtered Hidden Rows)
78 Do Until & Do While Loop
79 Practical Example of Do Loop (with Inputbox)
80 Find Method for Quicker Lookup Results
81 Find Method with Do Loop – Multiple Matches
82 Add a Timer to Test & Speech to Inform
83 Activity Document all Comments in Workbook in a Sheet
84 Project Document all Comments in Workbook in a Sheet
85 Useful VBA Statements & Key takeaways for Looping in VBA
Working with Arrays
86 Overview Working with Arrays in VBA
87 One Dimensional Arrays (and transferring back to sheet)
88 Dynamic Arrays (size adjusts depending on a condition)
89 Preserving Dynamic Arrays (adding more elements to the existing array)
90 Two Dimensional Arrays
91 Variant Arrays Quick Method to Write to Cells
92 Activity Create a New Worksheet per Company with Specific Information
93 Project Create a New Worksheet per Company with Specific Information
94 Key Takeaways Arrays
Working with Files, Folders & Text Files
95 Overview Working with Folders & Files
96 DIR Function Check if Files or Folders Exist
97 GetOpenFileName Method User Selects a File
98 FileDialog Property Loop Inside a Folder
99 Create CSV File from Data in Excel
100 Printing & Writing to Text Files for More Control
101 Activity Export Data to Text file (fix Delimiter to Semicolon)
102 Project Export Data to Text file (fix Delimiter to Semicolon)
103 Key Takeaways Files & Folders
Excel Tables, Formulas & Pivot Tables
104 Overview (Pivot) Tables & Formulas
105 Using Excel Formulas in VBA
106 Excel Tables Use the Right Syntax
107 Pivot Tables & VBA What you Need to Know
108 Activity Update Existing Formulas with VBA by Adding IFERROR
109 Project Update Existing Formulas with VBA by Adding IFERROR
110 Key Takeaways (Pivot) Tables & Formula
Main Project #2 Sales Regional Reporting Tool
111 What You’ll Learn (& Should be Aware of..)
112 Project Overview Regional Sales Reporting Tool
113 Importing Data from Multiple Files using MultiSelect
114 Setting up a Template as the Basis for Different Reports
115 Exporting Reports as Excel files (Pivot Table & Hard Coded Reports)
116 Fine-tuning Report Export Procedure (Double-check if user wants to overwrite)
117 Setting up Automatic Export of Text File (Fix delimiter in CSV to Semicolon)
118 Track the List of Tasks Completed with VBA
119 Final Touches & Creating Interface to Hide Specific Tabs with a Password
120 Key Takeaways & Tips for VBA based Excel Tools
121 Second Milestone Completed
Interacting with Other Applications (Word, PowerPoint etc.)
122 Overview & Important Concepts when Interacting with Other Applications
123 Project PDF Save Specific Sheets as a PDF File
124 Project Email Automatically Create Personalized Emails with Attachments
125 Project Microsoft Word Create Personalized Letters in Word from Excel
126 Project Microsoft PowerPoint Export Excel Sheets to PowerPoint Presentation
127 Key Takeaways when Working with other Applications
Workbook and Worksheet Events
128 Overview & What are Event Procedures
129 Workbook Events, Toggle Full Screen (Activate, Deactivate, Open, Close)
130 Worksheet Selection Change Dynamic Font Color of Selected Range
131 Refresh PivotTables Automatically & Concept of Range Intersection
132 Resetting a Dependent Drop-down when the first Drop-down is Changed
133 Activity Link Page Header to Cell Value (BeforePrint Event)
134 Project Link Page Header to Cell Value (BeforePrint Event)
135 Useful Tips when Working with Events & Key Takeaways
Working with Userforms & ActiveX Controls
136 Overview UserForms & ActiveX Controls
137 ActiveX Controls What you Need to Know
138 ActiveX Check Box to Fix Scroll Area & Toggle Screen Settings
139 ActiveX to Show and Hide Help
140 ActiveX Combo Box for Easy Sheet Navigation (Great for Larger Workbooks)
141 ActiveX to Show or Hide Other ActiveX controls (eg. Actual & Budget reporting)
142 UserForm Basics What You Need to Get Started
143 UserForm for Data Entry (Input & Check Boxes)
144 UserForm to Document External Links & Comments (Combo Box & Option button)
145 Bonus ListBox to Print Multiple Pages in ONE GO
146 UserForm Checklist & Course Map Status
Main Project #3 Invoice Generation Tool
147 Tool Overview What You Will Learn
148 Setting up an Input UserForm to Collect Master Data
149 Working with Multi-Column ListBoxes
150 Edit Master Data with UserForm
151 Searchable ListBox (with Selection Change event)
152 Enhanced Searchable ListBox Write Selection back to Excel Table
153 MultiPage UserForm to Create Invoices & Email (Setup)
154 Export PDF & Excel Workbook for Each Invoice based on ListBox Selection
155 Create Email in Outlook & Attach PDF Invoice
156 Final Updates & Debugging
157 Last Milestone Completed
Create your own Custom Functions
158 Section Overview Function Procedures
159 Setting up a Simple Function (including Optional Arguments)
160 Function to Calculate Age (Result in Years & Months)
161 Functions that Split Text and Numbers from Cell Value
162 Using Functions in Sub Procedures
163 Activity Function That Sums based on Cell Color
164 Project Function That Sums based on Cell Color
165 Key Takeaways Function Procedures in VBA & Course Map Status
Working with Charts
166 The Chart Object Library & Section Overview
167 Create and Modify a Chart with VBA
168 Animated Charts Different Methods
169 Show Charts inside UserForms
170 Key Takeaways Charts
Summary & Final Words
171 Your Next Steps & more Learning
172 BONUS Lecture More from Leila
Resolve the captcha to access the links!