Syllabus for Excel VBA & Macro (Foundation Level)


INTRODUCTION

·         Introduction about VBA

·         Difference between VBA & Macro.

·         Security concepts involved in Excel 2003 & 2007

·         Tips for getting skilled in VBA

·         Why we should limit use of a Macro Recorder for program development?

CHAPTER 1

·         Using Visual Basic Editor

·         Rules for naming a Subroutine/Function.

·         Introduction to Object, Properties & Methods

·         Understanding Library,  Class & Collections

·         Linking the Concept of Object, Properties & Methods with Object Browser

·         Using Immediate Window.

·         Assigning macro to a control.

·         Using Input box & Message box

·         Breaking long statements to multiple lines.

·         Entering multiple short statements in one line.

·         Concepts

o   Difference between ActiveCell & Selection

o   Difference between ThisWorkbook & ActiveWorkbook

o   Difference between Selected Sheets & Active Sheet.

o   What does Sheet Object includes?

·         Examples:

o   Creating a new book,

o   Entering arrays of data both horizontally & vertically.

o   Inserting rows,

o   Finding needed data & Inserting rows dynamically,

o   Inserting, Moving & Naming Worksheet, 

o   Inserting Worksheet Name dynamically, 

o   Creating various form controls like Command button, Group box & Option button,

o   Formatting Cells,

o   Showing/Hiding Columns,

o   Showing/Hiding/Very Hiding Sheets,

o   Sorting data, Four Methods of Copying Data, Importing Data.

o   Importing Text data using QueryTable property

·         Extra Questions:

o   Getting Last Sheet,

o   Knowing & Changing Sheets Name,

o   Showing Print Preview of Sheet, Range & Workbook,

o   Adding, deleting & modifying Comment,

o   Doing spell check,

o   Clearing the content of cells,

o   Replacing the content,

o   Auto fitting columns and rows,

o   Merging & Unmerging cells,

o   Printing range, sheet & pages,

o   Protecting & un protecting sheet, workbook with or without password,

o   Adding background picture,

o   Showing/Hiding Gridlines, Formula bar etc.,

o   Formatting all or few characters in cell,

o   Adding borders.

·         Using Offset Property.

·         R1C1 Formula reference.

·         Test for Offset & R1C1

·         Resize Property

·         Overcoming the limitation of most popular method of getting last row.

·         Example:

o   Getting the last row

o   Getting last column

o   Entering correct formula in every cell,

o   Creating Multiplication Table,

o   Adding total to table,

o   Running Total,

o   Bank Balance statement.

o   Copying Data except heading.

o   Converting Pivot like data to Database format.

·         Test on Resize.

·         Referring to Non contiguous cells

·         If/End If Condition.

·         Using Names while addressing to ranges.

·         Adding Spinner Control

·         Example:

o   Transferring Template data to Database.

o   Displaying, Editing & Deleting records from Database

o   Getting correct data from dynamically changing data.

·         Learning various ways of referring

o   Ranges

o   Sheet

o   Workbook

·         Importance of using an Object Variable in program.

·         Changing Object name of Sheet & Workbook.

·         Handing situations when Object returns Nothing.

CHAPTER 2

·         Error Handling Methodologies

·         Suppressing unwanted dialogs display during program execution.

·         Creating Subroutine with Arguments & calling them.

·         Understanding Enum, Creation & Advantages

·         Examples:

o   Subroutine to create sheets without returning error.

o   Creating Functions for getting LastRow & LastColumn

o   Creating Subroutine for creating Borders

o   Creating Subroutine for Cell Formatting.

·         Creating, Installing & Removing Addins to boost efficiency.

·         Creating References.

·         Various productivity tools in VBE

o   Executing user statement like inbuilt statement

o   Displaying all variable definitions

o   Soft Stopping the code

o   Hard Stopping the code

o   Conditional Stopping the code

o   Change Stopping the code

o   Keeping track of important elements of code.

o   Reaching Cursor position

·         Repeating codes in known limits

·         Repeating codes by adding a Step to it.

·         Understanding Chr Function

·         Examples:

o   Adding series

§  Vertically,

§   Horizontally,

§  Front Diagonally &

§  Backward Diagonally.

o   Create a One Color & Three Color Band.

o   Enter Alphabets from A-Z

o   Creating Alternative Sheet Names

o   Creating multiplication table across sheets

o   Create Hourglass like pattern.

o   Create Chessboard pattern.

·         Understanding creation of random numbers & texts

·         Short method for doing PasteSpecial values.

·         Looping within a collection

·         Knowing the presence of one text within another.

·         Looping within an Array

·         Understanding Hyperlinks, Shapes, Commandbars & Commandbar Control collections.

·         Examples:

o   Creating a Subroutine to add dummy data

o   Entering Row numbers in ranges

o   Adding many sheets with known name without creating garbage codes

o   Deleting default sheets

o   Inserting sheets based on cell values.

o   Conditional Formatting

o   Adding different cell comments

o   Creating Index by creating Hyperlinks to sheets.

o   Inserting in text frame of all Shape Objects in sheet.

o   Knowing all Commandbars name

o   Knowing all Commandbars and their Subsidary Control names upto 2 levels.

·         Repeating code on the basis of a condition.

·         Examples:

o   Appling Cheque amount to various invoices

o   Adding Subtotal to Data.

CHAPTER 3

·         Displaying and using a Folder open Dialog box. 

·         Getting list of File names in a folder

·         Copying, Renaming, Deleting list of files in a go

·         Creating various files and entering Dummy Data to it.

·         Consolidating the content of those files in one file.

·         Creating Folders

·         Understanding Date Concept

·         Getting present Date and Time

·         Entering in a particular format

·         Knowing Saved status of a file

·         Using Autofilter

·         Examples:

o   Copying relevant columns data only no matter any columns are inserted/deleted

o   Copy data that satisfies the given criteria

o   Copying special type of cells

CHAPTER 4

·         Things to remember while creating an Array

·         Method of storing multiple data in Array

·         Defining holding capacity of an Array  (Static, Dynamically, Between)

·         Returning the values of an Array

·         Impact on Speed of program

·         Getting Lower/Upper limit of an  Array

·          Changing the bottom limit of an Array

·         Stopping Array from releasing values

·         Storing Tabular data in Array

·         Example: Storing data based on a criteria and dumping its values elsewhere.

·         Understanding Type & purpose of using

·         Storing Arrays in Type

CHAPTER 5

·    Scope of Variable

·         Naming Convention

·         Dealing with Error Handlers

·         Knowing the type of error generated

·         Performing different operations on the basis of errors

·         Ignoring Error statement and cancelling Error ignoring statement

·         Holding capacity of Datatypes.

CHAPTER 6

·         Creating User Defined Functions

·         Finding a pattern match

·         Examples

o   Getting the name of Workbook

o   Getting Full path of Workbook

o   Getting present time (Static)

o   Getting Random numbers (Static)

o   Getting LastSaved Date & Time of a file

o   Getting Font Color Index of selected Cell

o   Getting Email Address out of hyperlink

o   Getting Random Text out of Array of Data

o   Getting Random Text out of values from Range

o   Getting a Random number between a range

o   Getting Column Name

o   Getting Column Number

o   Simple Add Formula

o   Simple Sum Formula

o   Getting ODD/EVEN row type

o   Getting only Weekdays

o   Creating formula like Sumif

o   Creating formula like Vlookup

o   Sum by Color

o   Getting first non blank cell.

o   Exploding data in a cell to various cells based on a delimiter

o   Showing text separated by enter in a cell to different cells

o   Exploding data in a cell to various cells based on two delimiters

o   Finding One text in Long Text and returning corresponding lookup text

o   Doing concatenate and lookup on multiple fields

o   Converting a list of duplicate data to unique data

o   Retrieving only numbers from Alphanumeric data

o   Returning the address of cells that contains lookup string

o   Reversing the content

o   Returning the Cell addresses separated by comma having maximum values

o   Array Formula to get only non empty cells

o   One Dimensional & two Dimensional Array Formula. 

Creating Unique Random Function.

Comments