Excel Power Programming with VBA

Part I: Introduction to Excel VBA 1

Chapter 1: Essentials 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

Chapter 2: Introducing Visual Basic for Applications

• Getting a Head Start with 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

Chapter 3: VBA Programming Fundamentals

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

Chapter 4: Working with VBA Sub Procedures

• About procedures
• Executing Sub Procedures
• Passing Arguments to procedures
• Error- Handling Techniques
• A Realistic Example that uses Sub Procedures
• Utility Availability
• Evaluating the Project

Chapter 5: Creating Function Procedures

• Sub procedures versus functions 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

Chapter 6: Understanding Excel’s Events

• What you should know about events
• Getting Acquainted with Workbook-Level Events
• Examining Worksheet Events
• Monitoring with Applications Events

Chapter 7: VBA Programming Examples and Techniques

• Learning by Examples
• Working with Ranges
• Working with workbooks and sheets
• VBA Techniques
• Some Useful Functions for use in your code
• Some useful worksheet functions
• Windows API Cells

Part II: Advanced VBA Techniques

Chapter 8: Working with Pivot Tables

• An Introductory Pivot Table Examples
• Creating a more complex Pivot table
• Creating multiple pivot Tables
• Creating a reverse pivot table

Chapter 9: Working with Charts

• Getting the inside scoop on charts
• Creating an embedded chart
• Creating a chart on a chart sheet
• Modifying a chart
• 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 chart 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 Arbitrary data Labels on a chart
• Displaying a chart in userform
• Understanding chart events
• Discovering VBA Charting Tricks
• Working with sparkline charts

Chapter 10: Interacting with Other Applications

• Understanding Microsoft Office Automation
• Automating access from excel
• Automating word from excel
• Automating PowerPoint from excel
• Automating Outlook from excel
• Starting other applications from excel

Chapter 11: Working with External Data and Files

• Working with external Data connections
• Power Query Basics
• Using ADO and VBA to pull External data
• Working with text files
• Text file manipulation examples
• Performing common file operations
• Zipping and unzipping files

Part III: Working with UserForms

Chapter 12: Leveraging 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

Chapter 13: Introducing 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 UserForm
• Creating a Userform: An Example
• Referencing UserForms controls
• Customizing the toolbox
• Creating UserForms templates
• A UserForm checklist

Chapter 14: Looking 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
• Animation a label

Chapter 15: Implementing Advanced UserForm Techniques

• 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 resizable userform
• Handling multiple userform controls with one event handler
• Selecting a colour in a userform
• Displaying a chart in a userform
• Making a userform semitransparent
• A puzzle on a userform
• Video poker on a userform

Part IV: Developing Excel Applications

Chapter 16: Creating and Using Add-Ins

• What is an Add-In?
• Understanding excels add in manager
• Creating an add in
• And add in example
• Comparing XLAM and XLSM Files
• Manipulating Add ins with VBA
• Optimizing the performance of add ins
• Special problems with add ins

Chapter 17: Working with the Ribbon

• Ribbon basics
• Customizing the ribbon
• Creating a custom ribbon
• Using VBA with ribbon
• Creating an old style toolbar

Chapter 18: Working with Shortcut Menus

• CommandBar overview
• Referring to Controls in a CommandBar
• Properties of CommandBar Controls
• Displaying All Shortcut Menu Items
• Using VBA to customize shortcut menus
• Resetting a shortcut menu
• Shortcut menus and events

Chapter 19: Providing Help for Your Applications

• Help for your excel applications
• Help systems that use excel components
• Displaying help in a web browser
• Using a HTML help system

Chapter 20: Leveraging Class Modules

• What is a class module?
• Creating a Numlock class
• Coding properties, methods, and events
• Exposing a query table event
• Creating a class to hold classes

Chapter 21: Understanding Compatibility Issues

• What is compatibility?
• Types of compatibility problems
• Avoid using new features
• But will work on a mac?
• Dealing with 64-Bit Excel
• Creating an international application
• Multilanguage Applications
• VBA Language Considerations
• Using Local Properties
• Identifying System Settings
• Date and Time Settings