Excel Vba Programming By Examples

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

Last updated 2022-01-10 | 4.5

- 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

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

Course content

4 sections • 35 lectures

Course Introduction and automation without coding 3 lectures

What to expect in this Excel VBA programming by example? Preview 03:57

Understand what you are going to achieve through the VBA by seeing demo and then seeing step by step explanation of VBA code.Please note all excel files along with VBA code is available for download.

Automate without coding - recording macro with relative reference Preview 09:49

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

FAQ - for recording macro Preview 00:58

Understand VBA development environment and VBA syntax 4 lectures

Understand Excel VBA integrated development environment Preview 12:00

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.

Understand Excel VBA syntax Preview 15:42

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 break point
How to run macro through click of a button

Understand Excel VBA syntax continued .... Preview 16:54

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

Understand conditional execution (if else conditions) Preview 11:30

If else command, If elseif else command
Using mod function (for remainder)
Showing status bar

Excel VBA workout examples 24 lectures

Workout Example 1- PartA- Introduction to Excel VBA forms Preview 05:32

See a demo of Excel VBA form. It helps you learn about
- various control, design aspects of Excel VBA form
- 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
- How to define level, text, combobox and button command
- How to pass dropdown data in combobox
- How to use form entry into VBA
- How to write back on Excel form
- How to load form while getting excel started
- How to change properties of control through VBA
- How to close excel when form is closed

Workout Example 1 - Part B 01- Design your first Excel VBA form Preview 10:15

Know about various controls available for form
Formatting and alignment of controls
Providing list for combobox
Ensuring data selection through combobox only
setting action for button
image control

Workout Example 1 - Part B 02- Design your first Excel VBA form Preview 09:28

Know about various controls available for form
Formatting and alignment of controls
Providing list for combobox
Ensuring data selection through combobox only
setting action for button
image control

Workout Example 1 - Part C - Explanation of demo Excel VBA form & code Preview 09:08

Learn
1. How accuracy of user entry was checked before execution of program
2. How form entry was taken inside VBA
3. How Control property was changed using VBA
4. How charts were updated using VBA
5. How charts were saved as gif using VBA
6. How images were loaded using VBA
7. How sheets are getting unhide and hide using VBA
8. How screen update has been put as false, so that end user don't see much changes happening

Workout Example 2 - Fetching data from MS Access database using Excel Preview 11:17

- 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 Example 3 - Part A - One sheet per agent or product Preview 12:03

- 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 Example 3 - Part B - One sheet per agent or product Preview 11:24

Workout Example 4 - Part A - Coupon Assignment and Inventory management Preview 10:22

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 Example 4 - Part B - Coupon Assignment and Inventory management Preview 09:02

Workout Example 5 - Reading microsoft access data and writing into a text file Preview 05:51

This is specially useful, when the output is more than 1 million
Here you are passing the criteria from excel and fetching the record
Then you are directly writing the output into a text file directly means without bringing data into Excel at all
Please note this is an extension of workout example 2
Please use MS Access data base available in workout 2 for this workout as well

Workout Example 6 - Part A- Design survey form in excel VBA (deal with list etc. Preview 09:53

Listbox

How you can create list box, so that user can comfortably select some choice available
How to ensure that users do not abuse the system - for example ensure that user selects at least one choice and at max three choice before submission
How to read user's selection

Options within a frame

How to create options so that only one of the choice can be selected
How to ensure that user don't proceed without making at least one selection

Message box

How to create new line in message box text
How to provide a title to a prompt to help user understand what is the purpose
How to take user entry and make progress accordingly

Workout Example 6 - Part B- Design survey form in excel VBA (deal with list etc. Preview 07:29