Excel VBA programming by Examples (MS Excel 2016)

Understand what you’re going to obtain by the VBA by seeing demo after which see step by step rationalization of VBA code. Learn about Excel VBA syntax, Excel VBA kind and management, strategies of  utilizing varieties and controls, a number of exercise examples to see utilization of Excel VBA for automation.

In this course, you’ll study following stuff in step by step method

Level 01 – begin with out coding – Automate duties utilizing Excel Macro recording
 

• Demo of an excel macro
   

• What is excel macro
   

• When to make use of it
   

• How to report a macro/create a shortcut motion
   

• How to run a recorded macro
   

• How to create a shortcut for a macro motion
   

• How to run a recorded macro on a brand new dataset (excel workbook)
   

• How to report a relative macro
   

• What is the distinction between a relative macro and a basic macro.
   

• How to grasp what was recorded as macro.
   

• How to delete a macro
   

Level 02 A – Understand Excel VBA built-in growth surroundings

• How to succeed in VBA window
   

• What are totally different element of the window
   

• What is use of these elements
   

• How to make use of breakpoint, properties window, edit instruments and many others.
   

Level 02 B – Understand Excel VBA syntax
 

• How to outline a variable
   

• Different sorts of variables
   

• How to jot down a for loop
   

• How to show output in an interactive manner
   

• How to jot down output in a special worksheet
   

• How to take person enter by a immediate
   

• How to make use of person enter
   

• How to make use of report macro to know VBA syntax
   

• How to make use of breakpoint
   

• How to run macro by click on of a button
   

• When you must write do whereas / do till loop
   

• Syntax of do whereas / till loop
   

• How to take enter from excel sheet for program execution
   

• How to make sure variable names are appropriate earlier than execution of program
   

• If else command, If elseif else command
   

• Using mod perform (for the rest)
   

• Showing standing bar
   

Workout Examples 01 – Using Forms for person entry, chart show and many others.
 

• See a labored out instance of a VBA kind
   

• Learn about varied management, design elements of Excel VBA kind
   

• Learn about why will want kind, and such controls
   

• Hide Data sheet and format different sheet to make it look skilled
   

• Ensure correct knowledge sort
   

• Ensure worth choice from combo field solely
   

• Learn to outline stage, textual content, combobox and button command
   

• Learn to go dropdown knowledge in combobox
   

• Learn to make use of kind entry into VBA
   

• Learn to jot down again on Excel kind
   

• Learn load kind whereas getting excel began
   

• Learn to vary properties of management by VBA
   

Workout Examples 02 – fetching knowledge from MS Access utilizing Excel VBA
 

• How to make use of Excel as entrance finish and fetch knowledge from microsoft Access database
   

• Where to make use of this à Greatly helpful when many customers have Excel however haven’t got MS entry database within the PC
   

• Where Reference is required
   

• Watch window – use it
   

• How to edit the code for a lot of fields and totally different databases
   

Workout Examples 03 – One sheet per product or agent
 

• How to make use of do whereas loop to let it run for as many data as it’s got
   

• How to search out block dimension (beginning and ending row for every product)
   

• How so as to add sheet utilizing VBA and provides it a reputation
   

• How to make sure that the software stays intact with a number of runs and even a mistake cannot trigger subject
   

• How to repeat header in every tab or worksheet
   

Workout Examples 04 – Inventory administration, coupon project and buyer communication utilizing Excel VBA
 

• Traverse by varied sheets and workbooks utilizing VBA          
   

• Formatting date              
   

• Writing derived info from one sheet to a different 
   

• Passing a number of parameters to VBA for conditional traversal         
   

  • 3 ought to imply three coupons to get reserved
     

  • [Coupon code : Validity] will want comma if there are a number of vouchers
     

• Error dealing with : alert, if there are not any coupons     
   

• Protecting Excel software for additional utilization   
   

Workout Examples 05 – Reading knowledge from a microsoft entry database and writing it right into a textual content file

• Reading Microsoft Access knowledge utilizing VBA

• immediately writing output right into a textual content file

• Making the output comma separated 

Workout Example 06 – Designing survey kind in Excel VBA with possibility buttons / listing field and many others.

Workout Example 07 – Insert Excel VBA kind knowledge in MS Access database 

Workout Example 08 – Using pivot desk, vlookup and several other different method for a sampling work 

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

Workout Example 10 – Running SQL command by VBA. Read Excel sheet as a desk inside VBA

You will study

• Reading full worksheet knowledge right into a desk

• Managing named in addition to unnamed headers

• Running SQL command inside VBA

• Little advance SQL coding as properly (by product)

  • A-B

  • B-A

Workout Example 11 – Voice primarily based reminder software

You will learn to remind speaker about how a lot time is left by automated announcement.

Workout Example 12 – Running VBA code in automated method

You will study what’s takes to used Windows scheduler to schedule the VBA code to run in automated method with desired frequency.

Workout Example 13 – Recursive file and folder dimension inside a folder

Many a instances we’d like a software, which ought to recursively put file names and file sizes inside a folder.

I imply to say that, when you go title of a folder and say in that folder, it has

1. 11 information

2. 2 folders

   Then it ought to once more go into the two folders and if it finds a folder once more say in any of the folder, it ought to go inside that folder too and will create outcome, with title and dimension of all of the information & folders.

Workout Example 14 – Combine all of the information inside a folder by VBA

Tasks

1. Combine Multiple information – having identical format (identical variety of fields and fields are at all times in the identical column) however not neessarily identical variety of rows

2. Also put file title as first column within the remaining knowledge set

Assumption:

1. All the information information are in the identical folder

2. There is nothing apart from knowledge information in that folder

3. Usually knowledge information ought to have a technique to know, which file was referred

4. All the information information are excel information solely

Who this course is for:

• Microsoft Excel customers who desires to study automation and VBA programming
• Someone who desires to study by seeing exercise examples