Advance Excel+VBA programming Training

M&G Technologies is a consultancy firm in the field of SAS, SAP, VBA anaylist.
By: vinit
 
 
Spread the Word
Listed Under

Tag:
* Excel VBA programming

Industrys:
* IT Trainings
* Education

Location:
* Delhi - Delhi - India

March 11, 2012 - PRLog -- M&G Technologies a premier company engaged in quality IT services in the areas of Training, Consultancy Recruitment.

OPPORTUNITY FOR Candidates FROM NON IT BACKGROUND LOOKING TOWARDS MAKING A CAREER IN IT SECTORS

Advance Excel Training with VBA Programming

Advance Excel
This module contains the topics as follows:-
• Advanced formatting techniques
• Conditional formatting
•   Cell References (Understanding of Absolute & Relative Cell references)
• Working with multiple worksheets in a workbook
• Data linking between worksheets and workbooks
• Importing and exporting data (text files, other spreadsheets, etc.)
• Retrieve data from a database (intro to MS Query)
• Advanced chart formatting (font formats, text orientation, changing data series order, etc.)
• Using Comments
•   Solver
•   Scenario Manager
•   What if Analysis
•   Goal Seek
•   Developer Tab
• Techniques to protect the workbook and worksheet
• Advanced filtering techniques

Advanced Excel Curriculum
• Using Data Validation tool
• Creating workbooks using templates and creating your own templates
• Auditing worksheets and tracing cell references
• Correcting errors using tracing tools
• Combining worksheets and data from different workbooks



• Using advanced functions
    o Financial functions
    o Logical functions
    o VLOOKUP functions/ HLOOKUP/INDEX/ MATCH
    o Aggregate functions
• PivotTables
    o Creating and updating
    o Manipulating the data layout
    o Calculated fields & Items
    o PivotCharts
• Macros
    o Recording macros
    o Run macros
    o Assigning macros to a button or toolbar
    o Edit macros with VBA
Excel VBA
Day 1
Exploring the Visual Basic editor in Excel
VBA Project Explorer and code windows
Our First Excel VBA Macro
VBA Project window
Saving off Macro
Variables, Arrays, Constant, and Data Types
Implicit & Explicit Declaration
Scope & Life time variables
Static, Local, Module level, Global Variables
Name Conflicts and shadowing
Data Types- Variant, Date/Time Values stored in variants, Empty Value, Null value & Others
VBA Data Types – Numeric & String
Arrays – Multidimensional & Dynamic
User defined types, constants, reserved words
Modules
Difference b/w subroutines & functions
Simple subroutine, simple functions
Public & private functions & subroutines
Argument Data Types, Optional Aguments, Passing Arguments by value


Day2
Programming Basics: Decision and Looping
Decisions
Multiple conditional statements
Select case Statements
Looping
For Next loops
For Each Loop
Do Until Loops
While wend loops
Early exit


Day3
Strings and Function and Massage  Boxes
Strings Concatination
Splitting strings
Changing the appearance of strings
Searching strings
Functions – Len, Abs, Int, Sqr, Asc, Chr
Conversion Functions- CStr, Clnt, Clng, CDbl, Val
Format functions
Date & Time Functions
Now, Date, Time, Date add, Date Diff, DatePart, Date Serial, Date Value, Day, Hour, Month, Second, Minute, Year, Weekday
Send keys Command,Message Boxes
Operators- Arithmatic    +   -   /   \   *  ^, Mod Operator
Comparison operators    >  <  =
Concatenantion Operators
Logical operators:  And, Or, Not, Xor
Other:   Is Operator, Like Operator

Day4:   Debugging
Types Of Errors:  Compile, Runtime, Logical
Design Time, Runtime, Break Mode, Break Points
Stop statements
Run Selected part of code
Single stepping, Procedure stepping, call stack dialogs
Debug window
Events can cause problemswhen debugging   :  Mouse down, key down, Got focus/Lost focus
Using Message Boxesin Debugging, avoiding bugs
Errors and  the Error Function
Resume Statements
Implications of Error Trapping, Generating  Own errors
Dialogs
Viewing Form, Displayingform in code
Populating form, Default Toolbox Controls
Label, Text Box, Combo Box, List Box, Check Box, Option Button, , Toggle Button, Frame
Command Button, Tab Strip, Multipage, Sroll Bar, Spin Button, Image, RefEdit
Common Dialog Control  :  Open Dailog, Save As, Color, Font, Pring, Default Dailog
Command Bars and Buttons

Day5
The Excel Object Model
Properties & Methods, Manipulating Properties, Calling Methods
Communicating with spreadsheet, Hierarchy, Recording Modes
Application Object – Main Properties, Methods & Collections
Workbook Object , Windows Objects, Worksheet Object, Range Object
Driving Microsoft Outlook & other Office Programs


Day6
Charts and Graphs
Working with series collection object
Exporting chart as picture file, Macro recording

Day7
API Call, Use of API Call
Getting Disk Space, Reading from n writing INI Files
Read Keyboard activity
Play Multimedia sounds  
Class Modules   :  Inserting a class module
Creating an object, collection
Using PNames Collection
Pivot Tables
Creating simple pivot table from the front end of excel
Creating pivot table using VBA
Using VBA with pivot tables
Advance VBA for Pivot Table


Day8
VBA in Action
Converting Labels to Numbers and Numbers to Labels
Transposing a Range of Cells
Adding Formula Details into contents
Calculating a Range
Reversing  a  Label
Evaluating a Cell


Day9
Sorting Worksheets into Alphabetical Order
Replacing Character in a String
Timed Event
Auto – Totaling a Matrix of Numbers
Absolute and Relative Formulas
Coloring Alternate Rows and Columns of the Spreadsheet
Coloring Cells Containing Formulas
Summing Cells by Reference to a Master Cell

Day10
Globally  Changing a Range of Values
Displaying Hidden Sheets without  a  Password
Searching  Multiple Sheets and Workbooks
Brighten Up Your Comments
Importing a CSV File Containing Leading Zeros

Day11
User Forms

Day12
Working Data base
ODBC Links
Using ADO

Day 13 & 14
SQL
Create DB, Table space, Table Insert, Select, Update, Distinct, Where,
And & Or, Order BY, Delete
SQl Top, Like, wildcards, In, Between, Alias, Joins, Inner, Left Join, Right Join, Union
Not Null. Unique, foreign key, Create Index, Drop, Alter, Views
Sql Functions sql avg(), Count(), first(), last(), max(), min(), sum(), Group by, having



Day 15
MS Access

Day 16
Doubt Session

For Detail Curricullam visit: http://www.morgans.in
Eligibility: Any Graduate
We offer this course. Only through Industry Professionals In-house Exam Facility We provide 100% placement assistance to all our students. Our placements have been best in the industry.

Center at: Pitampura

WANNA KNOW MORE ABOUT IT??
For More Enquiries (WE are 7 Days Open)
With Regards,
M&G Technologies
FD-6, 1st Floor, Pitampura
Contact 011-45201009, 9312420009
End
Email:***@morgans.in Email Verified
Phone:9312420009
Zip:110034
Tags:Excel VBA programming
Industry:IT Trainings, Education
Location:Delhi - Delhi - India
Account Email Address Verified     Disclaimer     Report Abuse



Like PRLog?
9K2K1K
Click to Share