Syllabus for Excel VBA & Macro (Inter Level)


·         Advantages & Disadvantages of using Excel Names.

·         Saving Strings, Formulas, Range references

·         Understanding Scope of Excel Names

·         Various ways of adding Names

·         Deleting, Modifying , Printing & Hiding Names

·         Adding Arrays of data to Name

·         Examples:

o   Creating dynamic range

o   Adding Data Validation using Name


·         Understanding Events

·         Difference between Open and Activate Event

·         Difference between Change and Selection Change Event.

·         Example:

o   Disabling double click, right click, Printing etc.

o   Assigning Shortcut keys to Macros

o   Automatically closing Excel book after given time.

o   Adding Footer before printing

o   Changing File Name when the Cell Value is changed

o   Creating a stronger & comprehensive Data Validation.

o   Changing Name of Sheet when Cell Value is changed

o   Auto Numbering

o   Creating a restricted area

o   Making unauthorized editing impossible

o   Adding to context menu

o   Creating a Security Sheet.

o   Various Observation Examples


·         Creating user form and adding various controls to it.

·         Making buttons responding to Enter and Esc Keys

·         Making Command button ALT key receptive

·         Rules for naming controls

·         Effortlessly creating controls

·         Validating data in user form

·         Making User form easy to navigate

·         Removing user form from the memory

·         Using Labels, Textbox, Command button, Combobox,

·         Adding Calendar

·         Adding Pictures (Static & Dynamically)

·         Example: Create a user form with Add, Clear, Delete, Edit, Find & Exit Functions

·         Adding Listbox at designtime

·         Adding Listbox at runtime

·         Knowing which single item is selected

·         Adding Table to the list

·         Extract selected item from Multicolumn listbox

·         Extract all selected items from Multicolumn listbox

·         Selecting Listbox items as per selection in Combobox

·         Multiline textbox and various features


·         Adding Chartsheet

·         Adding a datasource to it

·         Changing location

·         Difference between Chartsheet and Embeded charts

·         Different methods of creating embedded chart in 2003 & 2007

·         Assessing Chart converted to ChartObject (Excel 2003)

·         Positioning embedded Chart

·         Using Plot Area

·         Changing Charttype

·         Controlling Series

·         Copy/Pasting the Chart

·         Performing given task on all charts in the Sheet

·         Adding Secondary Axis to Chart

·         Exporting Chart as picture


·         Positioning Shapes

·         Selecting all shapes

·         Inserting various shapes

·         Adding Colors & formats to shapes

·         Connecting one shape with another.

·         Programmatically adding a Combo box etc form controls & adding a list and linking it will cells.

·         Performing other task when macro is running.

·         Animating Shapes

·         Inserting Pictures

·         Using Wordart

·         Copying Shapes

·         Copying shapes as picture

·         Changing Default property of shapes

·         Performing Adjustments with shapes

·         Inserting Text

·         Manipulating, formatting etc few characters in the text


·         Advantages and usage of Advance Filter

·         Getting unique data in One Column

·         Getting unique data from series of columns

·         Creating Summary Report

·         Extracting data by criteria from database

·         Creating dynamic filtering template


·         Understanding the concept of Pivot table programming

·         Clearing all Pivot tables from sheet

·         Creating Pivot Table

·         Adding Row, Column, Page & Data Field.

·         Setting the position of , Column, Page & Data Field

·         Adding Calculated Fields

·         Adding/Removing subtotals

·         Performing actions on all Pivot Fields

·         Speeding up the Code


·         Understanding concept of Toolbars, Menu bars & Controls.

·         Adding & Deleting Tool bar

·         Assessing Controls

·         Inserting Menubars and adding various controls to it.

·         Adding a picture to control

·         Assigning a Macro to control

·         Creating Submenus

·         Using Combobox in controls

·         Adding controls to right click

·         Enabling/Disabling Controls

·         Adding Menu & Controls for Addins.

·         Best Practices


·         Using simple technique to get work done with other applications.