Excel VBA Basic to Advance Part2 ( 16 Hours)

Name: Excel VBA Basic to Advance Part2 ( 16 Hours)
Rating: 4.5 (13 reviews)

In-depth study of how to deal with 100s of files insider folders, text file data handling and SuperPowerful USERFORMS

Created byajay parmar

Last updated 8/2024

English

English [Auto],

What you'll learn

In this VBA Part2 ,We are focusing on four topics and 4 sections are there for you - File & Folder Handling ,USERFORMS,Error handlers & Workbook/Sheets Events
It has 3 sections and knowledge of arrays, loops, if statements is must before you join this course. You can check VBA Part-1, if you want to learn all this.
You shall learn how to create excel files or folders , text files, manipulate data to any extent
Taking a deep dive into library called Scripting and learning the classes of this library like fiesystemobject, file, folder and text stream
You shall be able to pick data from hundreds of excel or text files and will be able to collate it under one roof and then do whatever is required.
Creating workbooks, text files, folders, jumping inside subfolders, putting conditions, modifying the data , whatever is required, you shall be able to do it.
You shall be able to learn how to create VBA Dashboards using UserForms. Its fully basic to advance course
You shall be able to understand why it is important to know error handlers like On Error Resume Next and Go to Err.
Also, how to create message and input box with real time examples
You shall learn how to trigger vba code without running them. It is called VBA Events.
How to work with EVENTS like open event, sheet activate event, sheet change, selection change events
How to handle problems like one event calling another event when you did not even think in that way.
Use of global variables in Events - Why it is useful

Course content

4 sections • 41 lectures • 16h 5m total length

What are we going to learn and what I should already know - A quick walkthrough5:59
Introduction to Library called Scripting32:45
How to Import file information in excel from any folder10:33
Use the file system object and folder class to import file information from any folder into Excel, iterating files to capture name, date created, size, and type.
How to combine data from different workbooks - Amazing, impactful18:16
Learn to merge data from multiple Excel workbooks by scanning a folder, opening matching files, copying sheet data, and compiling into a single workbook.
How to copy or move folders and files10:08
Learn how to copy and move folders and files in Excel VBA, handling destinations as strings, using the folder class, and resolving path syntax issues.
Create a folder of your desire19:15
Learn to automate monthly data organization in Excel with VBA by creating month folders using the file system object, checking folder existence, and moving files into the correct directory.
What are file dialogs and Role23:19
Use Vlookup - FileDialogs are so powerful20:26
Open file dialog introduction - Use to open excel files4:59
Explore how to open multiple Excel files with the Open File dialog in VBA using a loop and index-based handling, avoiding fixed file counts and memory issues.
SaveAs file dialog now4:22
Get subfolder names from any folder32:18
Learn to get subfolder names from any folder using VBA, with nested loops for folders and files and a dynamic file dialog to export results to Excel.
Let us start with text files - take your levels to new heights1:52
Explore how to create and manage text files in Excel VBA using the filesystem object class, including checking files exist, folders, and drives, and creating a text file named practice.
Open and Read text files data8:48
Learn how to open and read text files in Excel VBA, using the text stream to read line by line and monitor end-of-file via the immediate window.
Import text files in excel8:41
Open and read a text file in Excel, then import its data line by line. Split each line by a delimiter like space or tab and write into worksheet columns.
Write data in text files - be a All Rounder21:20
Learn how to write data from one text file to another in Excel VBA, using open modes for write or append, handling new lines, and avoiding unintended overwrites.
Check your knowledge
How to link pictures from a folder - Student Question24:01
Learn how to automatically import and display pictures from a folder into Excel cells using VBA, the File System Object, and a before double-click event to insert or remove images.

What are the UserForms - Create and understand them18:03
Know combo box, option buttons,frames, text box etc23:44
Learn to build an Excel VBA user form with combo box, text box, option buttons, and frames; arrange controls, use data validation, and link a submit button to a database.
Userform Events - Why you need them and how to use these events47:17
Explore Excel VBA userform events, including form initialization and control changes, and learn naming conventions, private macros, and how to run the form from Excel.
How to do coding - Insert your data in excel from form controls24:11
Learn Change event, after update, Queryclose and more - Super duper fun26:10
Explore Excel VBA user form events such as change and after update, build a dynamic calculator, handle blanks and type mismatches, and control form closing with queryclose and unload logic.
Let us check our Knowledge
Our ListBox in forms- its significant and its huge learning15:40
Explore how to use list boxes in VBA forms, including initializing data, handling events, and enabling single or multi select with extended mode for dynamic item lists.
How to get what is selected in a List Box7:03
Learn to fetch selected items from a list box by looping through items, using list count and the selected property, and retrieving values with indexing for single and multi-select modes.
Show files in a Listbox if a folder changes in combo box - Another level27:06
Build a vba user form that uses a combo box to select a folder and populate a listbox with Excel files from that folder, using scripting runtime and proper events.
Project for you - More live case study means more confidence20:30
Explore a hands-on excel vba project that ties together list boxes, checkboxes, option buttons, frames, and command buttons to build an interactive form with multi-select using shift and ctrl.
How to move up and down the listbox items - Super knowledge project32:36
Advance Level Dashboard using USER FORM - Challenge your limits1:03:13
Finishing the Dashboard - Final Touch44:34
Finish the dashboard with a multi-column list box and a ticket-based advanced filter search. Initialize forms, double-click to pull details from the database, and manage data and statuses.

Error Handlers - Why we need them - Deep Dive43:52
Master error handling in Excel VBA by using on error resume next and on error go to, exploring two error types with VLOOKUP examples and multi-sheet scenarios.
Project for you18:24
How to modify text file data - Student question16:58
Pls change the path used in this code.
Message box - intro17:00
Discover how to use the VBA message box to display prompts, choose buttons (yes, no, cancel), and drive macro logic with safe exit on cancel.
INPUT BOX Introduction6:57
Application inputbox - set rules on data type12:50
Explain how to use an application input box to select a range, enforce range-type input, and loop through cells to bold those matching a given value, using range addresses.

Introduction to EVENTS- Let us know them12:19
Learn to use workbook and sheet level events in Excel VBA, including the workbook open event, change, and selection change, to create self-triggered macros for sheet activities.
Sheet Activate and Deactivate events36:50
Explore sheet activate and before delete events in Excel VBA, learn how to prompt users, copy sheets, and manage event flow with module-level variables.
Sheet Change and Selection Change Events38:30
Learn how to track changes in Excel using sheet change and selection change events, log the old and new values, username, and date to a log sheet.
Project - how to track user activities on spreadsheet52:30
Master Excel VBA techniques to track user activities on a spreadsheet, logging changes with worksheet change events, date, username, and target values.
Your question my solution36:19
How to handle more than one cell - Copy table and paste issues solved - Awesome1:07:44
Data validation issue resolved8:34
Check your progress

Requirements

VBA Knowledge is required since this is our Part2. Knowlege of variables, arrays, loops ,functions is required. One can watch VBA Part1 , if haven't before opting for this part2.
If you already know above topics then please go ahead and take admission in this course.

Description

In section1, We are Learning to deal with small or large data stored in excel workbooks or text files using a library called SCRIPTING which offers us amazing classes like FileSystemObject , Folder and Files and Textstream.
- How to go inside a folder and pick the needed files and then do the things like compiling data from them or modifying the files or moving them or deleting them.
- Learn how to use the file-dialogs so that user can choose any folder of his or her choice and work accordingly.
- Not just folders or Excel files -we are also going to see what to do if we have to overwrite or append the data in text files. Yes, we can even control the text files using scripting library, Very useful especially when you are dealing with big data.
- Skip the files while running loops which you do not want. Set the filters in the extensions using file dialogs. Learn all types of file dialogs like open dialog. save as dialog, folder dialog and also why and where we can use them.
- Customization of file dialog buttons, titles to how to open multiple files with them to make more user friendly and robust program which can be run in any machine irrespective of the drives or folder paths they have.
- Learn to manipulate text files using scripting library. We have everything discussed here from basic level to advance level when it comes to file and folder manipulations.
- In section2, we have covered all form controls like labels, textboxes, combo, listbox, frame, option buttons, check boxes and many more.
- From understanding the Userform control design and properties, you shall also see the magic of userform events which are very much required to create projects.
- All of your doubts will be answered via email or forum in 24 hours.
- in section3, you shall learn Error handlers like On error resume next and goto err.
- You shall see what are the scenarios when we already know we can have errors - VBA Errors so if we want to ignore those errors and keep continuing running the vba code, how to do that and first of all, why in first place , I will like to ignore the errors.
- We will then learn how to use and customize a message box and input box in vba with various examples.
In section4, we will learn how to work with events which we write inside workbook and sheet modules.
Why they are called self triggered macros and what are the conditions - Events names we can go and use it for different type of jobs.
Learn amazing Events like - Workbook open, Sheet activate, sheet before delete, sheet change event, selection change events.
When one event starts calling another event and you never planned for it, how to avoid this class using public variable
Few projects for you to give you more confidence in Events

Who this course is for:

Excel data analytics, Those who like to automate data work in excel.
Those who want to create dashboards by using userforms of VBA.

Excel VBA Basic to Advance Part2 ( 16 Hours)

What you'll learn

Explore related topics

Course content

Introduction to Scripting Library to work with Files, folders and text files16 lectures • 4hr 7min

VBA UserForms for Dashboards12 lectures • 5hr 50min

Error Handlers and input/Message Boxes6 lectures • 1hr 56min

Worksheet and Workbook Events - Self trigger Macros - in detail7 lectures • 4hr 13min

Requirements

Description

Who this course is for: