ISO 9001:2000 Certified Computer Institute
Why do Foreign/Indian Students prefer to study at COMPUFIELD?

Advance Excel Macros VBA


Course Title: Microsoft Excel VBA , Level 3 (2003, XP, 2003/2007)	Duration : 2 days
This course will help to write macros in Excel using programming language Visual Basic Application (VBA). Recorded macro can edited to customize it to fulfill our requirement and one can directly go for coding to accomplish the required tasks. One can write his own functions and procedures, design forms and working with add-ins.


Program Objectives

After completion of this program, the participants will be able to the following Write macros to automate routine work Create functions and procedures Design and use forms for data entry Create add-ins to deploy at client machines Create simple reports


Audience

Program is scheduled for Advanced Microsoft Excel users. VBA is a programming language, requires aptitude for programming.


Prerequisites

Participants attending this training should be familiar with the advanced operations in Microsoft Excel.

Topics Coverd :


Overview of the Basics

Customizing common options in Excel Absolute and relative cells Protecting and un-protecting worksheets and cells


Working with Functions

Writing conditional expressions (using IF) Using logical functions (AND, OR, NOT) Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX)


Data Validations

Specifying a valid range of values for a cell Specifying a list of valid values for a cell Specifying custom validations based on formula for a cell


Working with Templates

Designing the structure of a template Using templates for standardization of worksheets


Sorting and Filtering Data

Sorting tables Using multiple-level sorting Using custom sorting Filtering data for selected view (AutoFilter) Using advanced filter options


Working with Reports

Creating subtotals Multiple-level subtotals Creating Pivot tables Formatting and customizing Pivot tables Using advanced options of Pivot tables Pivot charts Consolidating data from multiple sheets and files using Pivot tables Using external data sources Using data consolidation feature to consolidate data


More Functions

Date and time functions Text functions Database functions


Formatting

Using auto formatting option for worksheets Using conditional formatting option for rows, columns and cells