Excel Macros

Objective : To learn excel macros in order to do handy programming in MS Excel, for automating own work processes.
Suitability : Executives & Officers at supervisory / middle management / top level
Prior excel background : This programme is suitable for participants with good excel knowledge, atleast 1 year of working experience on excel is recommended. The participants should be familiar with excel functionalities like Paste-special, Text-to-columns, Sort, Pivot tables, Filters, functions like if, vlookup, count etc
Duration : 20 Hours or 3 Days
Functionalities Covered
Basics
  • Introduction to macros
  • Introduction to VBA environment
  • Macro recorder
  • Viewing macros, opening & running them
  • Break mode, watch window and locals window

Interaction with users
  • Msgbox
  • Inputbox
  • file dialogue box
  • Buttons and checkboxes

Loops
  • For next loop
  • Do until loop
  • For each..next loop
  • Do while loop
Developing macros - tools and actions
  • Find method
  • If - then - elseif - else - end if, with - end with
  • R1C1 notation
  • Working on multiple sheets and multiple files
  • Dragging of cell contents till last cell
  • Automatic Procedures and Events
  • Working on existing autofilters
  • 'Very hiding' excel sheets
  • Creating user defined functions
  • Turning off screen updating and display alerts

Error Handling
  • Types of errors
  • Automatically handling run time errors
  • Turning off error handling after use

Userforms and dashboards
  • Creating userforms: basics and controls
  • Accessing macros through user interface
  • Creating interactive dashboards using macros
Miscellaneous
  • Macro protection - assigning password
  • Importing and exporting modules
  • Calling subs/functions across modules