Advanced Excel & VBA

Course Duration : 50 hrs + Case Study
16,000

About Advance Excel & VBA

VBA is the acronym for Visual Basic for Applications. It is an integration of the Microsoft’s event-driven programming language Visual Basic with Microsoft Office applications – Microsoft Excel. By running VBA within the Microsoft Office applications, you can build customized solutions and programs to enhance the capabilities of those applications.


Course Overview

Here we will be learning to Work with:

  • VBA objects, properties, and methods
  • Run a sub procedure
  • Create a macro using the macro recorder
  • Create UserForm to Input Data (with proper validations).
  • Input/Output of Data with other Applications.

What we offer

Hands on experience with Visual Basic with Microsoft Excel.
Real time industry problems and solutions (Sample Projects)
Industry Experienced Faculties
Exclusive Study Material
State-of-Art Labs with latest Infrastructure

Chapter-1: Introduction to MS Excel & Understanding Basic Work with it

1.1 Features of Microsoft Excel
1.2 Improvements in the new version
1.3 Different components of Excel worksheet & workbook
1.4 Saving Excel File as .xlsx(.xls), .xlsm,.xlsb, .csv
1.5 Difference between Operators and Functions
1.6 Using Excel Shortcuts with Full List of Shortcuts -1
1.7 Copy, Cut, Paste, Hide, Unhide the Data in Rows, Columns and Sheet
1.8 Hyperlink

 

Chapter-2: Formatting Cell, Row, Column, Sheet – Merge Cells, Font, Color, Size, Style, Allignment,Wrap Text, Format Painter

2.1 HOME MENU -VARIANT OF AUTO, FILL & CLEAR FUNCTIONS
2.2 Logical Function: IF / ELSE, AND, OR, NOT, TRUE, NESTED IF/ELSE etc
2.3 Text Functions: LEFT, RIGHT, MID, LOWER, UPPER, PROPER, FIND, CONCATENATE, REPLACE, REPT,  TEXT, TRIM,SEARCH, SUBSTITUTE, TRUNC, CONVERT, DOLLAR etc
2.4 Using Excel Shortcuts with Full List of Shortcuts -2
2.5 Selection of visible cells & Paste Special Options

 

Chapter-3: Math and Trigonometry Functions: VARIANT OF RAND, ROUND, SUM, LOG function; CEILING, FLOOR, INT, LCM, MOD, EVEN/ODD, SUBTOTAL etc

3.1 Statistical Functions: VARIANT OF COUNT, MAX, MIN, AVERAGE, STDEV function; Forecast etc
3.2 Conditional Formatting -Single/ Multiple Cell Rules , Using Color Scales and Icon Sets in Conditional Formatting , 3.3 Creating New Rules and Managing Existing Rules
3.4 Using Excel Shortcuts with Full List of Shortcuts -3

 

Chapter-4: Data Sorting and Filtering -Single/Multiple level, Custom Sort & Filter

4.1 Date and Time Functions: DATE, DATEVALUE, DAY, DAY360, SECOND, MINUTES, HOURS, NOW, TODAY, MONTH, YEAR, YEARFRAC, TIME, WEEKDAY, WORKDAY etc
4.2 Sheet tab functions (Insert, delete,rename,move & copy,group & ungroup sheet tabs)
4.3 Page Layout and Printer Properties
4.4 Inserting Pictures and other objects in Worksheets

 

Chapter-5: Information Functions: CELL, ERROR.TYPE, INFO, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, TYPE etc

5.1 Types of common errors in Excel -ERROR.TYPE
5.2 Protecting & Unprotecting Cells, Rows, Columns and Sheets with or without Password
5.3 Shared workbook – advantages & disadvantages
5.4 Text to column, Remove duplicates
5.5 Import data from other sources

 

Chapter-6: Relative & Absolute Reference

6.1 Lookup and Reference Functions: VLOOKUP, HLOOKUP, INDEX, ADDRESS, MATCH, OFFSET, TRANSPOSE etc
6.2 Insert Table & charts – design/ switch between row & column/select data /change chart type, format axis, legends, secondary axis
6.6 Data validation

 

Chapter-7: Pivot table & Pivot chart, Comparison with Different functions

7.1 Slicer in Dynamic Dashboard
7.2 Sparkline charts
7.3 Formula Auditing – Evaluate formula
7.4 Insert/delete/edit/hide comments

 

Chapter-8: Array Functions, Comparison with Dbase functions

8.1 Database Functions: DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP etc
8.2 Gridlines,Freeze Panes, Arrange Windows
8.3 Excel Options – Debugging problems, Excel Add-ins
8.4 Group/ungroup rows & Columns, subtotal

 

Advanced VBA Macro Syllabus

Chapter-1: Create a Macro

1.1 Swap Values
1.2 Run Code from a Module
1.3 Macro Recorder
1.4 Use Relative References 1.5 FormulaR1C1
1.6 Add a Macro to the Toolbar
1.7 Macro Security
1.8 Protect Macro

 

Chapter-2: MsgBox

2.1 MsgBox Function
2.2 InputBox Function

 

Chapter-3: Workbook and Worksheet Object

3.1 Path and FullName
3.2 Close and Open
3.3 Loop through Books and Sheets
3.4 Sales Calculator
3.5 Files in a Directory
3.6 Import Sheets
3.7 Programming Charts

 

Chapter-4: Range Object

4.1 CurrentRegion
4.2 Dynamic Range
4.3 Resize
4.4 Entire Rows and Columns
4.5 Offset
4.6 From Active Cell to Last Entry
4.7 Union and Intersect
4.8 Test a Selection
4.9 Possible Football Matches
4.10 Font
4.11 Background Colors
4.11 Background Colors
4.12 Areas Collection
4.13 Compare Ranges

 

Chapter-5: Variables

5.1 Option Explicit
5.2 Variable Scope
5.3 Life of Variables

 

Chapter-6: If Then Statement

6.1 Logical Operators
6.2 Select Case
6.3 Tax Rates
6.4 Mod Operator
6.5 Prime Number Checker
6.6 Find Second Highest Value
6.7 Sum by Color
6.8 Delete Blank Cells

 

Chapter-7: Loop

7.1 Loop through Defined Range
7.2 Loop through Entire Column
7.3 Do Until Loop
7.4 Step Keyword
7.5 Create a Pattern
7.6 Sort Numbers
7.7 Randomly Sort Data
7.8 Remove Duplicates
7.9 Complex Calculations

 

Chapter-8: Macro Errors

8.1 Debugging
8.2 Error Handling
8.3 Err Object
8.4 Interrupt a Macro
8.5 Macro Comments

 

Chapter-9: Date and Time

9.1 Compare Dates and Times
9.2 DateDiff Function
9.3 Weekdays
9.4 Delay a Macro
9.5 Year Occurrences
9.6 Tasks on Schedule
9.7 Sort Birthdays

 

Chapter-10: Array

10.1 Dynamic Array
10.2 Array Function
10.3 Month Namess
10.4 Size of an Array

 

Chapter-11: Function and Sub

11.1 User Defined Function
11.2 Custom Average Function
11.3 Volatile Functions
11.4 ByRef and ByVal

 

Chapter-12: ActiveX Controls

12.1 Text Box
12.2 List Box
12.3 Combo Box
12.4 Check Box
12.5 Option Buttons
12.6 Spin Button
12.7 Loan Calculator

 

Chapter-13: Userform

13.1 Userform and Ranges
13.2 Currency Converter
13.3 Progress Indicator
13.4 Multiple List Box Selections
13.5 Multicolumn Combo Box
13.6 Dependent Combo Boxes
13.7 Loop through Controls
13.8 Controls Collection
13.9 Userform with Multiple Pages
13.10 Interactive Userform

Tanushree Bhattacharyya

Guest Faculty (Advanced Excel, R)

Tanushree, a post graduate in M.Sc(Econometrics & Statistics), having 8 yrs of experience in Analytics & Mkt Research.Currently working in a big MNC house, proficient in statistical tools like SAS, Advanced Excel, VBA, Access, SQL, SPSS, Quantum. She is highly skilled in data analysis and building statistical model, creating publication quality report and automation of the models with VBA/SAS/SPSS with an excellent track record of managing clients, projects and exceeding expectations. She is an expert in handling analytical projects involving various statistical techniques like demand forecasting , multivariate techniques, optimization, segmentation and reporting the insights to the management to fulfill the business requirements. She is involved with NIVT for over a year now and has an excellent track record of providing training to professionals on Excel,VBA & R Programming. On behalf NIVT she has conducted training in some corporate houses like Dynamic Level.