Excel Vba And Macros

Overview: Macros & Visual Basic Editor (VBE) Preview 01:40

Steps to Your First Macro Recording Preview 06:01

Visual Basic Editor: What You Need to Know Preview 11:56

Project: Dynamic Cell Selection (Special Cells) - Perfect for Flexible Macros Preview 11:28

Project: Copy (Consolidate) Data - Absolute versus Relative Macro Recording Preview 09:33

7 Ways to Run Macros / VBA code (incl. creative & modern buttons) Preview 12:24

OFFICE 365 Update: Change from Excel Comments to Notes Preview 00:38

Activity: Record a Macro that Deletes all Comments Preview 00:35

What's the Difference Between Macros and VBA? Preview 01:07

Quiz: Test Your Knowledge on Macros

Please fill out the following 4 questions to ensure you're ready to enter the next section.

Overview: VBA Object Model (What you need to become an expert in VBA) Preview 01:39

To become an expert in Excel VBA, it's important to fully understand the Object Model. This way you'd be able to write the correct VBA syntax for your Excel projects (without guessing!). Selecting the wrong combination of objects, properties and methods will result in errors in your code.

VBA's Code Reference Object Library Preview 04:12

Writing VBA code is all about manipulating objects on the screen. These objects have code words for easier reference. This code can be found in the VBA reference library. In this lecture you'll learn how to use the Excel VBA reference library so you can find the correct code for your Excel macro.

VB Color Guidelines & Keyboard Shortcuts Preview 12:09

In this lecture we cover a few essential Excel VBA concepts including what a sub and function procedures are and what the different color codes mean. We also learn some useful shortcuts we can use the VBA editor.

Excel VBA Object Model Preview 06:17

In this lecture we get into the setup of the object model. VBA syntax is object first, then action. Objects can also have relationships with other objects - you need to be aware of the object hierarchy. It gets really interesting and fun.

Object Properties Preview 04:36

In this lecture you learn all about Excel VBA object properties and what these are. 

Object Methods Preview 05:54

When you start to learn Excel VBA, it's important to understand what methods are and how you can use them together with objects to automate tasks.

How to Find the Object, Property & Method Preview 08:16

VBA syntax is not easy to remember. How do you find the correct object, property or method that you need for your code? 

Test Your Knowledge about the Object Model

Make sure you answer these before moving on to the next section.

Conclusion: Key Takeaways & Message from Me Preview 02:58

Overview: Referencing Ranges, Worksheets & Workbooks Preview 01:12

Referring to Ranges & Writing to Cells in VBA Preview 15:37

This lecture is a complete guide that teaches you how to refer and write to a range or to cells in Excel VBA. You'll learn how to change a cells value with VBA by using range and the cells property, the offset property. You'll also learn how to refer to different ranges in one statement, how to select a row or a column with VBA and how the used range property works.

Most Useful Range Properties & Methods Preview 13:19

4 Methods to Find the Last Row of your Range Preview 15:34

Find out how find the last empty row or the last empty column using different methods. Some work also with blank cells inside the data set. We take a look at using the end property of the range object. Then the current region property and the used range property of the worksheet object. We also take a look at using SpecialCells method to find the last used cell in the worksheet.

Copying & resizing a variably sized range Preview 09:28

Properly Referencing Worksheets Preview 14:50

Properly Referencing Workbooks Preview 10:21

Activity: Save a Hard-coded Copy of Workbook Preview 00:55

Project: Save Hard-coded Copy & Macro-free version of workbook Preview 12:24

Key Takeaways for Referencing Ranges Preview 03:03

Overview: Role of Assignment & Variables Preview 02:55

Data Types for Better Performance Preview 03:33

Declaring Variables, Arrays & Constants (Role of Option Explicit) Preview 12:52

Using Object Variables (Set statement) Preview 06:50

Scoping Variables Preview 06:58

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

Test Your Knowledge on Variables and data types

Key Takeaways for Working with Variables Preview 02:15

Overview: Why Collections are a VBA Essential Preview 01:27

With & End With for Easier Code Writing Preview 06:21

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

IF Then (Else, ElseIF) for Conditional Outcomes Preview 10:20

Select Case as Alternative for Many IF Statements Preview 05:21

Goto Statement to Change Program Flow Preview 07:37

Activity: Get the Number of Formulas on the worksheet Preview 00:15

Project: Get the Number of Formulas on the Worksheet Preview 07:00

Bonus: Unhide All Sheets in One Go Preview 10:09

Learn 3 ways how you can unhide all Excel tabs in one go. You can also apply this to all macro-free Excel Workbooks.

Key Takeaways for Collections & Decision Making in VBA Preview 00:05

Overview: VBA versus Worksheet Functions Preview 13:38

Most Useful VBA Functions Preview 10:05

Message Box (also with Yes, No buttons) Preview 08:43

Input Box (VBA InputBox Function) Preview 07:28

Input Box that can Select Ranges (Excel InputBox Method) Preview 12:28

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

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

Key Takeaways Built-in Functions Preview 00:05

Overview: Debugging, Handling Errors & Procedure Scope Preview 02:00

Debugging Options: Watch, Locals, Immediate Windows & More Preview 13:10

Error Handling: Different Methods Preview 10:47

Faster Code by Suppressing Pop-ups & Flickering Screen Preview 10:39

Procedure Scope & Running one Sub from Another Preview 06:23

Passing Arguments to Sub Procedures (By Ref, By Val) Preview 09:50

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

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

Key Takeaways Debugging, Error Handling & Course Map Status Preview 01:57

Project overview: Table of Contents (TOC) with one click Preview 02:01

TOC Project: The Basics Preview 10:33

TOC Project: Adding Hyperlinks Preview 06:11

TOC Project: Testing the VBA Code Preview 04:29

TOC Project: Adding Error Handling & More Checks Preview 13:29

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

First Milestone Completed! Preview 00:07

Section Overview: Looping in VBA Preview 02:28

For Next Counter Loops (Simple Example) Preview 08:49

For Next Nested Loop (Loop through Text & Cells) Preview 15:35

For Next Reverse Loop (Delete Filtered/Hidden Rows) Preview 08:37

Do Until & Do While Loop Preview 09:57

Practical Example of Do Loop (with Inputbox) Preview 03:22

Find Method for Quicker Lookup Results Preview 09:02

Find Method with Do Loop - Multiple Matches Preview 06:24

Add a Timer to Test & Speech to Inform Preview 07:56

Activity: Document all Comments in Workbook in a Sheet Preview 00:32

Project: Document all Comments in Workbook in a Sheet Preview 12:31

Useful VBA Statements & Key takeaways for Looping in VBA Preview 03:30

Overview: Working with Arrays in VBA Preview 01:44

Learn how to use arrays in Excel VBA. 

One Dimensional Arrays (and transferring back to sheet) Preview 13:42

Dynamic Arrays (size adjusts depending on a condition) Preview 06:41

Dynamic arrays can be really useful in VBA. These are needed for case when you don't know what the array size is at the beginning of your code. The size is found and defined while the macro runs. You will need to use the redim statement to define the size the array, before you fill up the array with values.

Preserving Dynamic Arrays (adding more elements to the existing array) Preview 03:56

There might be times where you need a dynamic array, but you need to preserve the first values inside the array. The redim preserve statement will help you do just that.

Two Dimensional Arrays Preview 07:36

Variant Arrays: Quick Method to Write to Cells Preview 08:39

Activity: Create a New Worksheet per Company with Specific Information Preview 00:25

Project: Create a New Worksheet per Company with Specific Information Preview 09:36

Key Takeaways Arrays Preview 00:05

Overview: Working with Folders & Files Preview 02:46

DIR Function: Check if Files or Folders Exist Preview 09:12

GetOpenFileName Method: User Selects a File Preview 09:27

FileDialog Property: Loop Inside a Folder Preview 16:50

Create CSV File from Data in Excel Preview 07:36

Printing & Writing to Text Files for More Control Preview 06:47

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

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

Key Takeaways Files & Folders Preview 00:05

Overview: (Pivot) Tables & Formulas Preview 02:10

Using Excel Formulas in VBA Preview 13:46

Excel Tables: Use the Right Syntax Preview 14:19

Pivot Tables & VBA: What you Need to Know Preview 18:30

Activity: Update Existing Formulas with VBA by Adding IFERROR Preview 00:27

Project: Update Existing Formulas with VBA by Adding IFERROR Preview 08:14

Key Takeaways (Pivot) Tables & Formula Preview 00:05

What You'll Learn (& Should be Aware of..) Preview 03:58

Project Overview: Regional Sales Reporting Tool Preview 14:10

Importing Data from Multiple Files using MultiSelect Preview 16:55

Setting up a Template as the Basis for Different Reports Preview 25:20

Exporting Reports as Excel files (Pivot Table & Hard Coded Reports) Preview 17:01

Fine-tuning Report Export Procedure (Double-check if user wants to overwrite) Preview 11:05

Setting up Automatic Export of Text File (Fix delimiter in CSV to Semicolon) Preview 08:30

Track the List of Tasks Completed with VBA Preview 11:17

Final Touches & Creating Interface to Hide Specific Tabs with a Password Preview 11:06

Key Takeaways & Tips for VBA based Excel Tools Preview 02:30

Second Milestone Completed! Preview 00:07

Overview & Important Concepts when Interacting with Other Applications Preview 05:02

Project PDF: Save Specific Sheets as a PDF File Preview 14:13

Project Email: Automatically Create Personalized Emails with Attachments Preview 09:08

Project Microsoft Word: Create Personalized Letters in Word from Excel Preview 16:10

Project Microsoft PowerPoint: Export Excel Sheets to PowerPoint Presentation Preview 19:08

Key Takeaways when Working with other Applications Preview 00:05

Overview & What are Event Procedures? Preview 03:08

Workbook Events, Toggle Full Screen (Activate, Deactivate, Open, Close) Preview 12:57

Worksheet Selection Change: Dynamic Font Color of Selected Range Preview 05:52

Refresh PivotTables Automatically & Concept of Range Intersection Preview 12:45

Resetting a Dependent Drop-down when the first Drop-down is Changed Preview 10:57

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

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

2 Useful Tips when Working with Events & Key Takeaways Preview 03:27

Overview: UserForms & ActiveX Controls Preview 01:56

ActiveX Controls: What you Need to Know Preview 07:32

ActiveX Check Box to Fix Scroll Area & Toggle Screen Settings Preview 07:54

ActiveX to Show and Hide Help Preview 06:17

ActiveX Combo Box for Easy Sheet Navigation (Great for Larger Workbooks) Preview 10:30

ActiveX to Show or Hide Other ActiveX controls (eg. Actual & Budget reporting) Preview 13:50

UserForm Basics: What You Need to Get Started Preview 20:17

UserForm for Data Entry (Input & Check Boxes) Preview 14:06

UserForm to Document External Links & Comments (Combo Box & Option button) Preview 20:37

Bonus: ListBox to Print Multiple Pages in ONE GO Preview 10:56

UserForm Checklist & Course Map Status Preview 00:05

Tool Overview: What You Will Learn Preview 14:08

In this project we cover different techniques:

1. How to work with user forms in Excel VBA (creating input user forms, forms to edit data and multicolumn listboxes)
2. How to use excel event procedures to create searchable drop downs or searchable lists
3. How to automatically create PDF documents
4. How to attach PDF Documents to email (using Microsoft Outlook)
5. How to create email body text from Excel using early binding technique (connecting to Outlook from Excel)
6. How to use Active X controls to hide columns and filter and un-filter a list.
7. How to design elegant Excel applications that use smart formulas as well as VBA.

Setting up an Input UserForm to Collect Master Data Preview 17:34

You'll learn how to create a userform for data entry. How to add validations so that the user cannot submit the data inside the userform unless they have input in multiple text boxes. I show you how you can use tags to make sure the user has input values in the correct fields and basic validation to ensure they have input numbers instead of text in certain input fields.

Working with Multi-Column ListBoxes Preview 10:18

Learn how to create multi-column list boxes inside user forms using the row source property. You'll also learn how to define the size of each column of the listbox as well as how to reference the different listbox items using index numbers.

Edit Master Data with UserForm Preview 07:47

Here you will learn how to view the data from an Excel table inside a user form and how to change the data and send it back to the Excel table. You'll also learn how to call a userform from another userform. 

Searchable ListBox (with Selection Change event) Preview 17:23

Find out how to create searchable Listboxes in Excel (alternative approach to creating a searchable drop-down data validation list). With this searchable list-box you have the ability to refine or update your search inside the form. You can also search for two different criteria. It is more complex to setup but we get to see some advanced Excel VBA techniques at work here. 

We'll use the selection change procedure of the worksheet to trigger our search user-form. Then we fill up the userform values by using VBA's instr function to see if we find a match. We also use a sub-procedure that uses two arguments and which is called from two different procedures - from the change event of the worksheet and from the update event of the text box inside the userform.

Enhanced Searchable ListBox: Write Selection back to Excel Table Preview 16:36

We enhance our searchable user-form to write back the value we select on in the userform to our Excel table. We don't only copy the values inside the form, but we find a match for the selection inside our master data and copy the master data to our table.

MultiPage UserForm to Create Invoices & Email (Setup) Preview 18:35

Here we get the opportunity to work with a multipage control in our userform. By hiding the tabs for the multipage, we can give the effect of having different userforms instead of one form. We also learn how to fill up a list box in a userform with VBA during macro run time. We use the initialize procedure of the user-form to add items to the list box. 

Export PDF & Excel Workbook for Each Invoice based on ListBox Selection Preview 23:48

For each report we create, we export a separate Excel file as well as a separate PDF file. We save these files inside different folders. To make sure the folders have been created in the directory, we use the DIR function. 

Create Email in Outlook & Attach PDF Invoice Preview 14:31

At this stage we use the find function in VBA to get the email address of the customer from our master data. We then use early binding and activate the reference to Microsoft Outlook. This gives us intellisense for Outlook. We then take a look at the outlook object library to find the correct objects, properties and methods to be able to create our email with a specific body text, add PDF document as attachment as well as add the email address of the person. We also save the email to our Outlook draft folder.

Final Updates & Debugging Preview 19:24

It's not fun if there's no debugging. Here we catch some errors I've made along the way by updating the code where needed and adding in error handling where required.

Last Milestone Completed! Preview 00:04

Section Overview: Function Procedures Preview 02:02

Setting up a Simple Function (including Optional Arguments) Preview 05:49

Function to Calculate Age (Result in Years & Months) Preview 06:04

Functions that Split Text and Numbers from Cell Value Preview 08:32

Using Functions in Sub Procedures Preview 06:18

Activity: Function That Sums based on Cell Color Preview 00:15

Project: Function That Sums based on Cell Color Preview 11:02

Key Takeaways Function Procedures in VBA & Course Map Status Preview 00:06

The Chart Object Library & Section Overview Preview 03:21

Create and Modify a Chart with VBA Preview 12:59

Animated Charts: Different Methods Preview 12:48

Show Charts inside UserForms Preview 13:21

Key Takeaways: Charts Preview 00:15

Your Next Steps & more Learning Preview 02:31

BONUS Lecture: More from Leila Preview 01:44