Home Page Icon
Home Page
Table of Contents for
Cover
Close
Cover
by John Walkenbach
Excel® 2010 Power Programming with VBA
Cover
Table of Contents
Title Page
Introduction
Part I: Some Essential Background
Chapter 1: Excel 2010: Where It Came From
A Brief History of Spreadsheets
Why Excel Is Great for Developers
Excel's Role in Microsoft's Strategy
Chapter 2: Excel in a Nutshell
Thinking in Terms of Objects
Workbooks
Excel's User Interface
Customizing the Display
Data Entry
Formulas, Functions, and Names
Selecting Objects
Formatting
Protection Options
Charts
Shapes and SmartArt
Database Access
Internet Features
Analysis Tools
Add-Ins
Macros and Programming
File Format
Excel's Help System
Chapter 3: Formula Tricks and Techniques
About Formulas
Calculating Formulas
Cell and Range References
Using Names
Formula Errors
Array Formulas
Counting and Summing Techniques
Working with Dates and Times
Creating Megaformulas
Chapter 4: Understanding Excel Files
Starting Excel
File Types
Working with Template Files
Inside an Excel File
The OfficeUI File
The XLB File
Add-In Files
Excel Settings in the Registry
Part II: Excel Application Development
Chapter 5: What Is a Spreadsheet Application?
Spreadsheet Applications
The Developer and the End User
Solving Problems with Excel
Basic Spreadsheet Types
Chapter 6: Essentials of Spreadsheet Application Development
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
Part III: Understanding Visual Basic for Applications
Chapter 7: Introducing Visual Basic for Applications
Getting Some BASIC Background
Delving in to VBA
Covering the Basics of VBA
Introducing the Visual Basic Editor
Working with the Project Explorer
Working with Code Windows
Customizing the VBE Environment
The Macro Recorder
About Objects and Collections
Properties and Methods
The Comment Object: A Case Study
Some Useful Application Properties
Working with Range Objects
Things to Know about Objects
Chapter 8: VBA Programming Fundamentals
VBA Language Elements: An Overview
Comments
Variables, Data Types, and Constants
Assignment Statements
Arrays
Object Variables
User-Defined Data Types
Built-in Functions
Manipulating Objects and Collections
Controlling Code Execution
Chapter 9: Working with VBA Sub Procedures
About Procedures
Executing Sub Procedures
Passing Arguments to Procedures
Error-Handling Techniques
A Realistic Example That Uses Sub Procedures
Chapter 10: Creating Function Procedures
Sub Procedures versus 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
Chapter 11: VBA 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
Part IV: Working with UserForms
Chapter 12: Custom Dialog Box Alternatives
Before You Create That UserForm . . .
Using an Input Box
The VBA MsgBox Function
The Excel GetOpenFilename Method
The Excel GetSaveAsFilename Method
Prompting for a Directory
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
Understanding UserForm Events
Referencing UserForm Controls
Customizing the Toolbox
Creating UserForm Templates
A UserForm Checklist
Chapter 14: 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
ListBox Techniques
Using the MultiPage Control in a UserForm
Using an External Control
Animating a Label
Chapter 15: 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
A Resizable 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
An Enhanced Data Form
A Puzzle on a UserForm
Video Poker on a UserForm
Part V: Advanced Programming Techniques
Chapter 16: Developing Excel Utilities with VBA
About Excel Utilities
Using VBA to Develop Utilities
What Makes a Good Utility?
Text Tools: The Anatomy of a Utility
More about Excel Utilities
Chapter 17: Working with Pivot Tables
An Introductory Pivot Table Example
Creating a More Complex Pivot Table
Creating Multiple Pivot Tables
Creating a Reverse Pivot Table
Chapter 18: Working with Charts
Getting the Inside Scoop on Charts
Creating an Embedded Chart
Creating a Chart on a Chart Sheet
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
Exporting a Chart
Changing the Data Used in a Chart
Using VBA to Display Arbitrary Data Labels on a Chart
Displaying a Chart in a UserForm
Understanding Chart Events
Discovering VBA Charting Tricks
Animating Charts
Creating an Interactive Chart without VBA
Working with Sparkline Charts
Chapter 19: Understanding Excel's Events
What You Should Know about Events
Getting Acquainted with Workbook-Level Events
Examining Worksheet Events
Checking Out Chart Events
Monitoring with Application Events
Using UserForm Events
Accessing Events Not Associated with an Object
Chapter 20: Interacting with Other Applications
Starting an Application from Excel
Activating an Application with Excel
Running Control Panel Dialog Boxes
Using Automation in Excel
Sending Personalized E-Mail via Outlook
Sending E-Mail Attachments from Excel
Using SendKeys
Chapter 21: Creating and Using Add-Ins
What Is an Add-In?
Understanding Excel's Add-In Manager
Creating an Add-in
An Add-In Example
Comparing XLAM and XLSM Files
Manipulating Add-Ins with VBA
Optimizing the Performance of Add-ins
Special Problems with Add-Ins
Part VI: Developing Applications
Chapter 22: Working with the Ribbon
Ribbon Basics
Using VBA with the Ribbon
Customizing the Ribbon
Creating an Old-Style Toolbar
Chapter 23: Working with Shortcut Menus
CommandBar Overview
Using VBA to Customize Shortcut Menus
Shortcut Menus and Events
Chapter 24: Providing Help for Your Applications
Help for Your Excel Applications
Help Systems That Use Excel Components
Displaying Help in a Web Browser
Using the HTML Help System
Associating a Help File with Your Application
Chapter 25: Developing User-Oriented Applications
What is a User-Oriented Application?
The Loan Amortization Wizard
Application Development Concepts
Part VII: Other Topics
Chapter 26: Compatibility Issues
What Is Compatibility?
Types of Compatibility Problems
Avoid Using New Features
But Will It Work on a Mac?
Dealing with 64-bit Excel
Creating an International Application
Chapter 27: Manipulating Files with VBA
Performing Common File Operations
Displaying Extended File Information
Working with Text Files
Text File Manipulation Examples
Zipping and Unzipping Files
Working with ADO
Chapter 28: Manipulating Visual Basic Components
Introducing the IDE
The IDE Object Model
Displaying All Components in a VBA Project
Listing All VBA Procedures in a Workbook
Replacing a Module with an Updated Version
Using VBA to Write VBA Code
Adding Controls to a UserForm at Design Time
Creating UserForms Programmatically
Chapter 29: Understanding Class Modules
What is a Class Module?
Example: Creating a NumLock Class
More about Class Modules
Example: A CSV File Class
Chapter 30: Working with Colors
Specifying Colors
Understanding Grayscale
Experimenting with Colors
Understanding Document Themes
Working with Shape Objects
Modifying Chart Colors
Chapter 31: Frequently Asked Questions about Excel Programming
Getting the Scoop on FAQs
General Excel Questions
The Visual Basic Editor
Procedures
Functions
Objects, Properties, Methods, and Events
UserForms
Add-Ins
User Interface
Part VIII: Appendixes
Appendix A: Excel Resources Online
The Excel Help System
Microsoft Technical Support
Internet Newsgroups
Internet Web sites
Appendix B: VBA Statements and Functions Reference
Invoking Excel functions in VBA instructions
Appendix C: VBA Error Codes
Appendix D: What's on the CD-ROM
System Requirements
Using the CD
Files and Software on the CD
Troubleshooting
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Table of Contents
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset