VBA Excel Training in Gurgaon

Innovative Technology Solutions

www.itstraining.in

VBA Excel VBA stands for Visual Basic for Applications an event driven programming language that is now predominantly used with Microsoft office applications such as MS-Word,MS-Excel & MS-Access. We can use VBA in all office versions right from MS-Office 97 to MS-Office 2013 and also with any of the latest versions available. Among VBA, Excel VBA is the most popular one. The reason for using VBA is that we can build very powerful tools in Microsoft Excel using linear programming.

www.itstraining.in

VBA Excel Objectives • • • • • • • • • •

Create recorded macros in Excel Use the macro recorder to create a variety of macros Understand the Excel object model and VBA concepts Work with the three main components of the VBA Editor window Create command procedures Create and use variables Create and work with user-defined functions Write code to manipulate Excel objects Use a range of common programming techniques Create a custom form complete with controls and event procedures • Code to drive a user form • Create procedures that start automatically • Write a variety of error handling routines www.itstraining.in

Prerequisites • Participants should have programming background • All attendees must have prior knowledge of Excel 2010.

www.itstraining.in

Course Content 1. Introduction to VBA • • • • • •

Introduction to Excel Introduction to Programming Introduction to VBA How to launch Visual Basic Editor What you can do with Macros How to use Visual Basic Editor (VBE)

2. Understanding Loops & Writing VBA Code • • • • •

Using Do While Loop & IF Statement in VBA DO WHILE Loop Explained Using FOR NEXT Loop in Excel Using SELECT CASE Statement in VBA Working with User Defined Functions (UDF)

www.itstraining.in

3. Variables & More • Sneak peek in to Excel Object Model • Deep Dive in to VBA: Theory on Variables, Scoping etc.

• How to Save and Reuse your Macros

4. Dealing with Cells & Ranges • • • •

Displaying Message Boxes using VBA Understanding & Using Cells Object File Handling: Opening, Reading & Writing to Files from VBA Wrapping a Set of Formulas with IFERROR

5. Workbooks, Worksheets & Databases • Introduction to MS Access & SQL • Using Worksheets, Workbook Objects in VBA: Example on how to save a copy of workbook • Consolidate Multiple Workbooks in to one using VBA • Delete Blank Cells & Sort a List using VBA • Generating Multiple PDF Reports using VBA www.itstraining.in

6. Working with Charts and Shapes • Cleaning up multiple charts using VBA • Interactive Excel Charts using VBA • Animating Excel Charts with VBA

7. Working with Pivots • Pivot Tables & VBA: Example #1 • Generating Multiple Pivot Table Reports using VBA • BONUS: Guest Lecture on Pivot Tables & VBA by Debra

8. User Forms & Databases • Linking to Databases from Excel & Working with them • User Form Basics

www.itstraining.in

9. More users Forms & Example Application • Understanding User Form Events & Working with Multiple Forms • Building an Application using User Forms & VBA

10. Integrating with Word & PowerPoint • Generate MS Word Reports from Excel • Create a PPT Slide Deck from Excel

11. Additional VBA Techniques • Running a Macro when user selects a cell • Showing & Hiding information using VBA • Text Processing & Analysis using VBA

www.itstraining.in

Thank You

Innovative Technology Solutions, B 100 A, South city 1, Near Signature Towers, Gurgaon – 122001 91-124-4253759 | 92015134607 enquiry@itstraining.in www.itstraining.in