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.
o Creating Unique Random Function.
