Excel® 2007 Power Programming with VBA

Book description

  • 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

  1. Copyright
  2. About the Author
  3. Preface
    1. Topics Covered
    2. What You Need to Know
    3. What You Need to Have
    4. Conventions in This Book
      1. Excel commands
      2. VBA editor commands
      3. Keyboard conventions
        1. Input
        2. VBA Code
        3. Functions, Filenames, and Named Ranges
      4. Mouse conventions
    5. What the Icons Mean
    6. How This Book Is Organized
      1. Part I: Some Essential Background
      2. Part II: Excel Application Development
      3. Part III: Understanding Visual Basic for Applications
      4. Part IV: Working with UserForms
      5. Part V: Advanced Programming Techniques
      6. Part VI: Developing Applications
      7. Part VII: Other Topics
      8. Part VIII: Appendixes
    7. About the Companion CD-ROM
    8. About the Power Utility Pak Offer
    9. How to Use This Book
    10. Reach Out
  4. I. Some Essential Background
    1. 1. Excel 2007: Where It Came From
      1. A Brief History of Spreadsheets
        1. It all started with VisiCalc
        2. Lotus 1-2-3
        3. Quattro Pro
        4. Microsoft Excel
      2. Why Excel Is Great for Developers
      3. Excel’s Role in Microsoft’s Strategy
    2. 2. Excel in a Nutshell
      1. Thinking in Terms of Objects
      2. Workbooks
        1. Worksheets
        2. Chart sheets
        3. XLM macro sheets
        4. Excel 5/95 dialog sheets
      3. Excel’s User Interface
        1. Introducing the Ribbon
          1. Contextual tabs
          2. Types of commands on the Ribbon
          3. The Quick Access Toolbar
          4. Accessing the Ribbon BY using your keyboard
        2. Shortcut menus
        3. Dialog boxes
        4. Keyboard shortcuts
        5. Smart Tags
        6. Task pane
      4. Customizing the Display
      5. Data Entry
      6. Formulas, Functions, and Names
      7. Selecting Objects
      8. Formatting
        1. Numeric formatting
        2. Stylistic formatting
      9. Protection Options
        1. Protecting formulas from being overwritten
        2. Protecting a workbook’s structure
        3. Applying password protection to a workbook
        4. Protecting VBA code with a password
      10. Charts
      11. Shapes and SmartArt
      12. Database Access
        1. Worksheet databases
        2. External databases
      13. Internet Features
      14. Analysis Tools
        1. Outlines
        2. Analysis ToolPak
        3. Pivot tables
        4. Solver
        5. XML features
      15. Add-Ins
      16. Macros and Programming
      17. File Format
      18. Excel’s Help System
    3. 3. Formula Tricks and Techniques
      1. About Formulas
      2. Calculating Formulas
      3. Cell and Range References
        1. Why use references that aren’t relative?
        2. About R1C1 notation
        3. Referencing other sheets or workbooks
      4. Using Names
        1. Naming cells and ranges
        2. Applying names to existing references
        3. Intersecting names
        4. Naming columns and rows
        5. Scoping names
        6. Naming constants
        7. Naming formulas
        8. Naming objects
      5. Formula Errors
      6. Array Formulas
        1. An array formula example
        2. An array formula calendar
        3. Array formula pros and cons
      7. Counting and Summing Techniques
        1. Counting formula examples
        2. Summing formula examples
        3. Other counting tools
      8. Working with Dates and Times
        1. Entering dates and times
        2. Using pre-1900 dates
      9. Creating Megaformulas
    4. 4. Understanding Excel’s Files
      1. Starting Excel
      2. File Types
        1. Excel file formats
        2. Text file formats
        3. Database file formats
        4. Other file formats
      3. Working with Template Files
        1. Viewing templates
        2. Creating templates
          1. Using the workbook template to change workbook defaults
          2. Using the worksheet template to change worksheet defaults
        3. Creating workbook templates
      4. Inside an Excel File
        1. Dissecting a file
        2. Why is the file format important?
      5. The QAT File
      6. The XLB File
      7. Add-In Files
      8. Excel Settings in the Registry
        1. About the Registry
        2. Excel’s settings
  5. II. Excel Application Development
    1. 5. What Is a Spreadsheet Application?
      1. Spreadsheet Applications
      2. The Developer and the End User
        1. Who are developers? What do they do?
        2. Classifying spreadsheet users
        3. The audience for spreadsheet applications
      3. Solving Problems with Excel
      4. Basic Spreadsheet Types
        1. Quick-and-dirty spreadsheets
        2. For-your-eyes-only spreadsheets
        3. Single-user applications
        4. Spaghetti applications
        5. Utility applications
        6. Add-ins that contain worksheet functions
        7. Single-block budgets
        8. What-if models
        9. Data storage and access spreadsheets
        10. Database front ends
        11. Turnkey applications
    2. 6. Essentials of Spreadsheet Application Development
      1. Determining User Needs
      2. Planning an Application That Meets User Needs
      3. Determining the Most Appropriate User Interface
        1. Customizing the Ribbon
        2. Customizing shortcut menus
        3. Creating shortcut keys
        4. Creating custom dialog boxes
        5. Using ActiveX controls on a worksheet
        6. Executing the development effort
      4. Concerning Yourself with the End User
        1. Testing the application
        2. Making the application bulletproof
        3. Making the application aesthetically appealing and intuitive
        4. Creating a user Help system
        5. Documenting the development effort
        6. Distributing the application to the user
        7. Updating the application when necessary
      5. Other Development Issues
        1. The user’s installed version of Excel
        2. Language issues
        3. System speed
        4. Video modes
  6. III. Understanding Visual Basic for Applications
    1. 7. Introducing Visual Basic for Applications
      1. Some BASIC Background
      2. About VBA
        1. Object models
        2. VBA versus XLM
      3. The Basics of VBA
      4. Introducing the Visual Basic Editor
        1. Displaying Excel’s Developer tab
        2. Activating the VBE
        3. The VBE windows
          1. VBE Menu Bar
          2. VBE Toolbars
          3. Project Explorer Window
          4. Code Window
          5. Immediate Window
      5. Working with the Project Explorer
        1. Adding a new VBA module
        2. Removing a VBA module
        3. Exporting and importing objects
      6. Working with Code Windows
        1. Minimizing and maximizing windows
        2. Storing VBA code
        3. Entering VBA code
          1. Entering Code Manually
          2. Using the Macro Recorder
          3. Copying VBA code
      7. Customizing the VBE Environment
        1. Using the Editor tab
          1. Auto Syntax Check option
          2. Require Variable Declaration option
          3. Auto List Members option
          4. Auto Quick Info option
          5. Auto Data Tips option
          6. Auto Indent option
          7. Drag-and-Drop Text Editing option
          8. Default to Full Module View option
          9. Procedure Separator option
        2. Using the Editor Format tab
          1. Code Colors option
          2. Font option
          3. Size setting
          4. Margin Indicator Bar option
        3. Using the General tab
        4. Using the Docking tab
      8. The Macro Recorder
        1. What the macro recorder actually records
        2. Relative or absolute?
        3. Recording options
          1. Macro name
          2. Shortcut key
          3. Store macro in
        4. Cleaning up recorded macros
      9. About Objects and Collections
        1. The object hierarchy
        2. About collections
        3. Referring to objects
      10. Properties and Methods
        1. Object properties
        2. Object methods
      11. The Comment Object: A Case Study
        1. Viewing Help for the Comment object
        2. Properties of a Comment object
        3. Methods of a Comment object
        4. The Comments collection
        5. About the Comment property
        6. Objects within a Comment object
        7. Determining whether a cell has a comment
        8. Adding a new Comment object
      12. Some Useful Application Properties
      13. Working with Range Objects
        1. The Range property
        2. The Cells property
        3. The Offset property
      14. Things to Know about Objects
        1. Essential concepts to remember
        2. Learning more about objects and properties
          1. Read the rest of the book
          2. Record your actions
          3. Use the help system
          4. Use the Object Browser
          5. Experiment with the Immediate Window
    2. 8. VBA Programming Fundamentals
      1. VBA Language Elements: An Overview
      2. Comments
      3. Variables, Data Types, and Constants
        1. Defining data types
        2. Declaring variables
          1. Determining a Data Type
          2. Forcing yourself to declare all variables
        3. Scoping variables
          1. Local variables
          2. Module-wide variables
          3. Public variables
          4. Static variables
        4. Working with constants
          1. Declaring constants
          2. Using predefined constants
        5. Working with strings
        6. Working with dates
      4. Assignment Statements
      5. Arrays
        1. Declaring arrays
        2. Declaring multidimensional arrays
        3. Declaring dynamic arrays
      6. Object Variables
      7. User-Defined Data Types
      8. Built-in Functions
      9. Manipulating Objects and Collections
        1. With-End With constructs
        2. For Each-Next constructs
      10. Controlling Code Execution
        1. GoTo statements
        2. If-Then constructs
        3. Select Case constructs
        4. Looping blocks of instructions
          1. For-Next loops
          2. Do While loops
          3. Do Until loops
    3. 9. Working with VBA Sub Procedures
      1. About Procedures
        1. Declaring a Sub procedure
        2. Scoping a procedure
          1. Public Procedures
          2. Private Procedures
      2. Executing Sub Procedures
        1. Executing a procedure with the Run Sub/UserForm command
        2. Executing a procedure from the Macro dialog box
        3. Executing a procedure with a Ctrl+shortcut key combination
        4. Executing a procedure from the Ribbon
        5. Executing a procedure from a customized shortcut menu
        6. Executing a procedure from another procedure
          1. Calling a procedure in a different module
          2. Calling a procedure in a different workbook
        7. Executing a procedure by clicking an object
        8. Executing a procedure when an event occurs
        9. Executing a procedure from the Immediate window
      3. Passing Arguments to Procedures
      4. Error-Handling Techniques
        1. Trapping errors
        2. Error-handling examples
      5. A Realistic Example That Uses Sub Procedures
        1. The goal
        2. Project requirements
        3. What you know
        4. The approach
        5. What you need to know
        6. Some preliminary recording
        7. Initial setup
        8. Code writing
        9. Writing the Sort procedure
        10. More testing
        11. Fixing the problems
        12. Utility availability
        13. Evaluating the project
    4. 10. Creating Function Procedures
      1. Sub Procedures versus Function Procedures
      2. Why Create Custom Functions?
      3. An Introductory Function Example
        1. A custom function
        2. Using the function in a worksheet
        3. Using the function in a VBA procedure
        4. Analyzing the custom function
      4. Function Procedures
        1. Declaring a function
        2. A function’s scope
        3. Executing function procedures
          1. From a Procedure
          2. In a worksheet formula
          3. From the VBE Immediate Window
      5. Function Arguments
      6. Function Examples
        1. Functions with no argument
        2. A function with one argument
        3. A function with two arguments
        4. A function with an array argument
        5. A function with optional arguments
        6. A function that returns a VBA array
        7. A function that returns an error value
        8. A function with an indefinite number of arguments
      7. Emulating Excel’s SUM Function
      8. Debugging Functions
      9. Dealing with the Insert Function Dialog Box
        1. Specifying a function category
        2. Adding a function description
          1. Describing your function in the Macro dialog box
          2. Describing your function with VBA Code
      10. Using Add-ins to Store Custom Functions
      11. Using the Windows API
        1. Windows API examples
        2. Determining the Windows directory
        3. Detecting the Shift key
        4. Learning more about API functions
    5. 11. VBA Programming Examples and Techniques
      1. Working with Ranges
        1. Copying a range
        2. Moving a range
        3. Copying a variably sized range
        4. Selecting or otherwise identifying various types of ranges
        5. Prompting for a cell value
        6. Entering a value in the next empty cell
        7. Pausing a macro to get a user-selected range
        8. Counting selected cells
        9. Determining the type of selected range
        10. Looping through a selected range efficiently
        11. Deleting all empty rows
        12. Duplicating rows a variable number of times
        13. Determining whether a range is contained in another range
        14. Determining a cell’s data type
        15. Reading and writing ranges
        16. A better way to write to a range
        17. Transferring one-dimensional arrays
        18. Transferring a range to a variant array
        19. Selecting cells by value
        20. Copying a noncontiguous range
      2. Working with Workbooks and Sheets
        1. Saving all workbooks
        2. Saving and closing all workbooks
        3. Hiding all but the selection
        4. Synchronizing worksheets
      3. VBA Techniques
        1. Toggling a Boolean property
        2. Determining the number of printed pages
        3. Displaying the date and time
        4. Getting a list of fonts
        5. Sorting an array
        6. Processing a series of files
      4. Some Useful Functions for Use in Your Code
        1. The FileExists function
        2. The FileNameOnly function
        3. The RangeNameExists function
        4. The SheetExists function
        5. The WorkbookIsOpen function
        6. Retrieving a value from a closed workbook
      5. Some Useful Worksheet Functions
        1. Returning cell formatting information
        2. A talking worksheet
        3. Displaying the date when a file was saved or printed
        4. Understanding object parents
        5. Counting cells between two values
        6. Counting visible cells in a range
        7. Determining the last non-empty cell in a column or row
        8. Does a string match a pattern?
        9. Extracting the nth element from a string
        10. A multifunctional function
        11. The SheetOffset function
        12. Returning the maximum value across all worksheets
        13. Returning an array of nonduplicated random integers
        14. Randomizing a range
      6. Windows API Calls
        1. Determining file associations
        2. Determining disk drive information
        3. Determining default printer information
        4. Determining video display information
        5. Adding sound to your applications
          1. Playing a WAV file
          2. Playing a MIDI file
          3. Playing sound from a worksheet function
        6. Reading from and writing to the Registry
          1. Reading from the Registry
          2. Writing to the Registry
  7. IV. Working with UserForms
    1. 12. Custom Dialog Box Alternatives
      1. Before You Create That UserForm . . .
      2. Using an Input Box
        1. The VBA InputBox function
        2. The Excel InputBox method
      3. The VBA MsgBox Function
      4. The Excel GetOpenFilename Method
      5. The Excel GetSaveAsFilename Method
      6. Prompting for a Directory
        1. Using a Windows API function to select a directory
        2. Using the FileDialog object to select a directory
      7. Displaying Excel’s Built-In Dialog Boxes
        1. About the Dialogs collection
        2. Executing Ribbon commands
      8. Displaying a Data Form
        1. Making the data form accessible
        2. Displaying a data form by using VBA
    2. 13. Introducing UserForms
      1. How Excel Handles Custom Dialog Boxes
      2. Inserting a New UserForm
      3. Adding Controls to a UserForm
      4. Toolbox Controls
        1. CheckBox
        2. ComboBox
        3. CommandButton
        4. Frame
        5. Image
        6. Label
        7. ListBox
        8. MultiPage
        9. OptionButton
        10. RefEdit
        11. ScrollBar
        12. SpinButton
        13. TabStrip
        14. TextBox
        15. ToggleButton
      5. Adjusting UserForm Controls
      6. Adjusting a Control’s Properties
        1. Using the Properties window
        2. Common properties
        3. Learning more about properties
        4. Accommodating keyboard users
          1. Changing the tab order of controls
          2. Setting hot keys
      7. Displaying and Closing UserForms
        1. Displaying a UserForm
          1. Displaying a modeless UserForm
          2. Displaying a UserForm Based on a Variable
          3. Loading a UserForm
        2. Closing a UserForm
        3. About event handler procedures
      8. Creating a UserForm: An Example
        1. Creating the UserForm
        2. Writing code to display the dialog box
        3. Testing the dialog box
        4. Adding event handler procedures
        5. Validating the data
        6. The finished dialog box
      9. Understanding UserForm Events
        1. Learning about events
        2. UserForm events
        3. SpinButton events
          1. Mouse-initiated events
          2. Keyboard-initiated events
          3. What about changes via code?
        4. Pairing a SpinButton with a TextBox
      10. Referencing UserForm Controls
      11. Customizing the Toolbox
        1. Changing icons or tip text
        2. Adding new pages
        3. Customizing or combining controls
        4. Adding new ActiveX controls
      12. Creating UserForm Templates
      13. A UserForm Checklist
    3. 14. UserForm Examples
      1. Creating a UserForm “Menu”
        1. Using CommandButtons in a UserForm
        2. Using a ListBox in a UserForm
      2. Selecting Ranges from a UserForm
      3. Creating a Splash Screen
      4. Disabling a UserForm’s Close Button
      5. Changing a UserForm’s Size
      6. Zooming and Scrolling a Sheet from a UserForm
      7. ListBox Techniques
        1. About the ListBox control
        2. Adding items to a ListBox control
          1. Adding items to a ListBox at design time
          2. Adding items to a ListBox at runtime
          3. Adding only unique items to a ListBox
        3. Determining the selected item
        4. Determining multiple selections in a ListBox
        5. Multiple lists in a single ListBox
        6. ListBox item transfer
        7. Moving items in a ListBox
        8. Working with multicolumn ListBox controls
        9. Using a ListBox to select worksheet rows
        10. Using a ListBox to activate a sheet
      8. Using the MultiPage Control in a UserForm
      9. Using an External Control
      10. Animating a Label
    4. 15. Advanced UserForm Techniques
      1. A Modeless Dialog Box
      2. Displaying a Progress Indicator
        1. Creating a standalone progress indicator
          1. Building the standalone progress indicator UserForm
          2. Creating the event handler procedures for the standalone progress indicator
          3. Creating the start-up procedure for a standalone progress indicator
          4. How the standalone progress indicator works
        2. Showing a progress indicator by using a MultiPage control
          1. Modifying your UserForm for a progress indicator with a Multipage control
          2. Inserting the UpdateProgress procedure for a Progress indicator with a Multipage control
          3. Modifying your procedure for a progress indicator with a Multipage control
          4. How a progress indicator with a Multipage control works
        3. Showing a progress indicator without using a MultiPage control
      3. Creating Wizards
        1. Setting up the MultiPage control for the wizard
        2. Adding the buttons to the wizard UserForm
        3. Programming the wizard buttons
        4. Programming dependencies in a wizard
        5. Performing the task with the wizard
      4. Emulating the MsgBox Function
        1. MsgBox emulation: MyMsgBox code
        2. How the MyMsgBox function works
        3. Using the MyMsgBox function in the MsgBox emulation
      5. A UserForm with Movable Controls
      6. A UserForm with No Title Bar
      7. Simulating a Toolbar with a UserForm
      8. A Resizable UserForm
      9. Handling Multiple UserForm Controls with One Event Handler
      10. Selecting a Color in a UserForm
      11. Displaying a Chart in a UserForm
        1. General steps to display a chart in a userform
        2. Saving a chart as a GIF file
        3. Changing the Image control Picture property
      12. An Enhanced Data Form
        1. About the Enhanced Data Form
        2. Installing the Enhanced Data Form add-in
      13. A Puzzle on a UserForm
  8. V. Advanced Programming Techniques
    1. 16. Developing Excel Utilities with VBA
      1. About Excel Utilities
      2. Using VBA to Develop Utilities
      3. What Makes a Good Utility?
      4. Text Tools: The Anatomy of a Utility
        1. Background for Text Tools
        2. Project goals for Text Tools
        3. The Text Tools workbook
        4. How the Text Tools utility works
        5. The UserForm for the Text Tools utility
        6. The Module1 VBA module
          1. Declarations in the Module1 VBA Module
          2. The ShowTextToolsDialog procedure in the Module1 VBA module
          3. The UndoTextTools procedure in the Module1 VBA module
        7. The UserForm1 code module
          1. The UserFoRm_Initialize procedure in the UserForm1 code module
          2. The ComboBoxOperation_Change procedure in the UserForm1 code module
          3. The ApplyButton_Click procedure in the UserForm1 code module
          4. The CloseButton_Click procedure in the UserForm1 code module
          5. The HelpButton_Click procedure in the UserForm1 code module
        8. Making the Text Tools utility efficient
        9. Saving the Text Tools utility settings
        10. Implementing Undo
        11. Displaying the Help file
        12. Adding the RibbonX code
        13. Post-mortem of the project
        14. Understand the Text Tools utility
      5. More about Excel Utilities
    2. 17. Working with Pivot Tables
      1. An Introductory Pivot Table Example
        1. Creating a pivot table
        2. Examining the recorded code for the pivot table
        3. Cleaning up the recorded pivot table code
      2. Creating a More Complex Pivot Table
        1. Data for a more complex pivot table
        2. The code that created the pivot table
        3. How the more complex pivot table works
      3. Creating Multiple Pivot Tables
      4. Creating a Reverse Pivot Table
    3. 18. Working with Charts
      1. About Charts
        1. Chart locations
        2. The macro recorder and charts
        3. The Chart object model
      2. Common VBA Charting Techniques
        1. Creating an embedded chart
        2. Creating a chart on a chart sheet
        3. Using VBA to activate a chart
        4. Moving a chart
        5. Using VBA to deactivate a chart
        6. Determining whether a chart is activated
        7. Deleting from the ChartObjects or Charts collection
        8. Looping through all charts
        9. Sizing and aligning ChartObjects
        10. Exporting a chart
        11. Exporting all graphics
      3. Using VBA to Apply Chart Formatting
        1. Formatting a chart
        2. More chart formatting examples
          1. Adding a shadow
          2. Adding A Bevel
      4. Changing the Data Used in a Chart
        1. Changing chart data based on the active cell
        2. Using VBA to determine the ranges used in a chart
      5. Using VBA to Display Arbitrary Data Labels on a Chart
      6. Displaying a Chart in a UserForm
      7. Understanding Chart Events
        1. An example of using Chart events
        2. Enabling events for an embedded chart
          1. Create a class module
          2. Declare a public Chart object
          3. Connect the declared object with your chart
          4. Write event handler procedures for the chart class
        3. Example: Using Chart events with an embedded chart
      8. VBA Charting Tricks
        1. Printing embedded charts on a full page
        2. Displaying a slide show
        3. Hiding series by hiding columns
        4. Creating unlinked charts
        5. Displaying text with the MouseOver event
      9. Animating Charts
        1. Scrolling a chart
        2. Creating a hypocycloid chart
        3. Creating a “clock” chart
      10. Creating an Interactive Chart without VBA
        1. Getting the data to create an interactive chart
        2. Creating the Option Button controls for an interactive chart
        3. Creating the city lists for the interactive chart
        4. Creating the interactive chart data range
        5. Creating the interactive chart
    4. 19. Understanding Excel’s Events
      1. Event Types That Excel Can Monitor
      2. What You Should Know about Events
        1. Understanding event sequences
      3. Where to put event handler procedures
        1. Disabling events
        2. Entering event handler code
        3. Event handler procedures that use arguments
      4. Workbook-Level Events
        1. The Open event
        2. The Activate event
        3. The SheetActivate event
        4. The NewSheet event
        5. The BeforeSave event
        6. The Deactivate event
        7. The BeforePrint event
          1. Updating a header or footer
          2. Hiding columns before printing
        8. The BeforeClose event
      5. Worksheet Events
        1. The Change event
        2. Monitoring a specific range for changes
          1. Monitoring a range to make formulas bold
          2. Monitoring a range to validate data entry
        3. The SelectionChange event
        4. The BeforeDoubleClick event
        5. The BeforeRightClick event
      6. Chart Events
      7. Application Events
        1. Enabling Application-level events
        2. Determining when a workbook is opened
        3. Monitoring Application-level events
      8. UserForm Events
      9. Events Not Associated with an Object
        1. The OnTime event
        2. The OnKey event
          1. An OnKey event example
          2. Key Codes
          3. Disabling shortcut menus
    5. 20. Interacting with Other Applications
      1. Starting an Application from Excel
        1. Using the VBA Shell function
        2. Using the Windows ShellExecute API function
      2. Activating an Application with Excel
        1. Using AppActivate
        2. Activating a Microsoft Office application
      3. Running Control Panel Dialog Boxes
      4. Using Automation in Excel
        1. Working with foreign objects using automation
        2. Early versus late binding
          1. Early Binding
          2. Late binding
        3. A simple example of late binding
        4. Controlling Word from Excel
        5. Controlling Excel from another application
      5. Sending Personalized E-Mail via Outlook
      6. Sending E-Mail Attachments from Excel
      7. Using SendKeys
      8. Working with ADO
    6. 21. Creating and Using Add-Ins
      1. What Is an Add-In?
        1. Comparing an add-in with a standard workbook
        2. Why create add-ins?
      2. Understanding Excel’s Add-In Manager
      3. Creating an Add-in
      4. An Add-In Example
        1. Setting up the workbook for the example add-in
        2. Adding descriptive information for the example add-in
        3. Creating an add-in
        4. Installing an add-in
        5. Testing the add-in
        6. Distributing an add-in
        7. Modifying an add-in
      5. Comparing XLAM and XLSM Files
        1. XLSM and XLAM file size and structure
        2. XLAM file VBA collection membership
        3. Visibility of XLSM and XLAM files
        4. Worksheets and chart sheets in XLSM and XLAM files
        5. Accessing VBA procedures in an add-in
      6. Manipulating Add-Ins with VBA
        1. Understanding the AddIns collection
          1. Adding an item to the AddIns collection
          2. Removing an item from the AddIns collection
        2. AddIn object properties
          1. The Name property of an addin object
          2. The Path property of an addin object
          3. The FullName property of an addin object
          4. The Title property of an addin object
          5. The Comments property of an addin object
          6. The Installed property of an addin object
        3. Accessing an add-in as a workbook
        4. AddIn object events
      7. Optimizing the Performance of Add-Ins
      8. Special Problems with Add-Ins
        1. Ensuring that an add-in is installed
        2. Referencing other files from an add-in
        3. Detecting the proper Excel version for your add-in
  9. VI. Developing Applications
    1. 22. Working with the Ribbon
      1. Ribbon Basics
        1. Ribbon tabs
      2. VBA and the Ribbon
        1. Accessing a Ribbon control
        2. Working with the Ribbon
        3. Activating a tab
      3. Customizing the Ribbon
        1. A simple RibbonX example
        2. More about the simple RibbonX example
          1. VBA callback procedures
          2. The .rels file
          3. The RibbonX code
        3. Another RibbonX example
          1. The RibbonX code
          2. The VBA Code
        4. Ribbon controls demo
          1. Creating a new tab
          2. Creating a Ribbon group
          3. Creating controls
        5. A DynamicMenu Control Example
        6. More on Ribbon customization
      4. Creating an Old-Style Toolbar
        1. Limitations of old-style toolbars in Excel 2007
        2. Code to create a toolbar
    2. 23. Working with Shortcut Menus
      1. CommandBar Overview
        1. CommandBar types
        2. Listing shortcut menus
        3. Referring to CommandBars
        4. Referring to controls in a CommandBar
        5. Properties of CommandBar controls
        6. Displaying all shortcut menu items
      2. Using VBA to Customize Shortcut Menus
        1. Resetting a shortcut menu
        2. Disabling a Shortcut Menu
        3. Disabling shortcut menu items
        4. Adding a new item to the Cell shortcut menu
        5. Adding a submenu to a shortcut menu
      3. Shortcut Menus and Events
        1. Adding and deleting menus automatically
        2. Disabling or hiding shortcut menu items
        3. Creating a context-sensitive shortcut menu
    3. 24. Providing Help for Your Applications
      1. Help for Your Excel Applications
      2. Help Systems That Use Excel Components
        1. Using cell comments for help
        2. Using a text box for help
        3. Using a worksheet to display help text
        4. Displaying help in a UserForm
          1. Using Label controls to display help text
          2. Using a scrolling Label to display help text
          3. Using a ComboBox control to select a Help topic
      3. Displaying Help in a Web Browser
        1. Using HTML files
        2. Using an MHTML file
      4. Using the HTML Help System
        1. Using the Help method to display HTML Help
        2. Using an API function to display HTML help
      5. Associating a Help File with Your Application
        1. Associating a help topic with a VBA function
    4. 25. Developing User-Oriented Applications
      1. What Is a User-Oriented Application?
      2. The Loan Amortization Wizard
        1. Using the Loan Amortization Wizard
        2. The Loan Amortization Wizard workbook structure
          1. How the Loan Amortization Wizard works
          2. Modifying the User Interface
          3. Displaying an Initial Message
          4. Initializing FormMain for the wizard
          5. Processing events while the UserForm is displayed
          6. Displaying help in the wizard
          7. Creating the new worksheet
          8. Saving and retrieving default settings
        3. Potential enhancements for the Loan Amortization Wizard
      3. Application Development Concepts
  10. VII. Other Topics
    1. 26. Compatibility Issues
      1. What Is Compatibility?
      2. Types of Compatibility Problems
      3. Avoid Using New Features
      4. But Will It Work on a Mac?
      5. Creating an International Application
        1. Multilanguage applications
        2. VBA language considerations
        3. Using local properties
        4. Identifying system settings
        5. Date and time settings
    2. 27. Manipulating Files with VBA
      1. Performing Common File Operations
        1. Using VBA file-related commands
          1. A VBA function to determine whether a file exists
          2. A VBA function to determine whether a path exists
          3. A VBA procedure to display a list of files in a directory
          4. A recursive VBA procedure to display a list of files in nested directories
        2. Using the FileSystemObject object
          1. Using FileSystemObject to determine whether a file exists
          2. Using FileSystemObject to determine whether a path exists
          3. Using FileSystemObject to list information about all available disk drives
      2. Displaying Extended File Information
      3. Working with Text Files
        1. Opening a text file
        2. Reading a text file
        3. Writing a text file
        4. Getting a file number
        5. Determining or setting the file position
        6. Statements for reading and writing
      4. Text File Manipulation Examples
        1. Importing data in a text file
        2. Exporting a range to a text file
        3. Importing a text file to a range
        4. Logging Excel usage
        5. Filtering a text file
        6. Exporting a range to HTML format
        7. Exporting a range to an XML file
      5. Zipping and Unzipping Files
        1. Zipping files
        2. Unzipping a File
    3. 28. Manipulating Visual Basic Components
      1. Introducing the IDE
      2. The IDE Object Model
        1. The VBProjects collection
          1. The VBComponents collection
          2. The References collection
      3. Displaying All Components in a VBA Project
      4. Listing All VBA Procedures in a Workbook
      5. Replacing a Module with an Updated Version
      6. Using VBA to Write VBA Code
      7. Adding Controls to a UserForm at Design Time
        1. Design-time versus runtime UserForm manipulations
        2. Adding 100 CommandButtons at design time
      8. Creating UserForms Programmatically
        1. A simple runtime UserForm example
        2. A useful (but not so simple) dynamic UserForm example
          1. Using the GetOption function
          2. How GetOption works
          3. GetOption Event-Handler Code
    4. 29. Understanding Class Modules
      1. What Is a Class Module?
      2. Example: Creating a NumLock Class
        1. Inserting a class module
        2. Adding VBA code to the class module
        3. Using the NumLockClass class
      3. More about Class Modules
        1. Naming the object class
        2. Programming properties of objects
        3. Programming methods for objects
        4. Class module events
      4. Example: A CSV File Class
        1. Class module–level variables for the CSVFileClass
        2. Property procedures for the CSVFileClass
        3. Method procedures for the CSVFileClass
          1. The Export Procedure
          2. The Import procedure
        4. Using the CSVFileClass object
    5. 30. Working with Colors
      1. Specifying Colors
        1. The RGB color system
        2. The HSL color system
        3. Converting colors
      2. Understanding Grayscale
        1. Converting colors to gray
        2. Viewing charts as grayscale
      3. Experimenting with Colors
      4. Understanding Document Themes
        1. About document themes
        2. Understanding document theme colors
        3. Displaying all theme colors
      5. Working with Shape Objects
        1. A shape’s background color
        2. Using other fill types with a shape
        3. Learning more about shapes
      6. Modifying Chart Colors
    6. 31. Frequently Asked Questions about Excel Programming
      1. General Excel Questions
        1. How do I record a macro?
        2. How do I run a macro?
        3. What do I do if I don’t have a Developer tab?
        4. I recorded a macro and saved my workbook. When I reopened it, the macros were gone! Where did they go?
        5. 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.
        6. How do I hide the Ribbon so it doesn’t take up so much space?
        7. Where are my old custom toolbars?
        8. Can I make my old custom toolbars float?
        9. Where can I find examples of VBA code?
        10. How can I hide the status bar in Excel 2007?
        11. Is there a utility that will convert my Excel application into a standalone EXE file?
        12. Why doesn’t Ctrl+A select all the cells in my worksheet?
        13. Why is the Custom Views command is grayed out?
        14. How can I add a drop-down list to a cell so the user can choose a value from the list?
        15. Can I use this drop-down list method if my list is stored on a different worksheet in the workbook?
        16. I use Application.Calculation to set the calculation mode to manual. However, this seems to affect all workbooks and not just the active workbook.
        17. Why doesn’t the F4 function key repeat all my operations?
        18. What happened to the ability to “speak” the cell contents?
        19. How can I increase the number of columns in a worksheet?
        20. How can I increase the number of rows in a worksheet?
        21. I opened a workbook, and it has only 65,546 rows. What happened?
        22. How do I get my old workbook to use the new fonts?
        23. How do I get a print preview?
        24. When I switch to a new document template, my worksheet no longer fits on a single page.
        25. How do I get rid of the annoying dotted-line page break display in Normal view mode?
        26. Can I add that Show Page Breaks option to my QAT?
        27. 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.
        28. I’m trying to apply a table style to a table, but it has no visible effect. What can I do?
        29. How do I get Office 2007 to support PDF output?
        30. Can I change the color of the sheet tabs?
        31. Can I change the font of the sheet tabs?
        32. Can I change the default font and color of cell comments?
        33. Can I write VBA macros that play sounds?
        34. 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?
        35. Why does Excel crash every time I start it?
      2. The Visual Basic Editor
        1. Can I use the VBA macro recorder to record all my macros?
        2. I turned on the macro recorder when I edited a chart, but many of the commands weren’t recorded.
        3. 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?
        4. I can’t find my Personal Macro Workbook. Where is it?
        5. I locked my VBA project with a password, and I forget what it was. Is there any way to unlock it?
        6. How can I write a macro to change the password of my project?
        7. When I insert a new module, it always starts with an Option Explicit line. What does this mean?
        8. Why does my VBA code appear in different colors? Can I change these colors?
        9. Can I delete a VBA module by using VBA code?
        10. 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?
        11. 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.
        12. How does the UserInterfaceOnly option work when protecting a worksheet?
        13. How can I tell whether a workbook has a macro virus?
        14. I’m having trouble with the concatenation operator (&) in VBA. When I try to concatenate two strings, I get an error message.
        15. I can’t seem to get the VBA line continuation character (underscore) to work.
        16. I distributed an Excel application to many users. On some machines, my VBA error-handling procedures don’t work. Why not?
      3. Procedures
        1. What’s the difference between a VBA procedure and a macro?
        2. What’s a procedure?
        3. What is a variant data type?
        4. What’s the difference between a variant array and an array of variants?
        5. What’s a type-definition character?
        6. 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?
        7. The Conditional Formatting feature is useful, but I’d like to perform other types of operations when data is entered into a cell.
        8. What other types of events can be monitored?
        9. I tried entering an event procedure (Sub Workbook_Open), but the procedure isn’t executed when the workbook is opened. What’s wrong?
        10. 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?
        11. I’m very familiar with creating formulas in Excel. Does VBA use the same mathematical and logical operators?
        12. How can I execute a procedure that’s in a different workbook?
        13. 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?
        14. 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?
        15. I have a workbook that uses a Workbook_Open procedure. Is there a way to prevent this from executing when I open the workbook?
        16. Can a VBA procedure access a cell’s value in a workbook that is not open?
        17. How can I prevent the “save file” prompt from being displayed when I close a workbook from VBA?
        18. How can I set things up so that my macro runs once every hour?
        19. How do I prevent a macro from showing in the macro list?
        20. Can I save a chart as a GIF file?
        21. 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?
      4. Functions
        1. I created a VBA function for use in worksheet formulas. However, it always returns #NAME?. What went wrong?
        2. 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?
        3. 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?
        4. Can I also display help for the arguments for my custom function in the Insert Function dialog box?
        5. 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?
        6. How can I create a new function category?
        7. 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!)?
        8. How can I force a recalculation of formulas that use my custom worksheet function?
        9. Can I use Excel’s built-in worksheet functions in my VBA code?
        10. Is there any way to force a line break in the text of a message box?
      5. Objects, Properties, Methods, and Events
        1. Is there a listing of the Excel objects I can use?
        2. 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?
        3. What’s the story with collections? Is a collection an object? What are collections?
        4. 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?
        5. How can I prevent the user from scrolling around the worksheet?
        6. What’s the difference between using Select and Application.Goto?
        7. What’s the difference between activating a range and selecting a range?
        8. Is there a quick way to delete all values from a worksheet yet keep the formulas intact?
        9. 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?
        10. 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?
        11. How can I turn off screen updating while a macro is running?
        12. What’s the easiest way to create a range name in VBA?
        13. How can I determine whether a particular cell or range has a name?
        14. Can I disable the Setup and Margins buttons that are displayed in Excel’s Print Preview window?
        15. 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?
        16. 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?
        17. 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?
        18. 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?
        19. How can I determine if a chart is activated?
        20. 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?
        21. I use Excel to create invoices. Can I generate a unique invoice number?
        22. 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?
        23. 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?
        24. How can I determine the last non-empty cell in a particular column?
        25. 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?
        26. Can I declare an array if I don’t know how many elements it will have?
        27. Can I let the user undo my macro?
        28. Can I pause a macro so the user can enter data into a certain cell?
        29. VBA has an InputBox function, but there’s also an InputBox method for the Application object. Are these the same?
        30. 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?
        31. I created an array, but the first element in that array is being treated as the second element. What’s wrong?
        32. I would like my VBA code to run as quickly as possible. Any suggestions?
      6. UserForms
        1. My macro needs to get just a few pieces of information from the user, and a UserForm seems like overkill. Are there any alternatives?
        2. I have 12 CommandButtons on a UserForm. How can I assign a single macro to be executed when any of the buttons is clicked?
        3. How can I display a chart in a UserForm?
        4. 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.
        5. 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?
        6. 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.
        7. Is there any difference between hiding a UserForm and unloading a UserForm?
        8. How can I make my UserForm stay open while I do other things?
        9. 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?
        10. 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?
        11. How can I use Excel’s shapes on my UserForm?
        12. How can I generate a list of files and directories into my UserForm so the user can select a file from the list?
        13. 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?
        14. Is there an easy way to fill a ListBox or ComboBox control with items?
        15. Can I display a built-in Excel dialog box from VBA?
        16. I tried the technique described in the preceding question and received an error message. Why is that?
        17. 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?
        18. Can I create a UserForm without a title bar?
        19. When I click a button on my UserForm, nothing happens. What am I doing wrong?
        20. Can I create a UserForm whose size is always the same, regardless of the video display resolution?
        21. Can I create a UserForm box that lets the user select a range in a worksheet by pointing?
        22. Can I change the startup position of a UserForm?
        23. Can I make a UserForm that’s resizable by the user?
      7. Add-ins
        1. Where can I get Excel add-ins?
        2. How do I install an add-in?
        3. 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?
        4. 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?
        5. How do I create an add-in?
        6. I try to create an add-in, but the Save as Type dropdown box doesn’t provide Add-in as an option.
        7. Should I convert all my essential workbooks to add-ins?
        8. Do I need to keep two copies of my workbook: the XLSM version and the XLAM version?
        9. How do I modify an add-in after it has been created?
        10. 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?
        11. How do I protect the code in my add-in from being viewed by others?
        12. 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?
      8. User Interface
        1. How do I use VBA to add a simple button to the Ribbon?
        2. What are my options for modifying the user interface to make it easy for a user to run my macros?
        3. How do I add a macro to the Quick Access Toolbar?
        4. I added my macro to the QAT, but clicking the icon generates an error.
        5. How do I use VBA to activate a particular tab on the Ribbon?
        6. How can I disable all the right-click shortcut menus?
  11. VIII. Appendixes
    1. A. Excel Resources Online
      1. The Excel Help System
      2. Microsoft Technical Support
        1. Support options
        2. Microsoft Knowledge Base
        3. Microsoft Excel home page
        4. Microsoft Office home page
      3. Internet Newsgroups
        1. Accessing newsgroups by using a newsreader
        2. Accessing newsgroups by using a Web browser
        3. Searching newsgroups
      4. Internet Web Sites
        1. The Spreadsheet Page
        2. Daily Dose of Excel
        3. Jon Peltier’s Excel Page
        4. Pearson Software Consulting
        5. Stephen Bullen’s Excel Page
        6. David McRitchie’s Excel Pages
        7. Mr. Excel
    2. B. VBA Statements and Functions Reference
      1. Invoking Excel Functions in VBA Instructions
    3. C. VBA Error Codes
    4. D. What’s on the CD-ROM
      1. System Requirements
      2. Using the CD
      3. Files and Software on the CD
        1. eBook version of Excel 2007 Power Programming with VBA
        2. Sample files for Excel 2007 Power Programming with VBA
          1. Chapter 3
          2. Chapter 4
          3. Chapter 6
          4. Chapter 7
          5. Chapter 8
          6. Chapter 9
          7. Chapter 10
          8. Chapter 11
          9. Chapter 12
          10. Chapter 13
          11. Chapter 14
          12. Chapter 15
          13. Chapter 16
          14. Chapter 17
          15. Chapter 18
          16. Chapter 19
          17. Chapter 20
          18. Chapter 21
          19. Chapter 22
          20. Chapter 23
          21. Chapter 24
          22. Chapter 25
          23. Chapter 26
          24. Chapter 27
          25. Chapter 28
          26. Chapter 29
          27. Chapter 30
      4. Troubleshooting

Product information

  • Title: Excel® 2007 Power Programming with VBA
  • Author(s): John Walkenbach
  • Release date: April 2007
  • Publisher(s): Wiley
  • ISBN: 9780470044018