Advanced Excel with VBA Programming & Macros

Introduction

Microsoft Excel is one of the greatest, most powerful, most important software applications of all time. It provides enormous capacity to do quantitative analysis,with hundreds of thousands of records to complex estimation tools with user-friendly front ends. Excel is everywhere you look in the business world. Our R&D team has designed a fully practical training module to cater the layman as per the current market requirement.

Course Name - Advanced Excel with VBA & Macros

Eligibility - Any Graduate /Under Graduate

Certificate - Provided by Laqshya after Assessment Test

Admission Helpline - 8976789830 | info@laqshya.in

Course Highlights

  • 100% Practical Training

  • Course delivery through experienced Faculties

  • Free Notes and Study Material

  • Fast track and Sunday Batches available on request

  • Placements to bright students


  • LEVEL 1 - Advanced MS-Excel with Macros Overview

    • Topic 1 -: Logical Function (If, And, OR), Conditional Sums using single & multiple criteria, Conditional Average using single & multiple criteria, The Trim Function

    • Topic 2 -: Flash Fill, Advance Number Formatting, Advance Conditional Formatting, Data Validation with Adv. Application

    • Topic 3 -: Sorting & Subtotal, Adv. Filter, Consolidate of Sheets Data, Goal Seek, Formula Auditing, Track Change, Scenario

    • Topic 4 -: Quick Analysis, Lookup Functions, Match & Index, Vlookup from Multiple Table, Dynamic Vlookup, SUMPRODUCT Function

    • Topic 5 -: Data Table, Data Form, Solver, Depreciation Functions

    • Topic 6 -: Financial Function(PMT, PPMT,IPMT, Rate, NPER, PV, FV, NPV, IRR, MIRR)

    • Topic 7 -: Create Charts in details with use of all Functions related to Charts, Insert,Edit & Delete Comment, Change Comment Box Shape

    • Topic 8 -: PowerPivot, Creating DashBoard, Power View, Power Map, Power Query

    • Topic 9 -: Common Error Messages in Excel, Advance uses of Macro, Array Functions, Apps for Office, Other Functions

    • Topic 10 -: Introduction to ASAP Utilities, Select cells with the smallest & largest number, Insert Multiple Sheets (uses the cell values as sheet names), Create an index page with links to all sheets (clickable), Print Multiple sheets at once Set print area on selected worksheets, Protect & Unprotect multiple sheets at once, Count and/or color duplicates in selection, Quick numbering of selected cells, Insert before and/or after current value, Merge column data (join cells) Insert multiple empty rows at once, Remove all empty columns & Rows, Put together rows or column from several sheets, Change Case, Delete all after a given number of Characters

    • Topic 11 -: Spell/Write out numbers or amounts, Some useful formulae of ASAP, Store files in One Drive, Introduction to Google Drive, Managing Files in Google Drive, Form Creation, Sending and receiving response, Mail Merge through Google Drive

    • Topic 12 -: Track Changes, Protect & Unprotect Sheet, Workbook, Hide Formula, Allow Users to Edit Ranges with password & without password

  • LEVEL 2 - Excel VBA Programming

    • Topic 1 -: The Fundamentals of Excel VBA, The Visual Basic Editor, Objects and Properties, Excel Objects - Workbook Included, Object Hierarchy, Application, Workbook, Worksheet, Range, Command Bars

    • Topic 2 -: Variables and Constants, Data types, Variable Levels, Declaring Variables, Constants, Loops, Types of Loops, Loop Pitfalls, Effective Decision Making, If/And/Or, Select Case, Dates and Times

    • Topic 3 -: Workbook and Worksheet Events, Debugging and Error Handling, Prevention, Good Habits, Runtime Errors, Excel Worksheet Functions in VBA, Specific Examples, Excels Built-In Features

    • Topic 4 -: User Defined Functions, Good vs Bad, Arguments, Calculation, Controls, ActiveX, Control Toolbox, Control Properties, Control Parents

    • Topic 5 -: UserForms Introduction, Filling UserForm Controls, Putting Data into our Controls, When to put Data into our Control, Filling a ListBox and/or ComboBox using Loops

    • Topic 6 -: Multi-Page Control, Creating a wizard using the Multi-Page Control, Validating the User Inputs, Some Useful Methods and Functions, When to apply Validation, Control Events, Which Controls to use & When

    • Topic 7 -: Parsing Control Values back to a Spreadsheet, Parsing range values to the UserForm Controls, Option Buttons and Checkboxes in Detail, True or False

    • Topic 8 -: Creating Template Controls, Using the RefEdit Control, Finding the Active Control, Creating Controls at Runtime