Follow on Google News News By Tag Industry News News By Location Country(s) Industry News
Follow on Google News | Advance Excel+VBA programming TrainingM&G Technologies is a consultancy firm in the field of SAS, SAP, VBA anaylist.
By: vinit 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/ 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: 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
|
| |||||||||||||||||||||||||||||||||||||||||||||||||
