Excel VBA programming by Examples (MS Excel 2016)

Recording Macro, Creating Excel VBA form, Fetching data from MS Access, Working with multiple sheets and workbook

What you'll learn
Learn to automate their time consuming repeatitive tasks for more accuracy and less time consuming
Understand VBA syntax for Excel
Understand usage of tools available in VBA environment
See several worked out examples
Learn to fetch access data using Excel as a front end
Learn to develop VBA froms and interact with the same, load charts on forms
See inventory management workout
And many other workout which i am adding step by step

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

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

More Info