Excel: Learn VBA (Macros) & Increase Your Productivity!

Snapshot of what you will learn: Trailer of What can be done

This is a free video published as a trailer to the course to help our students know how is the full movie going to be. basically the video contains a few creative stuff as examples and the course revolves around the concepts which will help you build similar creations yourself.

This video is again a sample data from a project that we solved for automation and displays how a complex data is automatically structured with the help of a Robot (Macro).

Creating Controllable Dashboards to manage reports: Dynamic Charts Intro

This Video contains the concept which clarifies the term "Dynamic Chart" for you and makes the target look simpler and doable.

Download the data and start this video to be able to work with me.

00:40 Types of Charts

03:00 Creating a template for the Dynamic Chart

05:35 Clarifying the logic by doing it manually

Creating Controllable Dashboards to manage reports: Looking up to the Data

This video is a continuation of the previous video, so you can continue with the same data here as well.

we are talking about fetching data information based on a common field from the source data using Vlookup function.

04:00 Lookup Value = What to look for?

06:25 Table Array = Where to look for?

10:50 Column Index Number = From which column you need the data corresponding to the found value in the first column

12:00 Lookup range = Exactly find it or approximately find it

Creating Controllable Dashboards to manage reports: Using smart Functions

This Video is again a continuation of the previous video and you continue with the same Telecom Data.

We are majorly discussing about how do you make your functions more dynamic/Smart here so that it starts acting on some changes by itself without spoon feeding it every time. its an art of delegating your work to the tool.

00:45 Decision Matrix

02:35 Match Functions : To provide you the location of the heading by itself

Creating Controllable Dashboards to manage reports: Drop Downs

This videos we are discussing about controlling the chart through the Data Validation Drop downs. Data Validation can be used but are not experts in generating controls. so this video also talks about the drawbacks of the same and how to overcome these drawbacks. the next video will showcase the best possible ways to control the reports.

Creating Controllable Dashboards to manage reports: Form Controls

This Video talks beyond data presentation, it talks about a very important component of a dashboard - Control.

Using which not only we can use our reports but it becomes ready for anybody to use it and change it easily with their requirements with the help of some user friendly controls.

01:20 Creating Controls to manage Charts.

02:45 Giving life to the control

09:00 connecting the control to your template Using Index Function

12:00 Presentation and Adding values to the chart

Visual Basic For Applications: Automating your Work: Introduction to VBA

This Video clarifies the concept of VBA and the agenda that we will follow.

05:20 Clarifying the difference between VBA and Macros

06:50 Personifying the concept to understand it easily.

12:00 Object oriented programming Language

Visual Basic For Applications: Automating your Work: Hierarchy

This video describes the structured way of communicating with excel objects like cells, sheets, rows, etc through VBA.

07:00 Hierarchy order for communication

12:00 Grammar to connect the order to build up a sentence

14:10 Using Grammar to connect the objects and create a sentence (Syntax)

Visual Basic For Applications: Automating your Work: Grammar in VBA

Did VBA come first or Excel?

01:30 Writing Cells through Logical Referencing of VBA

04:20 Writing Rows through logical referencing of VBA

06:20 Writing columns through logical referencing of VBA

All references done through VBA terminologies are logical and use direct terms mostly.

Visual Basic For Applications: Automating your Work: Intangible Hierarchy

This video explains the syntax's further and clarifies more objects of Excel.

	00:00 Difference between Excel and Workbook

	06:15 How to write functions in VBA using Intangible Hierarchy

	12:00 What are worksheetFunctions

	14:00 Len is a VBA Function (So no hierarchy)

Visual Basic For Applications: Automating your Work: Connecting Excel to VBA

This Video explains that all our work in Excel is summarized under 4 different type of objects and works over three connections among them.

03:40 Properties, methods and Events of an Object (Example of a cell Used as object)

04:10 Properties of an Object

04:40 Methods of Actions taken on a cell

06:00 Events - Point of Occurance of any action. the answer of any "When?" is an Event

13:00 Connections between different types of Objects to get an output

14:00 Applying Functions over objects of Excel (Through VBA)

Visual Basic For Applications: Automating your Work: Properties and Methods

This Video explains how do we connect Object with its Properties using Examples.

01:00 Example 1- Extracting column number Property from a cell

06:15 Example 2- Change the name of a sheet using VBA

09:35 Connecting Objects with Methods

10:40 Example 1- Delete a sheet from the workbook

Learn more with Hands on: Sub Routines

This video starts with an overview of the VBA window and you will type the first code here by following the video and the concepts gathered until now.

00:05: VBA screen is displayed and explained

02:00 Inserting a Module to write a macro

02:50 Understanding Routines

03:30 Explaining Sub Routines

05:35 Creating a Macro in a Sub Routine

08:05 Increasing the font size of the code window

08:40 creating Message box using VBA

10:00 Ways of Execution through VBA window (F5/ F8/ F9)

Learn more with Hands on: Ways of Executing a Macro

This video explains various ways of executing a macro from excel screen itself. without entering into the VBA window. this is a kind of a user interface given for the user.

00:50 Alt+F8 (Run) to see the list of all macros and execute the one that is needed

03:30 Alt+F8 (Options) to define a keyboard shortcut to execute the Macro

06:30 Button to execute a macro by clicking on the button

08:50 Shapes using different shapes and converting them into buttons to execute our macro.

Learn more with Hands on: Private Sub Routines

This video explains how we use the pre defined Macros that are already available in excel and is Privately owned by VBA. thus termed as Private Sub Routines.

03:00 Executing Macro on situations (Not Manually)

04:25 Looking for a relevant Private Macro to write our code

07:30 Typing our code in the Private Sub Routine

10:15 Saving the workbook as a Macro Enables workbook

12:30 Workbook Greets us on opening

13:00 Enable macros

Learn more with Hands on: Function Routines

This Video explains how can we create our own functions in Excel using VBA.

03:10 Creating a function routine

05:50 Understanding the work structure of the predefined formulas (SUM)

09:00 making the formula work to calculate a cube for example

Learn more with Hands on: Basic Error Debugging

Now since we have started writing macro, we need to be aware of the errors popping up in between. This video tells us how do we understand and Debug some of the Basic Errors:

00:00 Writing a comment in your code

00:20 Error 1 - When the line is visible in Red color Font

02:30 Error 2 - When the keyword does not gets converted to a Proper case automatically

Recording is a concept by which you can perform in Excel and let VBA immitate your work. We can then make use of it to repeat that task whenever we need. it also helps a ton in getting "Syntaxes" which we cant memorize.

04:20 Recording

Implementation on a case study: Filling the blank cells

We start with a step by step process of writing a standard macro. and we see the implementation with an example:

In This video we are taking the first question from a case study which can be downloaded form the material below:

Implementation on a case study: Making your code Dynamic

Best Practices:

To remove the constants from a code and replace it with variables so that the code becomes more smart and hence,

Acts smart.

Implementation on a case study: Loops

This video talks about loops and making any code dynamic in excel.

when we say dynamic, we mean to say self learning and a smart code which can itself identify a bit of changes and take decisions accordingly.

Implementation on a case study: Recording

This video is a continuation of the same case study that you downloaded in the above lecture. In this video we are discussing and solving the second question using "Record" option in Excel.

In the example we are deleting the entire rows for the missing unique field in the data.

Implementation on a case study: Trailing macros one behind other

We have now done two tasks one by one.

the time now to allocate the work such that we execute only once and both the tasks get executed in a specific sequence of requirement.

Implementation on a case study: Events to create self Executable Macros

In this video we will learn how can we create self executable macros so that the execution does not depends upon us, and the decision is taken smartly by the system itself.

Implementation on a case study: Framework of a User Defined Function

In this Video i am explaining the user defined functions and how are they created. to do that i have also gone into the logic s of the pre-defined functions and their working.

Implementation on a case study: Creating a User Defined Function to Extract numerics

Implementation on a case study: Creating a User Defined Function

This video demonstrated the creation of a function for extracting numeric values from between a string value.