Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
EAN: 2147483647
Year: 2007
Pages: 319
Pages: 319
Authors: John Walkenbach
- Table of Contents
- Back Cover
- Excel 2007 Power Programming with VBA
- Preface
- What You Need to Know
- What You Need to Have
- Conventions in This Book
- What the Icons Mean
- How This Book Is Organized
- About the Companion CD-ROM
- About the Power Utility Pak Offer
- How to Use This Book
- Reach Out
- Part I: Some Essential Background
- Chapter 1: Excel 2007-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 s Files
- Starting Excel
- File Types
- Working with Template Files
- Inside an Excel File
- The QAT 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
- 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
- Some BASIC Background
- About VBA
- 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
- 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
- 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 and Closing UserForms
- 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
- 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
- An Enhanced Data Form
- A Puzzle 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
- About Charts
- Common VBA Charting Techniques
- Using VBA to Apply Chart Formatting
- 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
- VBA Charting Tricks
- Animating Charts
- Creating an Interactive Chart without VBA
- Chapter 19: Understanding Excel s Events
- Event Types That Excel Can Monitor
- What You Should Know about Events
- Workbook-Level Events
- Worksheet Events
- Chart Events
- Application Events
- UserForm Events
- 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
- Working with ADO
- 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
- VBA and 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?
- 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
- 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
- General Excel Questions
- The Visual Basic Editor
- Procedures
- Functions
- Add-ins
- User Interface
- Part VIII: Appendixes
- Appendix A: Excel Resources Online
- 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
- Index
- A
- B
- C
- D
- E
- F
- G
- H
- I
- J
- K
- L
- M
- N
- O
- P
- Q
- R
- S
- T
- U
- V
- W
- X
- Y
- Z
- List of Figures
- List of Tables
- List of Listings
- List of Sidebars
- CD Content
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
EAN: 2147483647
Year: 2007
Pages: 319
Pages: 319
Authors: John Walkenbach
- ERP System Acquisition: A Process Model and Results From an Austrian Survey
- Enterprise Application Integration: New Solutions for a Solved Problem or a Challenging Research Field?
- The Effects of an Enterprise Resource Planning System (ERP) Implementation on Job Characteristics – A Study using the Hackman and Oldham Job Characteristics Model
- Distributed Data Warehouse for Geo-spatial Services
- Development of Interactive Web Sites to Enhance Police/Community Relations