Trainer:	Mohd. Arsalan
Venue:	AIM TECHNOLOGIES, AMEERPET, HYDERABAD
Contact:	arsalan701@gmail.com 91-9985130004
Duration:	3 Weeks- Starts on 19th October
Addons:	VBA Reference Notes + Excel Shortcuts+ Email support for couple of months.
Fee:	Rs 5000
Eligibility:	Intermediate Excel Users/ +1 year experience in excel reporting
Register:	Over Email

(Please take the XL TEST to assess yourself in Excel)

Objective of the workshop

To educate a participant on using the power of VBA programming language in Excel. An insructor led training with 1:1 man-pc ratio which will make you comfortable in writing vba macros.On successful completion of the workshop a user will be able to automate most of the stuff in excel like creating an excel model,consolidate sheets into one, consolidate workbooks into one, automate creating of charts and pivot tables, automate reporting by using functions like vlookup and match in VBA.

Many excel users fancy the terms "macro" or "writing a vba code". This workshop aims to give them a perfect platform to learn vba coding from Scratch. Before jumping to VBA I will explain the advance features of excel-2007 with some quick tips.

. Also an email support for couple of months for any projects you do at work.

About the Trainer:

I am a Financial Analyst (MBA) working with an MNC for the last 6 years. I have over 6 years of corporate training experience on Excel & VBA. Having given workshops in companies like Deloitte, Bank of America, Novartis, GMR.... and so on trained 500+ people so far. Also working as a Contractor to develop Excel Applications for small/medium sized firms.

This workshop is intended for:

Users with intermediate knowledge of Microsoft Excel, who wish to enhance their knowledge by writing macros.
For Analysts/Professionals who wish to have a competitive edge over their peers by developing automated tools and templates.
Employees in other operations who would like to get in a role of MIS reporting,Business Intelligence,Analytics, etc...

Workshop Contents:

Introducing VBA Editor

Customizing Toolbars
Adding/Removing Module
Code Window (Entering code manually vs Recording)
Properties Window
Immediate Window
Options in VBA Environment
Macro Recorder
- Relative or absolute reference recording.
- Rewriting code using "With" .
- Storing Macros (Same workbook vs personal.xls)
Objects and Collections
- Object Hierarchy
- Referring an application, workbook, sheets and range.
- Object properties
- Object Methods
- Collections Analogy exlained.
Working with Range/Cell Object:
- code to select range, rows, columns, block of cells.
- copy and paste ranges to different sheets /workbooks
- Determining last cell from bottom/right.

Offset Property
Example codes Macro to add numbers, find sum of first 100 numbers.....
Assignment Statements
Interacting with user for input.
Data Types in VBA.
Declaring range as an object.
Scoping a variable (public vs private)
Understanding Arrays.
With-End Constructs
For-Each Constructs
Conditional Statements

If-Then
If-else (Multiple If conditions)
Select Case
Do-while
For-Each
Combining For and IF conditions

PROJECT TO DISPLAY SHEET NAMES IN A RANGE
PROJECT TO DELETE BLANK ROWS
PROJECT TO FILTER DATA WITH A CRITERIA, CREATE A NEW WORKBOOK AND SHEET FOR EACH CRITERIA.

Executing a Macro:

From Macro Dialog Box
VBA Editor Directly
shortcut key
Button
Calling from other proceduress

Writing functions and using them in sheet.
Passing arguments to functions and creating statistical/financial formulas.
Using functions like vlookup, match, left, right in vba code.
Writing macro code to generate a pivot table.
Writing macro code to generate a chart.

VBA Events
- Workbook events (ex: running a macro when workbook opens/close)
- Sheet Events
- Cell Change Events (Run a macro when a value of cell changes)
Error Handling Techniques:
- On Error Resume next
- On Error go to (specific set of statements)
- Using Local/Watch Window to tap errors.
- Applying Breakpoints for testing code.
Userform
- Creating a userform to get data.
- Adding code in the background of each form.
- Collating data from form to the worksheet.
- Various controls of userforms.

* Interacting with Internet explorer. (Only If time Permits)
*Linking Ms-Excel to Access and retrieving data from tables-basic(only if time permits)
* Each feature will be explained by one/two examples.

VBA FOR EXCEL MACROS

Objective of the workshop

About the Trainer:

This workshop is intended for:

Workshop Contents:

Macro Recorder

Objects and Collections

Working with Range/Cell Object:

PROJECT TO DISPLAY SHEET NAMES IN A RANGE

PROJECT TO DELETE BLANK ROWS

PROJECT TO FILTER DATA WITH A CRITERIA, CREATE A NEW WORKBOOK AND SHEET FOR EACH CRITERIA.

VBA Events

Error Handling Techniques:

Userform