VBA MACRO Training

Visual Basic For Applications (VBA) & Macro Traning

Microsoft Visual Basic for Applications (VBA, Macros) when used with Microsoft Excel can build powerful automated business tools quickly and minimum cost. If you sit in front of a computer screen all day creating reports in Microsoft Excel, you know the power of this software package. But if you aren't using macros yet, you don't know the half of it. Macros automate common and repetitive keystrokes that you use in Excel to create and edit spreadsheets. By reducing the number of keystrokes required to perform common commands, macros speed up your production and reduce the time you have to spend staring at an electronic spreadsheet each day. VBA, a general purpose programming language, that comes along as standard with the Excel or Office can be used to perform various business tasks like-

Automate Report Generation
Automate Chart creation
Creating Dashboards
Communicating with MS Access Databases
Analyzing Business Data
Reconciling Data
Timesavings
Create our Own Functions

Prerequisites for attending the programme:
1.Good Knowledge of all features of Excel such as Filters, Pivot Tables, Text to Columns etc.
2.Good knowledge of Fundamental and Formulae and Functions in Excel
3.Must have worked on applications using Excel for atleast 1 year
4.Good Analytical Skill and Numerical ability is an advantage
5.Should take up Excel Test
Class Syllabus Training Details

Session - 1 VBA Overview & VBA Basic What is VBA?

Quick Review of Macros
Introducing the Visual Basic Editor
Using the Visual Basic Editor
Uses of Record Macro
Understanding and creating Modules
How to create a Message Box
Write program to update and retrieve information using Input Box
Conditions (IF, AND, OR etc.)
Understanding and using Select Case statement
How do I define a Variables and Rules for defining a Variable Name and Type
Creating And Using Variables
Working with For …. Next procedure
Working with Do While …. Loop procedure
Calling Procedures from one procedure to another
How to Save and Protect Modules

Session - 2
Session - 3 Workbook Objects Create or Add Single and Multiple Workbooks

Workbooks Save and Save As with Password
Open Single and Multiple Workbooks
Close Specific and Multiple Workbooks
Get Count of Workbooks
To refer a particular Workbooks
Activate from one Workbook to another Workbook
Open Workbooks from Specific Path
Get Workbooks Name and Path
Hide and Unhide for Single and Multiple Workbooks
Calculate entire Workbook
Protect and Unprotect Worksheets

Session - 4 Worksheet Objects Insert a Single and Multiple Worksheets
Delete Specific and Multiple Worksheets
Get Count of Worksheets
Select a Specific and All Worksheets
Get All Worksheets Name
Hide and Unhide for Single and Multiple Worksheets
Rename for Single and Multiple Worksheets
Protect and Unprotect Worksheets
Sort and Move Worksheets
Calculate entire Worksheet

Session - 5 Cell Objects Insert single and multiple Row, Column and Cells
Delete single and multiple Row, Column and Cells
Get Range or Address of Cell and Selection
Navigate from one Cell to another Cell
Select specific Range, Cell, Rows and Column
Types of Selection and Offset method
All the Options in Paste Special
All the Options in Go To
How to update the Formulas
Cut, Copy and Paste Options

Session - 6 Excel Options Create New Pivot, Refersh and Other Pivot Opotions
All the Filter and Advance Filter Options
Data Sort Options
All Text to Columns Options
All Data Formatting Options
Fill, Clear, Find and Replace Options
Preparation of Chart and Dashboard

Session - 7 VBA Function What is Function?
User Defined Function
Create a Function using IF Condition and Select Case
Create an Advanced IF Conditional Fuctions
Create an Advanced Computation and Conditional Function

Session - 8 VBA Function Create a Vlookup Fucntions
Create a Sum Fuctions
Create Count Function
Count All
Count Only Numbers
Count Only Text
Count Only Blanks
Count All

Session - 9 Access Database & Retrive Data From Access Databsae Using VBA Excel Create a New Table and Database in MS Access
Load the Data into Access Database
Create a Query in Access Database
Create Single and Multiple Mapping Tables
Filter Single and Multiple criteria using Access Query
Create Query for Data Grouping
Create Query to Select required Columns
Retrive specific data using Logical Access Query
Create Access Query to Update, Change and Delete the Information in Access
Retrive information through Excel from Access Database using VBA

Session - 10 Form Controls and User Forms Create and Design an UserForm
How to Link the VBA Macro to UserForm
Overview of Form Controls
Create a Form Information Tables and Database

Session - 11 Files Automations through VBA Files Automations real time scenarios
VBA Sample Project Report preparation

Session - 12 Outlook / Questions and Clarifications Retrive the Information through Excel from Outlook using VBA
Write query to Retrive Mail From, To and CC address from Outlook
Write query to Retrive Mail Subject and Time from Outlook
Write query to retrive Mail Attachment from Outlook


Cource Fee: Rs.8000/-
Place - Nungambakkam
Contact:
9941246766
9789024023
www.excelsense.com

Please call and book your seats.