Microsoft Excel VBA EXL-VBA

Module 1Essentials of Spreadsheet Application

• Development
• What Is a Spreadsheet Application?
• Steps for Application Development
• Determining User Needs
• Planning an Application That Meets User Needs
• Determining the Most Appropriate User Interface
• Concerning Yourself with the End User
• Other Development Issues

Module 2Introducing Visual Basic for Applications

• Getting a Head Start with the Macro Recorder
• Working with the Visual Basic Editor
• VBA Fundamentals
• Deep Dive: Working with Range Objects
• Essential Concepts to Remember
• Don’t Panic – You Are Not Alone

Module 3VBA Programming Fundamentals

• VBA Language Elements: An Overview
• Comments
• Variables, Data Types, and Constants
• Assignment Statements
• Arrays
• Declaring Arrays
• Object Variable
• User -Defined Data Types
• Built -in Functions
• Manipulating Objects and Collections
• Controlling Code Execution

Module 4Working with VBA Sub Procedures

• About Procedures
• Executing Sub Procedures
• Pas sing Arguments to Procedures
• Error-Handling Techniques
• A Realistic Example That Uses Sub Procedures
• Utility Availability
• Evaluating the Project

Module 5Creating Function Procedures

• Sub Procedures vs. Function Procedures
• Why Create Custom Functions?
• An Introductory Function Example
• Function Procedures
• Function Arguments
• Function Examples
• Emulating Excel’s SUM Function
• Extended Date Functions
• Debugging Functions
• Dealing with the Insert Function Dialog Box
• Using Add-Ins to Store Custom Functions
• Using the Windows API

Module 6Understanding Excel’s Events

• What You Should Know About Events
• Getting Acquainted with Workbook - Level Events
• Examining Worksheet Events
• Monitoring with Application Events

Module 7VBA Programming Examples and Techniques

• Learning by Example
• Working with Ranges
• Working with Workbooks and Sheets
• VBA Techniques
• Some Useful Functions for Use in Your Code
• Some Useful Worksheet Functions
• Windows API Calls

Module 8Working with Pivot Tables

• An Introductory Pivot Table Example
• Creating a More Complex Pivot Table
• Creating Multiple Pivot Tables
• Creating a Reverse Pivot Table

Module 9Working with Charts

• Getting the Inside Scoop on Charts
• Creating an Embedded Chart
• Creating a Chart on a Chart Sheet
• Modifying Charts
• Using VBA to Activate a Chart
• Moving a Chart
• Using VBA to Deactivate a Chart
• Determining Whether a Chart Is Activated
• Deleting from the ChartObjects or Charts Collection
• Looping Through All Charts
• Sizing and Aligning ChartObjects
• Creating Lots of Charts
• Exporting a Chart
• Changing the Data Used in a Chart
• Using VBA to Display Custom Data Labels on a Chart
• Displaying a Chart in a UserForm
• Understanding Chart Events
• Discovering VBA Charting Tricks
• Working with Sparkline Charts

Module 10Leveraging Custom Dialog Boxes

• Alternatives to UserForms
• Using an Input Box
• Using the VBA MsgBox Function
• Using the Excel GetOpenFilename Method
• Using the Excel GetSaveAsFilename Method
• Prompting for a Folder
• Displaying Excel’s Built-in Dialog Boxes
• Displaying a Data Form

Module 11Introducing UserForms

• How Excel Handles Custom Dialog Boxes
• Inserting a New UserForm
• Adding Controls to a UserForm
• Toolbox Controls
• Adjusting UserForm Controls
• Adjusting a Control’s Properties
• Displaying a UserForm
• Closing a UserFor m
• Creating a UserForm: An Example
• Referencing UserForm Controls
• Customizing the Toolbox
• Creating UserForm Templates
• A UserForm Checklist

Module 12Looking at UserForm Examples

• Creating a UserForm “Menu”
• Selecting Ranges from a UserForm
• Creating a Splash Screen
• Disabling a UserForm’s Close Button
• Changing a UserForm’s Size
• Zooming and Scrolling a Sheet from a UserForm
• Exploring ListBox Techniques
• Using the MultiPage Control in a UserForm
• Using an External Control
• Animating a Label

Module 13Implementing Advanced UserForm T echniques

• A Modeless Dialog Box
• Displaying a Progress Indicator
• Creating Wizards
• Emulating the MsgBox Function
• A UserForm with Movable Controls
• A UserForm with No Title Bar
• Simulating a Toolbar with a UserForm
• Emulating a Task Pane with a UserForm
• A Resiza ble UserForm
• Handling Multiple UserForm Controls with One Event Handler
• Selecting a Color in a UserForm
• Displaying a Chart in a UserForm
• Making a UserForm Semitransparent
• A Puzzle on a UserForm
• Video Poker on a UserForm