Microsoft EXCEL and VBA Programming

Description

Excel Programming Practice delivers state-of-the-art programming precepts by manipulating objects constituting the Excel application and by using custom-designed user-forms to develop solutions that are fully attuned to a situation. Excel exposes two access points to the functionality of the product: The first access point is via the familiar Graphical User Interface (GUI) – the access point for 95% of Excel users. The second access point is via manipulation of objects constituting the Excel application – Microsoft exposes underlying objects for programmatic manipulation.

For sophisticated applications, there is no alternative but to work with Excel Objects which are manipulated using Visual Basic for Applications, the language for programming Excel. While some users program Excel using Macros, the true power of Excel can be accessed only with visual basic code. Many enterprises are unaware that the database in which they have invested millions of dollars is good only for storing and retrieving data but ill-suited for numerical analysis for which Excel is the appropriate tool. Thus, databases and numerical analysis tools (such as Excel) have distinct roles to play and an Excel based solution often requires the data component of the application to be supplied from an external source such as the Internet or the back-end server.

This course comprises of slide presentations, problem solving, and discussions on

• Conceptual topics,
• The architecture of the Excel User Interface,
• The Excel Programming Architecture,
• Important features of Excel,
• Excel objects and object hierarchy,
• The structure of the visual basic language for programming Excel.

Participants will be divided into groups of two to four and each group will work on situations using Excel; participants will receive a CD with topics for problem solving and will be encouraged to discuss and question.

Who Should Attend:- Candidates who are having any graduate degree.

• Components of Financial Modeling
• Definition of Financial Modeling
• Introduction to Financial Modeling
• What is not a Financial Model
• Advantages of Financial Modeling
• IPO Framework
• Layout Issues
• Examples of Models
• Databases and Spreadsheets
• Excel Overview
• Accessing Excel
• Code Modules
• Objects
• Collections
• Manipulating Objects
• Identifying Objects
• Worksheet as a Platform
• Form as a Platform
• Requirements for Modeling
  • Arrays
  • Functions
  • Built-In
  • Analysis Tool Pak
  • User-Defined
  • Names
  • Formatting Values
  • Excel Controls
  • Add-Ins
  • Data Validation
  • Data Management
  • Lists
  • Data Filters
  • D Functions
  • Pivot Tables
  • What-If Analysis
  • One Variable Data Tables
  • Two Variable Data Tables
  • Goal Seek
  • Solver
• Scenario Manager
• Entry Points into Excel
• GUI Vs Code
• Visual Basic Code
• Parts of a VB Procedure
• Executing Code
• Manipulating Excel with Objects, Properties and methods
• Object Hierarchy
• Object Collections
• Excel Object Model
• Macro Recorder
• Control Structures
• Types of Control Structures
• Excel Events
• User Forms
• VB Controls
• Alternatives to Forms
• VB Functions
• Object Browser
• Range Object
• Worksheet Object, Properties, Methods and Events
• Range Properties and Methods
• VB Variables and Constants
• Arrays
• VB Operators
• Examples of Code
• Hands-On Programming