Excel VBA(Basic To Super Advance)-English
Excel VBA(Basic To Super Advance)-English
Complete guide to learn Basic and Advance VBA
Created by Ajay Parmar
Material Includes
- Supported files are attached. you may download.
Course Description
- How to do Macro recordings. edit it, run it , debug it.
- How to Run VBA program or delete them or give shortcut keys.
- Know the concept of finding out the last row – blank row or non blank row of your data
- How to select the data from top to bottom or vice versa – static and dynamic approaches with their advantages, disadvantages over each other.
- How to turn wrong steps into correct while recording the steps.
- How to run a program step by step and why we need it for debugging.
- Move from one sheet to another. What errors we can expect if sheet name changes like Subscript out of a Range error.
- Know how to compile data from different sheets and put under one sheet using recording and with basic understanding of sheets and ranges.
- Introduction to Variables and their data types. How to define variables and benefit of using them.
- Data types . Strings. Integer, Single, byte, Double , Variants – we have covered all. With amazing questions coming out from this live classes.
- What if errors like – OverFlow and Type Mismatch come – Know the reason and of course a solution.
- What are debugging windows -Local window
- Why we want Option Explicit – How to declare it and what benefit it provides.
- Frequently asked Questions in your interview related to option explicit and Variables.
- How to connect more than one macro using a Call keyword .
- How long a Variable remains in VBA -Scope of a Variable – Local variable, module, private level and Public or Global level variables.
- We are taking a deep dive into Loops and IF Functions with Select Case statements. You will not need to look back on Loops topic ever if you finish this course till end.
- Detailed discussion on For Next loops – their use and significance. How to mold them in different ways and use in your projects.
- Discussion on Do -While & Until loops from very basic level to advance.
- Which loop is better or how about making a choice in loops over each other.
- Learn loops using the basic and complex patterns in order to get mastery. Print numbers in asc or desc or diagonally patterns.
- Detailed discussion on IF functions and their different syntax’s. For example, Single IFs, IF AND, IF OR and Nested IFs.
- Use of Else-IF and when to use it , How it is different from IFs without Else-IF blocks. With real time examples.
- How to work with hundreds of conditions in one go using IFs.
- What is a Case Select statement- How it can be an alternate to IF conditions in VBA.
- Dangerous of running Loops on F5 mode without testing the code.
- How to write Sub-Loops in Loops- you will find all relevant information here.
- IF with ELSE-IF and IF without Else IF.
- Are we interested in compiling the data from different worksheets and workbooks.
- How to go to different workbooks and pull the data from there and compile it one place.
- How to work with so many sheets without bothering about things – what name they have , if they will get deleted in future or if more sheets get added.
- These collection loops make you a great programmer. in-fact, if you do not know this you can never be a great coder. Come and fall in love with them. Immensely helpful , interesting and powerful. So much fun .
- Learn How to work with workbooks and worksheet – Open, Save, Save as, delete, rename, move, add and many more such commands.
- How to open the workbooks, how to close one workbook, specific workbook or all opened workbooks
- Learn how to delete or add workbooks , how to jump from one workbook to another. From activate files to editing them -we are covering all.
- How to select or delete or protect or rename or add the worksheets in excel.
- How to define the workbooks and worksheets in a professional manner using “Set” Keyword. What is the advantage of declaring objects as classes.
- What happens if we declare set statements anywhere in the code. Does it allowed in vba? Detailed discussion on the proper mentioning of dim and set statements while dealing with objects and classes.
- How VBA works when it comes to writing syntaxes. Discussion on the object hierarchy and its methods and properties.
- Questioning rounds in a live session to students to practice more on set keyword.
- What are collection loops and their basic syntax’s – their practical use
- Loop through worksheets, workbooks and cells in a workbook
- How to copy and paste the data from each of the sheets in a workbook or from different workbooks.
- What if you want to copy the data from multiple workbooks , going to each of the worksheet and copying the data and pasting it in one single workbook. This is going to be done using collection loop.
- What is a Dot Operator and how objects are classified in VBA. Learn about object methods and properties – What are these and what is the difference between them . Their significance in VBA.
- What are arrays . Why we use them? Why they are so powerful and holds so much weightage in VBA .
- What is their by-default index system and how we can change it using option base.
- What are static and dynamic arrays.
- How to use arrays with VBA loops and if functions . They work like magic together.
- What is the use of Preserve word in arrays and how we can use it in projects.
- Complete tutorial with in-depth discussion.
- How to use split function in arrays and what are the key things to be taken care while using split in arrays.
- Questions discussed from VBA interview perspective as well.
- As usual- Assignments are also added to give you confidence and to check the progress.
- How to use Dim or Redim Statements .Their real use in projects. How to use variable value in Arrays and what is the purpose of using them in arrays.
- How to use Split function ,one of the best function we can use in arrays. How arrays work under split function.
- How option Base-1 works for arrays created by split function.
- What is the use of preserve word in arrays and how we can use it to hold the values. What happens if we do not use them.
- How and where we use them . Complete discussion on its use.
- Vlookup use – Lookup Project using Arrays
- Know the two or multi dimensional arrays and their use in practical situation
- We are learning everything in detail about VBA inbuilt and User created Functions from their roots.
- All Lectures are started from basic level and then slowly moving toward advance levels
- Use of Inbuilt functions like Left, StrReverse, mid, find , trim, Lcase, Ucase , Date and Time etc are discussed.
- UDFs – User defined functions in VBA – basic plus advance , both level examples.
- Benefits of UDFs over Inbuilt functions. Many amazing projects are shared in the series.
- Can we pass in parameters in sub routines and call Functions
- How do we run or Call Functions from excel sheets. A mesmerizing thing to learn and implement in your project today.
- How to do testing of your functions like UDFs and Inbuilt VBA Functions.
- What is the process of passing parameters from a sub routine to a function and how it is useful for us.
- Frequently asked questions in your VBA Interview.
- How do we use VLOOKUP in VBA at advance level project discussed. Real time project is discussed.
- How can we use Match function with Loops and VLOOKUP to create super powerful and wonderful practical automation.
- 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.
- We have covered all form controls like labels, textboxes, combo, listbox, frame, option buttons, check boxes and many more.
- How to use loops in list box
- Events role in Userforms like On change, After update, initialize, Activate etc.
- How to link combo box with list boxes.
- How to use Listbox for multiple columns.
- 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.
- 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.
- 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
- We are learning about Chart automation. How to create new charts using VBA. We can fully automate the making of charts using VBA.
- Learn the Chart class called Chart and see how easy it is to create the new charts.
- The role of macro recordings plays a very important role here as most of the code we can record and later customize as per the requirement.
- How to work with existing charts in excel. How to go to each of them one by one and do modifications. Any type of modification like changing chart design or editing labels or chart titles etc
- Understand the Loops importance in charts. You can loop through infinite charts using one simple collection loop.
- Collection loops of charts – to work with multiple charts either to delete them or to edit their design, colors. You can do whatever. Even change the axis’s.
- Use of other Collection loops with chart collection loops. Loops in Loops
- You will become master in Pivots. From making new pivots to working in existing pivots and modifying them
- Collection loops in PIvots – how to access pivots . How to loop pivot field items and fully control it.
- We will learn the classes we use in Pivots to handle new pivots or existing pivots like Pivot-table and Pivot cache classes.
- We will take a deep dive into knowing other classes as well which helps us in controlling the pivot fields and field items like pivot item class and pivot-Field class.
- How to mix worksheet collection loops with pivot collection loops in real time projects and what are the benefits of same
- What is the role of macro recording in making pivots and how to edit those recording if requirement changes.
- How to work with row or column of filter pivot fields. Line by line explanation.
- We are learning how to connect Outlook with Excel VBA.
- Early binding approach and Late Binding Approaches. Advantage/Disadvantages
- Sending auto emails, Customizing the email subject lines, body , signatures , to , cc, bcc ,attachments
- Copying or moving folders in outlook
- Copying or moving emails from one folder to another
- How to extract the files from outlook emails and save them on your machine.
- How to work with default folders, own created folders/subfolders and much more
- Discussing about the connectivity of Excel with PowerPoint in this advance series using Excel VBA.
- How to copy table data from excel and paste into PowerPoint application slides.
- How to export charts from excel sheets to PowerPoint and paste them in one slide or more slides one by one.
- Taking a deep dive into power-point classes like Presentation class, Slide class and shapes class. Understanding the power-point object hierarchy.
- How every class is responsible for other class using the dot hierarchy.
- How to work with already created PowerPoint applications and also how to create new PowerPoint applications and work with them.
- How to resize the charts or tables images in slides and move them to the top or left or right of the slides.
- Control which chart should go in PowerPoint using the excel sheet cells.
- How to loop through all sides and decide which one to be deleted or used for data presentation.
- Collection loops for slides as well as shapes. This is amazing source of learning.
- Real time projects discussion with you to give you live exposure of working with Power-point.
- Understanding the concept of Early Binding and Late Binding methods or techniques
- Learn about ADO Library. Active- X data object library in VBA. Why it is required and how to activate this.
- Know about the ado library classes and use it for database connections
- How to set up connection with access if located on server or local machine using excel VBA
- What are recordsets in ms access using excel vba and how to use strings to connect with databases.
- for more database how to plan the things and see the connections strings for different type of databases.
- How to run loops in access tables
- How to use SQL Commands like select statement, update statement ,delete sql syntax and insert syntax to exchange the information back and forth.
- How to edit or change specific record in ms access creating user forms
Course Curriculum
Introduction to VBA Macro Recordings & Selections
-
What is VBA and why we need it – Do you need it?
14:28 -
Get familiar with VBA Editor important options – Our first step
20:57 -
Record, Edit and Run your first macro – Know from zero level
26:02 -
Sheet and Cells Commands with Debug of a program – Fundamentals
15:16 -
Recording Macro Fundamentals and Mistakes – Important thing to understand
11:25 -
How to go to last row and Data Selection right approach- Static and Dynamic
18:37 -
Project -Data Compile from sheets – its Super fun and practical lecture
32:23 -
Your question – What is personal MacroWorkbook?
17:18
Scope of a Variable
-
Declare Variables and Data Types and Errors you may face
41:56 -
Variant and String Data types with exercises – Fundamentals
28:26 -
Scope of a Variable with Call keyword – Local, module and Public .Global
36:21 -
Option Explicit and Interview preparation
13:17
VBA Loops with IFs
-
For Next Loop – First step
18:24 -
For next with more offset and more variations – Super Fun
09:42 -
Print numbers using Cells command and learn why it is important to know
04:20 -
Exercise for you – Make loop diagonally – Print numbers
06:10 -
How to Reverse a Loop – Become a Master now in Loops forever
06:03 -
Mastery over Sub Loops – Loop inside Loop -Achieve Magnificent level
15:37 -
IF statements in VBA – Full in-depth discussion is on
28:06 -
Quiz for you – Use IF with Loops on Real Data
24:36 -
Do While Loop begins
46:56 -
What is IF with ElseIf & IF without Elseif – Meaning & Difference – Just awesome
19:34 -
Select with End Select – What is it and benefits
05:51
Collection Loops and define Object/Classes
-
Begin with Workbook Commands
41:34 -
ThisWorkbook and ActiveWorkbook difference – Interview question
39:35 -
Know what is a Class and its methods and properties- fundamentals
51:39 -
Define own Objects using Set Keyword – A new level for you
47:04 -
How to use Worksheet , Range and Workbook Classes – TIme to do practical
35:27 -
Collection Loops begins – Work with Sheets
16:52 -
PROJECT – import all sheets names using Collection Loop
15:02 -
Super Advance Project – Data Compile – It cannot be better than this
18:47 -
Our second Collection Loop – Deal with Workbooks now
17:16 -
More Advance Project – Data Compile with more conditions
01:09:20
Arrays
-
How to Define and Fill Arrays – Why we need Arrays
23:02 -
Dynamic Arrays
19:06 -
Preserve function in Arrays – Take your skills to a new level
41:48 -
How to use Two or Multi Dimensional Arrays
01:08:05 -
Project for you- More confidence and more clarity
26:16 -
Challenge Lookup made simple with Arrays- Super Advance
59:57
Functions
-
Begin with UDF- User Defined Function
26:50 -
More examples of UDF
01:14:24 -
How to use SPLIT Function- Powerful
26:46 -
Inbuilt TEXT functions – len, ucase,lcase,strReverse
21:24 -
Text Inbuilt functions Left Right Find Mid
10:32 -
Vlookup Project
13:33 -
Challenge – VLOOKKUP and MATCH functions in VBA
18:58 -
Date functions in VBA
11:01 -
UDF Advance Challenge Vlookup – Find any instance of Lookup
18:28
Files and Folders
-
Introduction to Library called Scripting
32:46 -
How to Import file information in excel from any folder
10:34 -
How to combine data from different workbooks – Amazing, impactful
18:17 -
How to copy or move folders and files
10:09 -
Create a folder of your desire
19:16 -
What are file dialogs and Role
23:20 -
Use Vlookup – FileDialogs are so powerful
20:27 -
Open file dialog introduction – Use to open excel files
05:00 -
SaveAs file dialog now
04:22 -
Get subfolder names from any folder
32:18 -
Let us start with text files – take your levels to new heights
01:53 -
Open and Read text files data
08:48 -
Import text files in excel
08:42 -
Write data in text files – be a All Rounder
21:20
UserForms
-
What are the UserForms – Create and understand them
18:04 -
Know combo box, option buttons,frames, text box etc
23:45 -
Userform Events – Why you need them and how to use these events
47:17 -
How to do coding – Insert your data in excel from form controls
24:11 -
Learn Change event, after update, Queryclose and more – Super duper fun
26:11 -
Our ListBox in forms- its significant and its huge learning
15:41 -
How to get what is selected in a List Box
07:03 -
Show files in a Listbox if a folder changes in combo box – Another level
27:07 -
Project for you – More live case study means more confidence
20:31 -
How to move up and down the listbox items – Super knowledge project
32:37 -
Advance Level Dashboard using USER FORM – Challenge your limits
01:03:14 -
Finishing the Dashboard – Final Touch
44:34
Error Handlers
-
Error Handlers – Why we need them – Deep Dive
43:52 -
Project for you
18:25 -
How to modify text file data – Student question
16:58 -
Message box – intro
17:01 -
INPUT BOX Introduction
06:57 -
Application inputbox – set rules on data type
12:51
Events – Self Triggered Macros
-
Introduction to EVENTS- Let us know them
12:20 -
Sheet Activate and Deactivate events
36:51 -
Sheet Change and Selection Change Events
38:31 -
Project – how to track user activities on spreadsheet
52:31 -
Your question my solution
36:20 -
How to handle more than one cell – Copy table and paste issues solved – Awesome
01:07:45 -
Data validation issue resolved
08:34
VBA Charts
-
Introduction to Charts – How to Create
21:36 -
Errors Solutions in VBA – Charts are easy if you understand fundamentals
06:34 -
How to Create multiple Charts
17:25 -
Alignment in Charts – Resizing and dimensioning
14:30 -
Collection Loop role in Charts
15:35
VBA Pivots
-
Create Pivots in VBA – Begin the show
22:44 -
How to add Pivot Fields
13:24 -
Collection Loops in Pivot Fields
08:26 -
How to retrieve fields
03:47 -
Control Pivot fields from excel cells
28:52 -
Project for you – Gain more confidence
05:33 -
Work with Pivots across Worksheets
15:11 -
Loop Slicers in Pivots
23:41 -
How to control Slicers and then Pivots – SuperPowerful
24:58
Connectivity with Outlook
-
Outlook Connectivity – know outlook Classes
01:25:58 -
Import emails and how to send emails
42:55 -
Move Emails and Folders in Outlook
33:05 -
How to set up the date ranges and pick emails having specific subject line
26:56 -
Send different attachments with different subject-lines to different recipients
20:33 -
How to track replies of emails and move them in a new folder
24:18 -
Paste data as snapshot
14:42 -
Sending multiple snapshots in single email.
33:39 -
Excel report numbers on email body
23:31 -
Early and Late Binding
57:43 -
Send Specific data range to specific managers
20:06
Connectivity with PowerPoint application
-
Connecting powerpoint application with VBA
01:12:43 -
Export Charts in PPT
24:47 -
Paste Charts in existing slides
24:11 -
Collection Loops in Powerpoint
52:32 -
Project for you
59:22 -
Proejct2
32:25 -
Project3
01:01:23 -
Your question is Solved – Insert a slide after every five slides into existing PowerPoint
21:19
Database Connectivity
-
Connecting Database
01:10:39 -
Connecting database2
38:03 -
Update records in database
01:12:16 -
Delete and Modify database
56:54 -
insert data in database
32:23 -
Form break Timer- project
44:56 -
Make break timer Part2
01:04:41
₹899.00
₹4,500.00
-
LevelAll Levels
-
Total Enrolled103
-
Duration55 hours
-
Last UpdatedFebruary 5, 2023
Hi, Welcome back!
Excel VBA(Basic To Super Advance)-English
₹899.00
₹4,500.00
-
LevelAll Levels
-
Total Enrolled103
-
Duration55 hours
-
Last UpdatedFebruary 5, 2023
Material Includes
- Supported files are attached. you may download.
Course Description
- How to do Macro recordings. edit it, run it , debug it.
- How to Run VBA program or delete them or give shortcut keys.
- Know the concept of finding out the last row – blank row or non blank row of your data
- How to select the data from top to bottom or vice versa – static and dynamic approaches with their advantages, disadvantages over each other.
- How to turn wrong steps into correct while recording the steps.
- How to run a program step by step and why we need it for debugging.
- Move from one sheet to another. What errors we can expect if sheet name changes like Subscript out of a Range error.
- Know how to compile data from different sheets and put under one sheet using recording and with basic understanding of sheets and ranges.
- Introduction to Variables and their data types. How to define variables and benefit of using them.
- Data types . Strings. Integer, Single, byte, Double , Variants – we have covered all. With amazing questions coming out from this live classes.
- What if errors like – OverFlow and Type Mismatch come – Know the reason and of course a solution.
- What are debugging windows -Local window
- Why we want Option Explicit – How to declare it and what benefit it provides.
- Frequently asked Questions in your interview related to option explicit and Variables.
- How to connect more than one macro using a Call keyword .
- How long a Variable remains in VBA -Scope of a Variable – Local variable, module, private level and Public or Global level variables.
- We are taking a deep dive into Loops and IF Functions with Select Case statements. You will not need to look back on Loops topic ever if you finish this course till end.
- Detailed discussion on For Next loops – their use and significance. How to mold them in different ways and use in your projects.
- Discussion on Do -While & Until loops from very basic level to advance.
- Which loop is better or how about making a choice in loops over each other.
- Learn loops using the basic and complex patterns in order to get mastery. Print numbers in asc or desc or diagonally patterns.
- Detailed discussion on IF functions and their different syntax’s. For example, Single IFs, IF AND, IF OR and Nested IFs.
- Use of Else-IF and when to use it , How it is different from IFs without Else-IF blocks. With real time examples.
- How to work with hundreds of conditions in one go using IFs.
- What is a Case Select statement- How it can be an alternate to IF conditions in VBA.
- Dangerous of running Loops on F5 mode without testing the code.
- How to write Sub-Loops in Loops- you will find all relevant information here.
- IF with ELSE-IF and IF without Else IF.
- Are we interested in compiling the data from different worksheets and workbooks.
- How to go to different workbooks and pull the data from there and compile it one place.
- How to work with so many sheets without bothering about things – what name they have , if they will get deleted in future or if more sheets get added.
- These collection loops make you a great programmer. in-fact, if you do not know this you can never be a great coder. Come and fall in love with them. Immensely helpful , interesting and powerful. So much fun .
- Learn How to work with workbooks and worksheet – Open, Save, Save as, delete, rename, move, add and many more such commands.
- How to open the workbooks, how to close one workbook, specific workbook or all opened workbooks
- Learn how to delete or add workbooks , how to jump from one workbook to another. From activate files to editing them -we are covering all.
- How to select or delete or protect or rename or add the worksheets in excel.
- How to define the workbooks and worksheets in a professional manner using “Set” Keyword. What is the advantage of declaring objects as classes.
- What happens if we declare set statements anywhere in the code. Does it allowed in vba? Detailed discussion on the proper mentioning of dim and set statements while dealing with objects and classes.
- How VBA works when it comes to writing syntaxes. Discussion on the object hierarchy and its methods and properties.
- Questioning rounds in a live session to students to practice more on set keyword.
- What are collection loops and their basic syntax’s – their practical use
- Loop through worksheets, workbooks and cells in a workbook
- How to copy and paste the data from each of the sheets in a workbook or from different workbooks.
- What if you want to copy the data from multiple workbooks , going to each of the worksheet and copying the data and pasting it in one single workbook. This is going to be done using collection loop.
- What is a Dot Operator and how objects are classified in VBA. Learn about object methods and properties – What are these and what is the difference between them . Their significance in VBA.
- What are arrays . Why we use them? Why they are so powerful and holds so much weightage in VBA .
- What is their by-default index system and how we can change it using option base.
- What are static and dynamic arrays.
- How to use arrays with VBA loops and if functions . They work like magic together.
- What is the use of Preserve word in arrays and how we can use it in projects.
- Complete tutorial with in-depth discussion.
- How to use split function in arrays and what are the key things to be taken care while using split in arrays.
- Questions discussed from VBA interview perspective as well.
- As usual- Assignments are also added to give you confidence and to check the progress.
- How to use Dim or Redim Statements .Their real use in projects. How to use variable value in Arrays and what is the purpose of using them in arrays.
- How to use Split function ,one of the best function we can use in arrays. How arrays work under split function.
- How option Base-1 works for arrays created by split function.
- What is the use of preserve word in arrays and how we can use it to hold the values. What happens if we do not use them.
- How and where we use them . Complete discussion on its use.
- Vlookup use – Lookup Project using Arrays
- Know the two or multi dimensional arrays and their use in practical situation
- We are learning everything in detail about VBA inbuilt and User created Functions from their roots.
- All Lectures are started from basic level and then slowly moving toward advance levels
- Use of Inbuilt functions like Left, StrReverse, mid, find , trim, Lcase, Ucase , Date and Time etc are discussed.
- UDFs – User defined functions in VBA – basic plus advance , both level examples.
- Benefits of UDFs over Inbuilt functions. Many amazing projects are shared in the series.
- Can we pass in parameters in sub routines and call Functions
- How do we run or Call Functions from excel sheets. A mesmerizing thing to learn and implement in your project today.
- How to do testing of your functions like UDFs and Inbuilt VBA Functions.
- What is the process of passing parameters from a sub routine to a function and how it is useful for us.
- Frequently asked questions in your VBA Interview.
- How do we use VLOOKUP in VBA at advance level project discussed. Real time project is discussed.
- How can we use Match function with Loops and VLOOKUP to create super powerful and wonderful practical automation.
- 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.
- We have covered all form controls like labels, textboxes, combo, listbox, frame, option buttons, check boxes and many more.
- How to use loops in list box
- Events role in Userforms like On change, After update, initialize, Activate etc.
- How to link combo box with list boxes.
- How to use Listbox for multiple columns.
- 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.
- 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.
- 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
- We are learning about Chart automation. How to create new charts using VBA. We can fully automate the making of charts using VBA.
- Learn the Chart class called Chart and see how easy it is to create the new charts.
- The role of macro recordings plays a very important role here as most of the code we can record and later customize as per the requirement.
- How to work with existing charts in excel. How to go to each of them one by one and do modifications. Any type of modification like changing chart design or editing labels or chart titles etc
- Understand the Loops importance in charts. You can loop through infinite charts using one simple collection loop.
- Collection loops of charts – to work with multiple charts either to delete them or to edit their design, colors. You can do whatever. Even change the axis’s.
- Use of other Collection loops with chart collection loops. Loops in Loops
- You will become master in Pivots. From making new pivots to working in existing pivots and modifying them
- Collection loops in PIvots – how to access pivots . How to loop pivot field items and fully control it.
- We will learn the classes we use in Pivots to handle new pivots or existing pivots like Pivot-table and Pivot cache classes.
- We will take a deep dive into knowing other classes as well which helps us in controlling the pivot fields and field items like pivot item class and pivot-Field class.
- How to mix worksheet collection loops with pivot collection loops in real time projects and what are the benefits of same
- What is the role of macro recording in making pivots and how to edit those recording if requirement changes.
- How to work with row or column of filter pivot fields. Line by line explanation.
- We are learning how to connect Outlook with Excel VBA.
- Early binding approach and Late Binding Approaches. Advantage/Disadvantages
- Sending auto emails, Customizing the email subject lines, body , signatures , to , cc, bcc ,attachments
- Copying or moving folders in outlook
- Copying or moving emails from one folder to another
- How to extract the files from outlook emails and save them on your machine.
- How to work with default folders, own created folders/subfolders and much more
- Discussing about the connectivity of Excel with PowerPoint in this advance series using Excel VBA.
- How to copy table data from excel and paste into PowerPoint application slides.
- How to export charts from excel sheets to PowerPoint and paste them in one slide or more slides one by one.
- Taking a deep dive into power-point classes like Presentation class, Slide class and shapes class. Understanding the power-point object hierarchy.
- How every class is responsible for other class using the dot hierarchy.
- How to work with already created PowerPoint applications and also how to create new PowerPoint applications and work with them.
- How to resize the charts or tables images in slides and move them to the top or left or right of the slides.
- Control which chart should go in PowerPoint using the excel sheet cells.
- How to loop through all sides and decide which one to be deleted or used for data presentation.
- Collection loops for slides as well as shapes. This is amazing source of learning.
- Real time projects discussion with you to give you live exposure of working with Power-point.
- Understanding the concept of Early Binding and Late Binding methods or techniques
- Learn about ADO Library. Active- X data object library in VBA. Why it is required and how to activate this.
- Know about the ado library classes and use it for database connections
- How to set up connection with access if located on server or local machine using excel VBA
- What are recordsets in ms access using excel vba and how to use strings to connect with databases.
- for more database how to plan the things and see the connections strings for different type of databases.
- How to run loops in access tables
- How to use SQL Commands like select statement, update statement ,delete sql syntax and insert syntax to exchange the information back and forth.
- How to edit or change specific record in ms access creating user forms
Course Curriculum
Introduction to VBA Macro Recordings & Selections
-
What is VBA and why we need it – Do you need it?
14:28 -
Get familiar with VBA Editor important options – Our first step
20:57 -
Record, Edit and Run your first macro – Know from zero level
26:02 -
Sheet and Cells Commands with Debug of a program – Fundamentals
15:16 -
Recording Macro Fundamentals and Mistakes – Important thing to understand
11:25 -
How to go to last row and Data Selection right approach- Static and Dynamic
18:37 -
Project -Data Compile from sheets – its Super fun and practical lecture
32:23 -
Your question – What is personal MacroWorkbook?
17:18
Scope of a Variable
-
Declare Variables and Data Types and Errors you may face
41:56 -
Variant and String Data types with exercises – Fundamentals
28:26 -
Scope of a Variable with Call keyword – Local, module and Public .Global
36:21 -
Option Explicit and Interview preparation
13:17
VBA Loops with IFs
-
For Next Loop – First step
18:24 -
For next with more offset and more variations – Super Fun
09:42 -
Print numbers using Cells command and learn why it is important to know
04:20 -
Exercise for you – Make loop diagonally – Print numbers
06:10 -
How to Reverse a Loop – Become a Master now in Loops forever
06:03 -
Mastery over Sub Loops – Loop inside Loop -Achieve Magnificent level
15:37 -
IF statements in VBA – Full in-depth discussion is on
28:06 -
Quiz for you – Use IF with Loops on Real Data
24:36 -
Do While Loop begins
46:56 -
What is IF with ElseIf & IF without Elseif – Meaning & Difference – Just awesome
19:34 -
Select with End Select – What is it and benefits
05:51
Collection Loops and define Object/Classes
-
Begin with Workbook Commands
41:34 -
ThisWorkbook and ActiveWorkbook difference – Interview question
39:35 -
Know what is a Class and its methods and properties- fundamentals
51:39 -
Define own Objects using Set Keyword – A new level for you
47:04 -
How to use Worksheet , Range and Workbook Classes – TIme to do practical
35:27 -
Collection Loops begins – Work with Sheets
16:52 -
PROJECT – import all sheets names using Collection Loop
15:02 -
Super Advance Project – Data Compile – It cannot be better than this
18:47 -
Our second Collection Loop – Deal with Workbooks now
17:16 -
More Advance Project – Data Compile with more conditions
01:09:20
Arrays
-
How to Define and Fill Arrays – Why we need Arrays
23:02 -
Dynamic Arrays
19:06 -
Preserve function in Arrays – Take your skills to a new level
41:48 -
How to use Two or Multi Dimensional Arrays
01:08:05 -
Project for you- More confidence and more clarity
26:16 -
Challenge Lookup made simple with Arrays- Super Advance
59:57
Functions
-
Begin with UDF- User Defined Function
26:50 -
More examples of UDF
01:14:24 -
How to use SPLIT Function- Powerful
26:46 -
Inbuilt TEXT functions – len, ucase,lcase,strReverse
21:24 -
Text Inbuilt functions Left Right Find Mid
10:32 -
Vlookup Project
13:33 -
Challenge – VLOOKKUP and MATCH functions in VBA
18:58 -
Date functions in VBA
11:01 -
UDF Advance Challenge Vlookup – Find any instance of Lookup
18:28
Files and Folders
-
Introduction to Library called Scripting
32:46 -
How to Import file information in excel from any folder
10:34 -
How to combine data from different workbooks – Amazing, impactful
18:17 -
How to copy or move folders and files
10:09 -
Create a folder of your desire
19:16 -
What are file dialogs and Role
23:20 -
Use Vlookup – FileDialogs are so powerful
20:27 -
Open file dialog introduction – Use to open excel files
05:00 -
SaveAs file dialog now
04:22 -
Get subfolder names from any folder
32:18 -
Let us start with text files – take your levels to new heights
01:53 -
Open and Read text files data
08:48 -
Import text files in excel
08:42 -
Write data in text files – be a All Rounder
21:20
UserForms
-
What are the UserForms – Create and understand them
18:04 -
Know combo box, option buttons,frames, text box etc
23:45 -
Userform Events – Why you need them and how to use these events
47:17 -
How to do coding – Insert your data in excel from form controls
24:11 -
Learn Change event, after update, Queryclose and more – Super duper fun
26:11 -
Our ListBox in forms- its significant and its huge learning
15:41 -
How to get what is selected in a List Box
07:03 -
Show files in a Listbox if a folder changes in combo box – Another level
27:07 -
Project for you – More live case study means more confidence
20:31 -
How to move up and down the listbox items – Super knowledge project
32:37 -
Advance Level Dashboard using USER FORM – Challenge your limits
01:03:14 -
Finishing the Dashboard – Final Touch
44:34
Error Handlers
-
Error Handlers – Why we need them – Deep Dive
43:52 -
Project for you
18:25 -
How to modify text file data – Student question
16:58 -
Message box – intro
17:01 -
INPUT BOX Introduction
06:57 -
Application inputbox – set rules on data type
12:51
Events – Self Triggered Macros
-
Introduction to EVENTS- Let us know them
12:20 -
Sheet Activate and Deactivate events
36:51 -
Sheet Change and Selection Change Events
38:31 -
Project – how to track user activities on spreadsheet
52:31 -
Your question my solution
36:20 -
How to handle more than one cell – Copy table and paste issues solved – Awesome
01:07:45 -
Data validation issue resolved
08:34
VBA Charts
-
Introduction to Charts – How to Create
21:36 -
Errors Solutions in VBA – Charts are easy if you understand fundamentals
06:34 -
How to Create multiple Charts
17:25 -
Alignment in Charts – Resizing and dimensioning
14:30 -
Collection Loop role in Charts
15:35
VBA Pivots
-
Create Pivots in VBA – Begin the show
22:44 -
How to add Pivot Fields
13:24 -
Collection Loops in Pivot Fields
08:26 -
How to retrieve fields
03:47 -
Control Pivot fields from excel cells
28:52 -
Project for you – Gain more confidence
05:33 -
Work with Pivots across Worksheets
15:11 -
Loop Slicers in Pivots
23:41 -
How to control Slicers and then Pivots – SuperPowerful
24:58
Connectivity with Outlook
-
Outlook Connectivity – know outlook Classes
01:25:58 -
Import emails and how to send emails
42:55 -
Move Emails and Folders in Outlook
33:05 -
How to set up the date ranges and pick emails having specific subject line
26:56 -
Send different attachments with different subject-lines to different recipients
20:33 -
How to track replies of emails and move them in a new folder
24:18 -
Paste data as snapshot
14:42 -
Sending multiple snapshots in single email.
33:39 -
Excel report numbers on email body
23:31 -
Early and Late Binding
57:43 -
Send Specific data range to specific managers
20:06
Connectivity with PowerPoint application
-
Connecting powerpoint application with VBA
01:12:43 -
Export Charts in PPT
24:47 -
Paste Charts in existing slides
24:11 -
Collection Loops in Powerpoint
52:32 -
Project for you
59:22 -
Proejct2
32:25 -
Project3
01:01:23 -
Your question is Solved – Insert a slide after every five slides into existing PowerPoint
21:19
Database Connectivity
-
Connecting Database
01:10:39 -
Connecting database2
38:03 -
Update records in database
01:12:16 -
Delete and Modify database
56:54 -
insert data in database
32:23 -
Form break Timer- project
44:56 -
Make break timer Part2
01:04:41