VBA

Microsoft Excel workbook is one of the most commonly used tools to summarize and present data - When combined with VBA, this tool can perform that work with ultimate efficiency. Preparing long and tedious reports at your job must be your common and routine work; All the companies wants to shorten that time if you are one of them, then you must consider undergoing the Visual Basic /VBA Training. Macro is an excellent Visual Basic / VBA training institute in Delhi, You can learn all about Advance excel and VBA Macros and start doing it yourself as an expert. Our VBA training includes lab assignment and projects for better understanding, which makes us best advance excel and VBA training institute in Delhi.

COURSE CONTENTS

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

  • Financial functions

Logical functions

  • VLOOKUP functions/ HLOOKUP/INDEX/ MATCH Aggregate functions

Pivot Tables

  • Creating and updating Manipulating the data layout g Calculated fields & Items PivotCharts

Macros

  • Recording macros
  • Run macros
  • Assigning macros to a button or toolbar
  • 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
Day 2
  • 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
Day 3
  • 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 Comm and, Message Boxes
  • Operators- Arithmatic + - / \ * ^, Mod Operator
  • Comparison operators > < =
  • Concatenantion Operators
  • Logical operators: And, Or, Not, Xor
  • Other: Is Operator, Like Operator
Day 4
  • 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 problems when debugging : Mouse down, key down, Got focus/Lost focus
  • Using Message Boxes in Debugging, avoiding bugs
  • Errors and the Error Function
  • Resume Statements
  • Implications of Error Trapping, Generating Own errors
  • Dialogs
  • Viewing Form, Displaying form 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
Day 5
  • 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
Day 6
  • Charts and Graphs
  • Working with series collection object
  • Exporting chart as picture file, Macro recording
Day 7
  • 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
Day 8
  • 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
Day 9
  • 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
Day 10
  • 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
Day 11
  • User Forms
Day 12
  • 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