DURATION: 16 Hours (2 Full Day)

Course Content:

OVERVIEW

• What is VBA or Macros?
• What it can be used for
• Activate the Developer Tab
• Macro Security Settings

RECORDING A MACRO

• Naming & Recording a simple Macro
• Recording using Relative & Absolute References
• Editing a Recorded Macro in the VBA Editor
• Saving the File as Macro Enabled Workbook

RUNNING AND ASSIGNING A MACRO

• Running a Macro using a Toolbar
• Assigning a Macro to a Button or Drawn Object on a Sheet 
• Assigning a Macro to a Quick Access Toolbar
• Creating a Short-Cut Key to Run a Macro 

THE VBA EDITOR

• Opening and Closing the VBA Editor
• Using the Project Explorer
• Inserting a Module
• Working with the Properties and Code Windows

UNDERSTANDING VBA TERMINOLOGY

• Module, Procedures, Functions
• Object, Property, Methods
• Application, Workbook, Worksheet, Range

PROCEDURES

• Creating a Sub Routine
• Understanding the code structure
• Commenting Statements
• Calling procedures

USER INTERACTION

• Creating an Interactive Message Box
• Creating a Custom Input Box
• Advantage of Application.Inputbox

CONDITIONAL STRUCTURES

• Simple IF (IF…THEN)
• IF Else (IF…THEN…ELSE)
• IF & Logical Operators (IF AND/OR…THEN)
• Nested IF (IF…THEN…ELSEIF)
• SELECT CASE
• CASE ELSE

VARIABLES AND CONSTANT

• Declaring a Variable’s Data Type
• Working with Object Variables
• Option Explicit
• Constants

CONTROL STRUCTURES (LOOPS)

• For Loop (FOR…NEXT)
• For Each Loop (FOR EACH…NEXT)
• Do Until Loop (DO UNTIL…LOOP)
• Do While Loop (DO WHILE…LOOP)

WORKING WITH RANGE

• Referring to a Range
• Entering Values and Formulas
• Formatting Cells and Values
• With…Statement
• Using CurrentRegion Property
• Accessing a range using UsedRange Property
• The End Property
• Offset
• Select Special Cells
• Sort & Filter
• Rows and Columns (Insert/Delete/Autofit/Hide)

WORKING WITH WORKSHEETS

• Activate Worksheet
• Add/Delete/Rename a Worksheet
• Copy/Move a Worksheet
• Working with Group Worksheets
• Hide/ Protect Worksheets

WORKING WITH WORKBOOK

• Open/Close Workbook
• Save/Protect Workbook
• This Workbook

WORKING WITH APPLICATION

• Screen updating/ Display Alerts
• Cut copy Mode/ Username/ Speech

USER DEFINE FUNCTIONS

• Using Excel Worksheet Functions in VBA
• VBA Function
• Creating User-Define Functions
• Using Range and declaring variables in User defined functions

CREATING USER FORMS

• Creating a custom form
• Adding text boxes to forms
• Moving controls
• Adding label controls to forms
• Changing text box and label properties
• Combo box, option, command

USING EXCEL EVENTS

• Sequences of Events
• Events Associated with Worksheets
• Events Associated with a Workbook

ERROR HANDLING & DEBUGGING

• Understanding different Error Types
• Debugging: Stepping through Code and Breakpoint
• On Error Goto Statement
• Resume Next Syntax

 Excel Prodigy have conducted 750+ Excel VBA Macro Training for Corporate companies in Chennai & All Major Cities in India. We also conduct Excel Macro VBA Training Workshops in Chennai every month. Our trainer are experts in Excel VB Programming and created many Excel VBA Automation project for top level corporate’s.