Excel VBA Macros

Join our free video course on Excel VBA Macros to get started – Click Here

We organise Corporate workshops in Mumbai, Kolkata, Bangalore, New Delhi, Gurgaon, Chennai, Hyderabad, Pune etc.

Key Learning: Automate repetitive tasks | Prepare month-end reports with the click of a button | Automate PowerPoint preparation from Excel reports | User-defined functions (UDF) | Prepare User Forms … and more

 

Introduction to Macros


  • The Power of Macros – What can be done with Macros and When to use Macros
  • Objects, their functions, methods and properties
  • Introduction to Events
  • What are events, how & when to use them

Preparing to ‘Macro’


  • Visual Basic Editor (VBE) – Developer Tab, Security
  • Introduction to the VBE, Project Explorer, Properties window, Password protection of code
  • How to use the VBE – Features, Options, Intellisense technology
  • Debugging mode
  • Inbuilt VBE Help feature – Tips and Tricks.
  • How and when to use the inbuilt help features, Object browser
  • Common terminology
  • Terms like ‘Keywords’, ‘Compile’, ‘Debug’ etc.
  • Recording a Macro
  • If and when to use Macro recorder along with the code

More On Excel Macros – I


  • Structure of an Excel workbook from VBA point of view -Common Objects
  • MS Excel Objects like Applications, Workbook, work sheets, etc. Where to write the code?
  • Variables and Constants
  • What are variables and constants?
  • Type of variables; How and when to use variables to store information.
  • Typical Variable naming conventions
  • When to use Variables or constants

More on Excel Macros – II


  • Loops
  • For-Next, For-Each, Do-While, Do-Until
  • Decision-making and Code Branching
  • If-Then-Else, Select-Case, And/Or conditions
  • User defined functions (UDF)
  • What are user defined functions. How to create & use them.

Getting into the Code


  • MsgBox
  • Working with Data in Excel
  • Data types in Excel worksheets, like Dates, Texts, Nulls etc.
  • Working with workbooks, worksheets, ranges, cells etc.
  • Using offset and other cell navigation methods.

Power Data Processing


  • Working with Data in Excel
  • Working with dynamic ranges.
  • Protecting worksheets, cells and ranges.
  • Working with multiple files. Opening & Saving files

Illustrative Examples


  • Using Google to locate VBA code
  • Using Sample Code Snippets – Merging 100s of workbooks in one, Splitting worksheets into multiple workbooks, Creating index of hyperlinks to worksheets


 

Learning Pre-requisites: Cell/Range Navigation shortcuts; Logical Statements – IF(), AND(), OR(), ISBLANK(), ISNUMBER(); Cell Format – General, Text, Number, Date; Basic formulas – SUM(), AVERAGE(), COUNT(), CONCATENATE(), LEFT(), RIGHT, MID(); Sort & Filter, Basics of Pivot Table