Description

Understand what you are going to achieve through the VBA by seeing demo and then see step by step explanation of VBA code. Learn about Excel VBA syntax, Excel VBA form and control, methods of  using forms and controls, several workout examples to see usage of Excel VBA for automation.

In this course, you will learn following stuff in step by step manner

Level 01 – start without coding – Automate tasks using Excel Macro recording  

   Demo of an excel macro  
   What is excel macro  
   When to use it  
   How to record a macro/create a shortcut action  
   How to run a recorded macro  
   How to create a shortcut for a macro action  
   How to run a recorded macro on a new dataset (excel workbook)  
   How to record a relative macro  
   What is the difference between a relative macro and a general macro.  
   How to understand what was recorded as macro.  
   How to delete a macro  

Level 02 A – Understand Excel VBA integrated development environment

   How to reach VBA window  
   What are different component of the window  
   What is use of those components  
   How to use breakpoint, properties window, edit tools etc.  

Level 02 B – Understand Excel VBA syntax  

   How to define a variable  
   Different types of variables  
   How to write a for loop  
   How to display output in an interactive way  
   How to write output in a different worksheet  
   How to take user input through a prompt  
   How to use user input  
   How to use record macro to know VBA syntax  
   How to use breakpoint  
   How to run macro through click of a button  
   When you need to write do while / do until loop  
   Syntax of do while / until loop  
   How to take input from excel sheet for program execution  
   How to ensure variable names are correct before execution of program  
   If else command, If elseif else command  
   Using mod function (for remainder)  
   Showing status bar  

Workout Examples 01 – Using Forms for user entry, chart display etc.  

   See a worked out example of a VBA form  
   Learn about various control, design aspects of Excel VBA form  
   Learn about why will need form, and such controls  
   Hide Data sheet and format other sheet to make it look professional  
   Ensure proper data type  
   Ensure value selection from combo box only  
   Learn to define level, text, combobox and button command  
   Learn to pass dropdown data in combobox  
   Learn to use form entry into VBA  
   Learn to write back on Excel form  
   Learn how to load form while getting excel started  
   Learn to change properties of control through VBA  

Workout Examples 02 – fetching data from MS Access using Excel VBA  

   How to use Excel as front end and fetch data from microsoft Access database  
   Where to use this à Greatly useful when many users have Excel but don’t have MS access database in the PC  
   Where Reference is needed  
   Watch window – how to use it  
   How to edit the code for many fields and different databases  

Workout Examples 03 – One sheet per product or agent  

   How to use do while loop to let it run for as many records as it has got  
   How to find block size (starting and ending row for each product)  
   How to add sheet using VBA and give it a name  
   How to ensure that the tool remains intact with multiple runs and even a mistake can’t cause issue  
   How to repeat header in each tab or worksheet  

Workout Examples 04 – Inventory management, coupon assignment and customer communication using Excel VBA  

   Traverse through various sheets and workbooks using VBA            
   Formatting date                
   Writing derived information from one sheet to another  
   Passing several parameters to VBA for conditional traversal          
       3 should mean three coupons to get reserved  
       [Coupon code : Validity] will need comma if there are multiple vouchers  
   Error handling : alert, if there are no coupons      
   Protecting Excel tool for further usage    

Workout Examples 05 – Reading data from a microsoft access database and writing it into a text file

   Reading Microsoft Access data using VBA
   directly writing output into a text file
   Making the output comma separated

Workout Example 06 – Designing survey form in Excel VBA with option buttons / list box etc.

Workout Example 07 – Insert Excel VBA form data in MS Access database

Workout Example 08 – Using pivot table, vlookup and several other formula for a sampling work

Workout Example 09 – Windows based user authentication and Voice notification of execution of VBA

Workout Example 10 – Running SQL command through VBA. Read Excel sheet as a table inside VBA

You will learn

   Reading complete worksheet data into a table
   Managing named as well as unnamed headers
   Running SQL command inside VBA
   Little advance SQL coding as well (by product)
       A-B
       B-A

Workout Example 11 – Voice based reminder tool

You will learn how to remind speaker about how much time is left through automated announcement.

Workout Example 12 – Running VBA code in automated manner

You will learn what is takes to used Windows scheduler to schedule the VBA code to run in automated manner with desired frequency.

Workout Example 13 – Recursive file and folder size within a folder

Many a times we need a tool, which should recursively put file names and file sizes within a folder.

I mean to say that, if you pass name of a folder and say in that folder, it has

   11 files
   2 folders
   Then it should again go into the 2 folders and if it finds a folder again say in any of the folder, it should go within that folder too and should create result, with name and size of all the files & folders.

Workout Example 14 – Combine all the files inside a folder through VBA

Tasks

1. Combine Multiple files – having same layout (same number of fields and fields are always in the same column) but not neessarily same number of rows

2. Also put file name as first column in the final data set

Assumption:

   All the data files are in the same folder
   There is nothing other than data files in that folder
   Usually data files should have a way to know, which file was referred
   All the data files are excel files only

Who this course is for:

   Microsoft Excel users who wants to learn automation and VBA programming
   Someone who wants to learn by seeing workout examples

Requirements

   Basic Excel 2016
   Understanding of basic excel functions and features (pivot table, sort, filter, vlookup) etc

Last Updated 11/2020