This book is a single reference that’s indispensable for Excel beginners, intermediate users, power users, and would-be power users everywhere
Fully updated for the new release, this latest edition provides comprehensive, soup-to-nuts coverage, delivering over 900 pages of Excel tips, tricks, and techniques readers won’t find anywhere else
John Walkenbach, aka "Mr. Spreadsheet," is one of the world’s leading authorities on Excel
Thoroughly updated to cover the revamped Excel interface, new file formats, enhanced interactivity with other Office applications, and upgraded collaboration features
Includes a valuable CD-ROM with templates and worksheets from the book
Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.
Table of Contents
- About the Author
- Topics Covered
- 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
I. Some Essential Background
- 1. Excel 2007: Where It Came From
2. Excel in a Nutshell
- Thinking in Terms of Objects
- Excel’s User Interface
- Customizing the Display
- Data Entry
- Formulas, Functions, and Names
- Selecting Objects
- Protection Options
- Shapes and SmartArt
- Database Access
- Internet Features
- Analysis Tools
- Macros and Programming
- File Format
- Excel’s Help System
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
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
II. Excel Application Development
5. What Is a Spreadsheet Application?
- Spreadsheet Applications
- The Developer and the End User
- Solving Problems with Excel
- Basic Spreadsheet Types
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
- 5. What Is a Spreadsheet Application?
III. Understanding Visual Basic for Applications
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
- Using the Editor tab
- Using the Editor Format tab
- Using the General tab
- Using the Docking tab
- 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
8. VBA Programming Fundamentals
- VBA Language Elements: An Overview
Variables, Data Types, and Constants
- Defining data types
- Declaring variables
- Scoping variables
- Working with constants
- Working with strings
- Working with dates
- Assignment Statements
- Object Variables
- User-Defined Data Types
- Built-in Functions
- Manipulating Objects and Collections
- Controlling Code Execution
9. Working with VBA Sub Procedures
- About Procedures
Executing Sub Procedures
- Executing a procedure with the Run Sub/UserForm command
- Executing a procedure from the Macro dialog box
- Executing a procedure with a Ctrl+shortcut key combination
- Executing a procedure from the Ribbon
- Executing a procedure from a customized shortcut menu
- Executing a procedure from another procedure
- Executing a procedure by clicking an object
- Executing a procedure when an event occurs
- Executing a procedure from the Immediate window
- Passing Arguments to Procedures
- Error-Handling Techniques
- A Realistic Example That Uses Sub Procedures
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
11. VBA Programming Examples and Techniques
Working with Ranges
- Copying a range
- Moving a range
- Copying a variably sized range
- Selecting or otherwise identifying various types of ranges
- Prompting for a cell value
- Entering a value in the next empty cell
- Pausing a macro to get a user-selected range
- Counting selected cells
- Determining the type of selected range
- Looping through a selected range efficiently
- Deleting all empty rows
- Duplicating rows a variable number of times
- Determining whether a range is contained in another range
- Determining a cell’s data type
- Reading and writing ranges
- A better way to write to a range
- Transferring one-dimensional arrays
- Transferring a range to a variant array
- Selecting cells by value
- Copying a noncontiguous range
- Working with Workbooks and Sheets
- VBA Techniques
- Some Useful Functions for Use in Your Code
Some Useful Worksheet Functions
- Returning cell formatting information
- A talking worksheet
- Displaying the date when a file was saved or printed
- Understanding object parents
- Counting cells between two values
- Counting visible cells in a range
- Determining the last non-empty cell in a column or row
- Does a string match a pattern?
- Extracting the nth element from a string
- A multifunctional function
- The SheetOffset function
- Returning the maximum value across all worksheets
- Returning an array of nonduplicated random integers
- Randomizing a range
Windows API Calls
- Determining file associations
- Determining disk drive information
- Determining default printer information
- Determining video display information
- Adding sound to your applications
- Reading from and writing to the Registry
- Working with Ranges
- 7. Introducing Visual Basic for Applications
IV. Working with UserForms
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
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
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
- About the ListBox control
- Adding items to a ListBox control
- Determining the selected item
- Determining multiple selections in a ListBox
- Multiple lists in a single ListBox
- ListBox item transfer
- Moving items in a ListBox
- Working with multicolumn ListBox controls
- Using a ListBox to select worksheet rows
- Using a ListBox to activate a sheet
- Using the MultiPage Control in a UserForm
- Using an External Control
- Animating a Label
15. Advanced UserForm Techniques
- A Modeless Dialog Box
Displaying a Progress Indicator
- Creating a standalone progress indicator
- Showing a progress indicator by using a MultiPage control
- Showing a progress indicator without using a MultiPage control
- 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
- 12. Custom Dialog Box Alternatives
V. Advanced Programming Techniques
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
- Background for Text Tools
- Project goals for Text Tools
- The Text Tools workbook
- How the Text Tools utility works
- The UserForm for the Text Tools utility
- The Module1 VBA module
The UserForm1 code module
- The UserFoRm_Initialize procedure in the UserForm1 code module
- The ComboBoxOperation_Change procedure in the UserForm1 code module
- The ApplyButton_Click procedure in the UserForm1 code module
- The CloseButton_Click procedure in the UserForm1 code module
- The HelpButton_Click procedure in the UserForm1 code module
- Making the Text Tools utility efficient
- Saving the Text Tools utility settings
- Implementing Undo
- Displaying the Help file
- Adding the RibbonX code
- Post-mortem of the project
- Understand the Text Tools utility
- More about Excel Utilities
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
18. Working with Charts
- About Charts
Common VBA Charting Techniques
- 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
- Exporting all graphics
- 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
19. Understanding Excel’s Events
- Event Types That Excel Can Monitor
- What You Should Know about Events
- Where to put event handler procedures
- Workbook-Level Events
- Worksheet Events
- Chart Events
- Application Events
- UserForm Events
- Events Not Associated with an Object
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
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
- Understanding the AddIns collection
- AddIn object properties
- Accessing an add-in as a workbook
- AddIn object events
- Optimizing the Performance of Add-Ins
- Special Problems with Add-Ins
- 16. Developing Excel Utilities with VBA
VI. Developing Applications
22. Working with the Ribbon
- Ribbon Basics
- VBA and the Ribbon
Customizing the Ribbon
- A simple RibbonX example
- More about the simple RibbonX example
- Another RibbonX example
- Ribbon controls demo
- A DynamicMenu Control Example
- More on Ribbon customization
- Creating an Old-Style Toolbar
23. Working with Shortcut Menus
- CommandBar Overview
- Using VBA to Customize Shortcut Menus
- Shortcut Menus and Events
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
25. Developing User-Oriented Applications
- What Is a User-Oriented Application?
The Loan Amortization Wizard
- Using the Loan Amortization Wizard
- The Loan Amortization Wizard workbook structure
- Potential enhancements for the Loan Amortization Wizard
- Application Development Concepts
- 22. Working with the Ribbon
VII. Other Topics
- 26. Compatibility Issues
27. Manipulating Files with VBA
Performing Common File Operations
- Using VBA file-related commands
- Using the FileSystemObject object
- Displaying Extended File Information
- Working with Text Files
- Text File Manipulation Examples
- Zipping and Unzipping Files
- Performing Common File Operations
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
29. Understanding Class Modules
- What Is a Class Module?
- Example: Creating a NumLock Class
- More about Class Modules
- Example: A CSV File Class
30. Working with Colors
- Specifying Colors
- Understanding Grayscale
- Experimenting with Colors
- Understanding Document Themes
- Working with Shape Objects
- Modifying Chart Colors
31. Frequently Asked Questions about Excel Programming
General Excel Questions
- How do I record a macro?
- How do I run a macro?
- What do I do if I don’t have a Developer tab?
- I recorded a macro and saved my workbook. When I reopened it, the macros were gone! Where did they go?
- Before saving my workbook as an XLSM file, I converted all my VBA statements to comments so I could debug the code later. When I re-opened the workbook, all my VBA code was gone.
- How do I hide the Ribbon so it doesn’t take up so much space?
- Where are my old custom toolbars?
- Can I make my old custom toolbars float?
- Where can I find examples of VBA code?
- How can I hide the status bar in Excel 2007?
- Is there a utility that will convert my Excel application into a standalone EXE file?
- Why doesn’t Ctrl+A select all the cells in my worksheet?
- Why is the Custom Views command is grayed out?
- How can I add a drop-down list to a cell so the user can choose a value from the list?
- Can I use this drop-down list method if my list is stored on a different worksheet in the workbook?
- I use Application.Calculation to set the calculation mode to manual. However, this seems to affect all workbooks and not just the active workbook.
- Why doesn’t the F4 function key repeat all my operations?
- What happened to the ability to “speak” the cell contents?
- How can I increase the number of columns in a worksheet?
- How can I increase the number of rows in a worksheet?
- I opened a workbook, and it has only 65,546 rows. What happened?
- How do I get my old workbook to use the new fonts?
- How do I get a print preview?
- When I switch to a new document template, my worksheet no longer fits on a single page.
- How do I get rid of the annoying dotted-line page break display in Normal view mode?
- Can I add that Show Page Breaks option to my QAT?
- I changed the text in a cell to use Angle Clockwise orientation (in the Home Alignment group). How do I get the orientation back to normal? There’s no Horizontal Alignment option.
- I’m trying to apply a table style to a table, but it has no visible effect. What can I do?
- How do I get Office 2007 to support PDF output?
- Can I change the color of the sheet tabs?
- Can I change the font of the sheet tabs?
- Can I change the default font and color of cell comments?
- Can I write VBA macros that play sounds?
- When I open a workbook, Excel asks whether I want to update the links. I’ve searched all my formulas and cannot find any links in this workbook. Is this a bug?
- Why does Excel crash every time I start it?
The Visual Basic Editor
- Can I use the VBA macro recorder to record all my macros?
- I turned on the macro recorder when I edited a chart, but many of the commands weren’t recorded.
- I have some macros that are general in nature. I would like to have these available all the time. What’s the best way to do this?
- I can’t find my Personal Macro Workbook. Where is it?
- I locked my VBA project with a password, and I forget what it was. Is there any way to unlock it?
- How can I write a macro to change the password of my project?
- When I insert a new module, it always starts with an Option Explicit line. What does this mean?
- Why does my VBA code appear in different colors? Can I change these colors?
- Can I delete a VBA module by using VBA code?
- I wrote a macro in Excel 2000 that adds VBA code to the VB project. When I run it in Excel 2007, I get an error message. What’s wrong?
- How can I write a macro to change the user’s macro security setting? I want to avoid the “this workbook contains macros” message when my application is opened.
- How does the UserInterfaceOnly option work when protecting a worksheet?
- How can I tell whether a workbook has a macro virus?
- I’m having trouble with the concatenation operator (&) in VBA. When I try to concatenate two strings, I get an error message.
- I can’t seem to get the VBA line continuation character (underscore) to work.
- I distributed an Excel application to many users. On some machines, my VBA error-handling procedures don’t work. Why not?
- What’s the difference between a VBA procedure and a macro?
- What’s a procedure?
- What is a variant data type?
- What’s the difference between a variant array and an array of variants?
- What’s a type-definition character?
- I would like to create a procedure that automatically changes the formatting of a cell based on the data that I enter. For example, if I enter a value greater than 0, the cell’s background color should be red. Is this possible?
- The Conditional Formatting feature is useful, but I’d like to perform other types of operations when data is entered into a cell.
- What other types of events can be monitored?
- I tried entering an event procedure (Sub Workbook_Open), but the procedure isn’t executed when the workbook is opened. What’s wrong?
- I can write an event procedure for a particular workbook, but can I write an event procedure that will work for any workbook that’s open?
- I’m very familiar with creating formulas in Excel. Does VBA use the same mathematical and logical operators?
- How can I execute a procedure that’s in a different workbook?
- I’ve used VBA to create several custom functions. I like to use these functions in my worksheet formulas, but I find it inconvenient to precede the function name with the workbook name. Is there any way around this?
- I would like a particular workbook to be loaded every time I start Excel. I would also like a macro in this workbook to execute automatically. Am I asking too much?
- I have a workbook that uses a Workbook_Open procedure. Is there a way to prevent this from executing when I open the workbook?
- Can a VBA procedure access a cell’s value in a workbook that is not open?
- How can I prevent the “save file” prompt from being displayed when I close a workbook from VBA?
- How can I set things up so that my macro runs once every hour?
- How do I prevent a macro from showing in the macro list?
- Can I save a chart as a GIF file?
- Are variables in a VBA procedure available to other VBA procedures? What if the procedure is in a different module? Or in a different workbook?
- I created a VBA function for use in worksheet formulas. However, it always returns #NAME?. What went wrong?
- I wrote a VBA function that works perfectly when I call it from another procedure, but it doesn’t work when I use it in a worksheet formula. What’s wrong?
- When I access a custom worksheet function with the Insert Function dialog box, it reads “No help available.” How can I get the Insert Function dialog box to display a description of my function?
- Can I also display help for the arguments for my custom function in the Insert Function dialog box?
- My custom worksheet function appears in the User Defined category in the Insert Function dialog box. How can I make my function appear in a different function category?
- How can I create a new function category?
- I have a custom function that will be used in a worksheet formula. If the user enters arguments that are not appropriate, how can I make the function return a true error value (#VALUE!)?
- How can I force a recalculation of formulas that use my custom worksheet function?
- Can I use Excel’s built-in worksheet functions in my VBA code?
- Is there any way to force a line break in the text of a message box?
Objects, Properties, Methods, and Events
- Is there a listing of the Excel objects I can use?
- I’m overwhelmed with all the properties and methods available. How can I find out which methods and properties are available for a particular object?
- What’s the story with collections? Is a collection an object? What are collections?
- When I refer to a worksheet in my VBA code, I get a “subscript out of range” error. I’m not using any subscripts. What gives?
- How can I prevent the user from scrolling around the worksheet?
- What’s the difference between using Select and Application.Goto?
- What’s the difference between activating a range and selecting a range?
- Is there a quick way to delete all values from a worksheet yet keep the formulas intact?
- I know how to write a VBA instruction to select a range by using a cell address, but how can I write one to select a range if I know only its row and column number?
- When I try to record the Office Exit Excel command, Excel closes down before I can see what code it generates. Is there a VBA command to quit Excel?
- How can I turn off screen updating while a macro is running?
- What’s the easiest way to create a range name in VBA?
- How can I determine whether a particular cell or range has a name?
- Can I disable the Setup and Margins buttons that are displayed in Excel’s Print Preview window?
- I have a lengthy macro, and it would be nice to display its progress in the status bar. Can I display messages in the status bar while a macro is running?
- I recorded a VBA macro that copies a range and pastes it to another area. The macro uses the Select method. Is there a more efficient way to copy and paste?
- I have not been able to find a method to sort a VBA array. Does this mean that I have to copy the values to a worksheet and then use the Range.Sort method?
- My macro works with the selected cells, but it fails if something else (like a chart) is selected. How can I make sure that a range is selected?
- How can I determine if a chart is activated?
- My VBA macro needs to count the number of rows selected by the user. Using Selection.Rows.Count doesn’t work when nonadjacent rows are selected. Is this a bug?
- I use Excel to create invoices. Can I generate a unique invoice number?
- Is there a workbook property that forces an Excel workbook to always remain visible so it won’t be hidden by another application’s window?
- Is there a VBA instruction to select the last entry in a column or row? Normally, I can use Ctrl+Shift+↓ or Ctrl+Shift+→ to do this, but how can I do it with a macro?
- How can I determine the last non-empty cell in a particular column?
- VBA references can become very lengthy, especially when I need to fully qualify an object by referencing its sheet and workbook. Can I reduce the length of these references?
- Can I declare an array if I don’t know how many elements it will have?
- Can I let the user undo my macro?
- Can I pause a macro so the user can enter data into a certain cell?
- VBA has an InputBox function, but there’s also an InputBox method for the Application object. Are these the same?
- I’m trying to write a VBA instruction that creates a formula. To do so, I need to insert a quote character (“) within quoted text. How can I do that?
- I created an array, but the first element in that array is being treated as the second element. What’s wrong?
- I would like my VBA code to run as quickly as possible. Any suggestions?
- My macro needs to get just a few pieces of information from the user, and a UserForm seems like overkill. Are there any alternatives?
- I have 12 CommandButtons on a UserForm. How can I assign a single macro to be executed when any of the buttons is clicked?
- How can I display a chart in a UserForm?
- How can I remove the “X” from the title bar of my UserForm? I don’t want the user to click that button to close the form.
- I created a UserForm with controls that are linked to cells on the worksheet with the ControlSource property. Is this the best way to do this?
- Can I create a control array for a UserForm? It’s possible with Visual Basic, but I can’t figure out how to do it with Excel VBA.
- Is there any difference between hiding a UserForm and unloading a UserForm?
- How can I make my UserForm stay open while I do other things?
- Excel 97 gives me a compile error when I write UserForm1.Show vbModeless. How can I make the form modeless in Excel 2000 and later while allowing it to remain modal in Excel 97?
- I need to display a progress indicator like those you see when you’re installing software while a lengthy process is being executed. How can I do this?
- How can I use Excel’s shapes on my UserForm?
- How can I generate a list of files and directories into my UserForm so the user can select a file from the list?
- I need to concatenate strings and display them in a ListBox control. But when I do so, they aren’t aligned properly. How can I get them to display equal spacing between strings?
- Is there an easy way to fill a ListBox or ComboBox control with items?
- Can I display a built-in Excel dialog box from VBA?
- I tried the technique described in the preceding question and received an error message. Why is that?
- Every time I create a UserForm, I go through the steps of adding an OK button and a Cancel button. Is there a way to get these controls to appear automatically?
- Can I create a UserForm without a title bar?
- When I click a button on my UserForm, nothing happens. What am I doing wrong?
- Can I create a UserForm whose size is always the same, regardless of the video display resolution?
- Can I create a UserForm box that lets the user select a range in a worksheet by pointing?
- Can I change the startup position of a UserForm?
- Can I make a UserForm that’s resizable by the user?
- Where can I get Excel add-ins?
- How do I install an add-in?
- When I install my add-in from Excel’s Add-Ins dialog box, it shows up without a name or description. How can I give my add-in a description?
- I have several add-ins that I no longer use, but I can’t figure out how to remove them from the Add-Ins Available list in the Add-Ins dialog box. What’s the story?
- How do I create an add-in?
- I try to create an add-in, but the Save as Type dropdown box doesn’t provide Add-in as an option.
- Should I convert all my essential workbooks to add-ins?
- Do I need to keep two copies of my workbook: the XLSM version and the XLAM version?
- How do I modify an add-in after it has been created?
- What’s the difference between an XLSM file and an XLAM file created from an XLSM file? Is the XLAM version compiled? Does it run faster?
- How do I protect the code in my add-in from being viewed by others?
- Are my add-ins safe? In other words, if I distribute an XLAM file, can I be assured that no one else will be able to view my code?
- How do I use VBA to add a simple button to the Ribbon?
- What are my options for modifying the user interface to make it easy for a user to run my macros?
- How do I add a macro to the Quick Access Toolbar?
- I added my macro to the QAT, but clicking the icon generates an error.
- How do I use VBA to activate a particular tab on the Ribbon?
- How can I disable all the right-click shortcut menus?
- General Excel Questions
A. Excel Resources Online
- The Excel Help System
- Microsoft Technical Support
- Internet Newsgroups
- Internet Web Sites
- B. VBA Statements and Functions Reference
- C. VBA Error Codes
D. What’s on the CD-ROM
- System Requirements
- Using the CD
Files and Software on the CD
- eBook version of Excel 2007 Power Programming with VBA
- Sample files for Excel 2007 Power Programming with VBA
- A. Excel Resources Online
- Title: Excel® 2007 Power Programming with VBA
- Release date: April 2007
- Publisher(s): Wiley
- ISBN: 9780470044018