Advance Excel VBA Workshop @ Delhi

This course introduces the participants from basics to advance level of programming in Microsoft Excel using Visual Basic for Applications (VBA). The course starts with the recording of keystrokes as macros, and gradually builds up to cover the basic language constructs of VBA. Utilising the DOM of excel, writing custom procedures, custom functions, utilising built-in string functions, date functions designing forms using controls such as Textbox, Command button, label, option button etc.

Program Objectives
Upon completion of the program, the participants would be able to do the following:

• Automate processes to speed up routine work by creating custom functions and sub procedures
• Use decision making IF concepts for testing different conditions
• Using different types of Loops for automating repetitive task
• Design and use forms for data entry
• Utilise built-in functions for creating custom procedures and functions
• Automate consolidating data from different files
• Create an automated Chart, PivotTables
• Automate the process of filtering data by using AutoFilter and Advanced filter objects and Properties.

Audience Pre-requisite
This course is meant for power-users of Microsoft Excel, who want to further automate their tasks in Excel. Since this course covers VBA, which is a programming language, the participants should have an aptitude for programming. And if the audiences are having prior knowledge of programming it is appreciated

Course Outline in Brief with Day-wise break up
Day 1

Module 1: Recording and running Macro’s, making basic editing in recorded macro, understanding the concept of recording macro’s in Relative and Absolute method

Module 2: Understanding DOM (Document Object Model) of Excel, Creating custom procedures using objects such as Pagesetup, Font, characters objects etc. Adding custom procedures to quick access toolbar, creating custom TAB

Module 3: Understanding about Datatypes, Operators, Variables, Creating custom functions, using decision making concepts (IF), solving problems of IF, Select Case statements, working with built-in functions InputBox and MsgBox, Creating automated chart using Chart objects and properties

Day 2

Module 4: Understanding different forms of Loops such as For loop, For Each loop, Do-Loops, writing programs using loops, Solving practical problems using various types of loops and decision making

Module 5:  Working with built in String and Date functions such as (Strcomp, Instr, Instrrev, Ucase, Lcase, Date, Month, Year, Day etc)

Day 3

Module 6: Working with Filter objects, Copy, Paste objects ustilising Selection method for writing custom programs for process automation. Using Advanced filter object properties for unique data extraction. Consolidating data from different files into one, Creating Automated PivotTabels

Module 7: Using PivotTable Objects and properties for creating automated PivotTables,
MsExcel VBA (Macro) Programming,Interacting with other applications for copy, pasting data, learning to set the preferences for interacting with other applications

Module 8: Creating User forms using different controls such as Command button, Labels, Textbox, Option buttons, Frames, Check boxes etc. working with Global variables, automating protecting and un-protecting multiple sheets by creating custom UI

Please write to us at mudit.malhotra@teamleaseuniversity.ac.in