+91-8750676667, +91-9871076667

Advanced VBA Macros Training in Gurgaon

  • Home -
  • Courses -
  • Advanced VBA Macros Training in Gurgaon
Advanced VBA Macros Training
    • Introduction to the Data and Data Formats.
    • Protection of Cells, Rows, Columns and Sheets.
    • Password protection to the Worksheet, Sheets, Rows, Columns, and Cells.
    • Text Functions: Concatenate, Left, Mid, Right, Lower, Upper, Proper, Replace, Rept, Find, Search, Substitute.
    • Logical Functions: If, If with OR, If with AND, If with AND &OR, If with OR&AND,
    • Nested If (For Multiple Conditions.
    • Introduction to Name Manager.
    • Math & Trig Functions: Sumif, Sumifs, Subtotal.
    • Statistical Functions: AverageA, Averageif, Averageifs,CountA, Countblack, Countif, Countifs
    • Lookup Functions: Discussion on Lookup Functions, Use of Lookup, Vlookup, Hlookup, Index, Indirect Match, Offset. Choose.
    • Use of Data Validation.
    • Use of Data Validation as a magical tool.
    • Introduction to the Interactive Charts & Dashboard.
    • Adding Switches to Dashboard.
    • Use of Multiple formulas for Dashboard.
    • Pivot Table.
    • Pivot Chart & Slicers.
    • What Is VBA? Need and Application of VBA
    • Introduction to Developer tab
    • Introduction to Macro Recordings: Using the Excel Macro Recorder, Macro Security, Recording Macro, Naming Macro, Executing Macro, Saving and Editing Macro.
    • Working In the Visual Basic Editor: Project Explorer, Properties window, Object Browser, Standard Module and Sheet Module.
    • Debugging mode, Breakpoints, Bookmarks, Watch window, immediate window and Locals window, Inbuilt VBE Help feature
    • Introducing the Excel Object Model: Application, Workbooks, Worksheet Objects
    • Variable, Constant and Data types: Variable Naming rules, Declaring, Initializing Variables, Option Explicit, Object Variables
    • Simple Dialog Boxes: Message boxes and Input boxes
    • Scope and lifetime of variables
    • VBA Sub and Function Procedures
    • Using VBA and Worksheet Functions
    • Working with Range Objects: Properties and Methods of Ranges – Range referencing, selection, activecell, cells and offset properties, resize ranges, value, formula and text properties, clear, delete, copy, paste and format ranges, Filters, special cells method, union and intersect ranges, current region property, working with dynamic ranges, last cell, last row and last column
    • Workbooks and Worksheets: The Workbooks Collection, Getting a Filename from a Path, Files in the Same Directory, Overwriting an Existing Workbook, Saving Changes, The Sheets Collection, Worksheets, Copy and Move
    • Using Conditional constructs& Loops: If-Then-Else, Select-Case, And/Or conditions, Using Looping constructs: For-Next, For-Each, Do-While, Do-Until Decision-making and Code Branching, Using Label Constructs
    • Automatic Procedures and Events
    • Types of Errors: Design Errors, Compile Errors, Runtime Errors. Logical Errors.
    • How to Debug the Errors.
    • Discussion of Formula Writing Skills in VBA.
    • Difference between R1C1 and A1 style for writing formula in VBA.
    • Writing of Basic and Complex formulas in VBA.
    • Data Validation & Input restrictions Effective Coding
    • Testing and debugging your code
    • Error-Handling Techniques
    • Bug Extermination Techniques
    • Using On error Go to and On error Resume next
    • Introduction to arrays
    • Static Arrays, Dynamic Arrays
    • One dimensional, Two dimensional and multi dimensional arrays
    • Named Ranges: Naming Ranges, Using the Name Property of the Range Object, Working with Named Ranges, Determining which Names Overlap a Range
    • Sorting: Structuring the Data, Sorting a Range, Sorting a Table,
    • Filter: AutoFilter Object, Filter Object, Date Custom Filter, Adding Combo Boxes, Copying the Visible Rows, Advanced Filter
    • PivotTables and Pivot charts: Creating a PivotTable Report, PivotCaches, PivotTables Collection, PivotFields, CalculatedFields, PivotItems, Grouping, Visible Property, CalculatedItems, Updating Pivot Tables, Synchronizing multiple pivot tables, PivotCharts, External Data Sources
    • UserForm Basics
    • Using UserForm Controls
    • UserForm Techniques and Tricks
    • Form Control vs Active X control
    • Accessing Your Macros through the User Interface
    • User Defined Functions & Add ins
    • Charts: Chart Sheets-Adding a Chart Sheet Using VBA Code, Embedded Charts, Using the Macro Recorder, Adding an Embedded Chart Using VBA Code, Creating charts, Defining Chart Series with Arrays, Converting a Chart to Use Arrays, Changing chart type and series type, Adding secondary axis, Changing chart marker options, Determining the Ranges Used in a Chart, Creating Dynamic Charts, Chart Labels, Copying Charts as picture/chart/excel objects into other Microsoft application.
    • Creating Interactive Dashboards: Introduction to dashboard and interactive dashboards, Creating dashboard using Named ranges, Charts, Tables, Pivot Tables and user forms, Choosing the right chart – Bullet Chart, Thermo meter chart, Using Alerts in dashboards, Interactive Dashboard Examples.
    • MS Access.

    SQL Statements

    • Create DB
    • DDL ( Data Definition Language) – CREATE, ALTER, DROP statements
    • DML (Data Manipulation Language) – SELECT, UPDATE, INSERT statements
    • DCL (Data Control Language) – GRANT, REVOKE statements
    • TCL (Transaction Control Language) – COMMIT, ROLLBACK statements

    Data manipulation and extraction

    • Select statement– select, select distinct
    • Column and Table Aliases
    • Sorting Data – order by
    • Filtering Data – WHERE Clause
    • Grouping data – Using the GROUP BY Clause , Filtering Groups with HAVING
    • Aggregate Functions – count() ,sum() ,avg() ,min() , max() , first(), last()
    • Not null, unique, foreign key.

    Joins and Functions

    • Joins – Inner Joins ,Outer Joins , Left joins, Right joins, Cross Joins and Self Joins .
    • Functions – String Functions, Math Functions, Date Functions, Conversion Functions
    • Establishing the Connection, Late Binding, Early Binding.
    • Interacting with Outlook: Drafting and sending mails via Outlook, Adding the recipients’ mail address and subject lines, Adding ranges, charts, tables, text in the mail body, Adding attachments.
    • Data Access with ADO Connectivity with Access and SQL: An Overview of ADO, The Connection Object, The Recordset Object, The Command Object, Using ADO in Microsoft Excel Applications, Using ADO with Microsoft Access, Using ADO with Microsoft SQL Server, Using ADO with Non-Standard Data Sources.
Tags: Advance Excel, Advance Excel classes In Delhi, Advance Excel classes In Gurgaon, Advance Excel Institute In Delhi, Advance Excel Institute In Gurgaon, Advance Excel training In Delhi, Advance Excel training In Gurgaon, Advanced Excel and VBA macros institute in Delhi, Advanced Excel and VBA macros institute in Gurgaon, Advanced Excel Classes in Gurgaon, Advanced Excel Corporate Trainer, Advanced Excel Corporate training, Advanced excel training in delhi, Advanced excel training in gurgaon, Excel Classes In Gurgaon, Excel Course in Gurgaon, Excel Institute in Gurgaon, Excel Macros Corporate Training in Delhi, Excel Macros Corporate Training in Gurgaon, Excel Microsoft Certification in Gurgaon, Excel training In Delhi, Excel training In Gurgaon, Macros training In Delhi, Macros training In Gurgaon, MIS Training in Gurgaon, VBA Macros Classes in Delhi, VBA Macros Classes in Gurgaon, VBA Macros Institute in Delhi, VBA Macros Institute in Gurgaon, VBA Macros training In Delhi, VBA Macros training In Gurgaon
Power BI Free Demo

Quick Enquiry

If you have interested any of above course which we provide, So send us a mail we will reply you soon !!