Excel 2013 Power Programming with VBA E-Book

John Walkenbach

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Serie: Mr. Spreadsheet's Bookshelf
• Sprache: Englisch
• Veröffentlichungsjahr: 2013

Seitenzahl: 1241

Beschreibung

Maximize your Excel 2013 experience using VBA application development The new Excel 2013 boasts updated features, enhanced power, and new capabilities. Naturally, that means John Walkenbach returns with a new edition of his bestselling VBA Programming book and covers all the methods and tools you need to know in order to program with Excel. With this comprehensive guide, "Mr. Spreadsheet" shows you how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Featuring a complete introduction to Visual Basic for Applications and fully updated for the latest features of Excel 2013, this essential reference includes an analysis of Excel application development and is packed with procedures, tips, and ideas for expanding Excel's capabilities with VBA. * Offers an analysis of Excel application development and a complete introduction to VBA * Features invaluable advice from "Mr. Spreadsheet" himself, bestselling author John Walkenbach, who demonstrates all the techniques you need to create Excel applications, both large and small * Covers navigating the Excel interface, formatting worksheets, interacting with other Office applications, working with collaboration tools, and using sample workbooks and John Walkenbach's award-winning Power Utility Pak to help enhance your Excel skills * Provides tips, tricks, and techniques for expanding Excel's capabilities with VBA that you wont find anywhere else Excel 2013 Power Programming with VBA is packed with procedures, tips, and ideas for achieving Excel excellence with VBA.

Leseprobe

Excel® 2013 Power Programming with VBA

Table of Contents

Introduction

Topics Covered

What You Need to Know

What You Need to Have

Conventions in This Book

Excel commands

Visual Basic Editor commands

Keyboard conventions

Mouse conventions

What the Icons Mean

How This Book Is Organized

Part I: Some Essential Background

Part II: Understanding Visual Basic for Applications

Part III: Working with UserForms

Part IV: Advanced Programming Techniques

Part V: Developing Applications

Part VI: Other Topics

Part VII: Appendixes

About This Book's Website

About the Power Utility Pak Offer

How to Use This Book

Part I: Some Essential Background

Chapter 1: Excel in a Nutshell

About Excel

Thinking in Terms of Objects

Workbooks

Worksheets

Chart sheets

XLM macro sheets

Excel 5 and 95 dialog sheets

Excel's User Interface

About the Ribbon

Shortcut menus and the Mini toolbar

Dialog boxes

Task pane

Keyboard shortcuts

Data Entry

Formulas, Functions, and Names

Selecting Objects

Formatting

Protection Options

Protecting formulas from being overwritten

Protecting a workbook's structure

Applying password protection to a workbook

Protecting VBA code with a password

Charts

Shapes and SmartArt

Database Access

Worksheet databases

External databases

Internet Features

Analysis Tools

Add-Ins

Macros and Programming

File Format

Excel's Help System

Chapter 2: Formula Tricks and Techniques

About Formulas

Calculating Formulas

Cell and Range References

Why use references that aren't relative?

About R1C1 notation

Referencing other sheets or workbooks

Using Names

Naming cells and ranges

Applying names to existing references

Intersecting names

Naming columns and rows

Scoping names

Naming constants

Naming formulas

Naming objects

Formula Errors

Excel Auditing Tools

Identifying cells of a particular type

Viewing formulas

Tracing cell relationships

Tracing error values

Fixing circular reference errors

Using the background error-checking feature

Using Excel's Formula Evaluator

Array Formulas

An array formula example

An array formula calendar

Array formula pros and cons

Counting and Summing Techniques

Counting formula examples

Summing formula examples

Other counting tools

Lookup Formulas

Working with Dates and Times

Entering dates and times

Using pre-1900 dates

Creating Megaformulas

Chapter 3: Understanding Excel Files

Starting Excel

File Types

Excel file formats

Text file formats

Database file formats

Other file formats

Excel File Compatibility

Protected View

Using AutoRecover

Recovering versions of the current workbook

Recovering unsaved work

Configuring AutoRecover

Working with Template Files

Viewing templates

Creating templates

Creating workbook templates

Inside an Excel File

Dissecting a file

Why is the file format important?

The OfficeUI File

The XLB File

Add-In Files

Excel Settings in the Registry

About the Registry

Excel's settings

Chapter 4: Essentials of Spreadsheet Application Development

What Is a Spreadsheet Application?

Steps for Application Development

Determining User Needs

Planning an Application That Meets User Needs

Determining the Most Appropriate User Interface

Customizing the Ribbon

Customizing shortcut menus

Creating shortcut keys

Creating custom dialog boxes

Using ActiveX controls on a worksheet

Executing the development effort

Concerning Yourself with the End User

Testing the application

Making the application bulletproof

Making the application aesthetically appealing and intuitive

Creating a user Help system

Documenting the development effort

Distributing the application to the user

Updating the application when necessary

Other Development Issues

The user's installed version of Excel

Language issues

System speed

Video modes

Part II: Understanding Visual Basic for Applications

Chapter 5: Introducing Visual Basic for Applications

Getting Some BASIC Background

Delving into VBA

Object models

VBA versus XLM

Covering the Basics of VBA

Introducing Visual Basic Editor

Displaying Excel's Developer tab

Activating VBE

VBE windows

Working with Project Explorer

Adding a new VBA module

Removing a VBA module

Exporting and importing objects

Working with Code Windows

Minimizing and maximizing windows

Storing VBA code

Entering VBA code

Customizing the VBE Environment

Using the Editor tab

Using the Editor Format tab

Using the General tab

Using the Docking tab

The Macro Recorder

What the macro recorder actually records

Relative or absolute recording?

Recording options

Cleaning up recorded macros

About Objects and Collections

The object hierarchy

About collections

Referring to objects

Properties and Methods

Object properties

Object methods

The Comment Object: A Case Study

Viewing Help for the Comment object

Properties of a Comment object

Methods of a Comment object

The Comments collection

About the Comment property

Objects contained in a Comment object

Determining whether a cell has a comment

Adding a new Comment object

Some Useful Application Properties

Working with Range Objects

The Range property

The Cells property

The Offset property

Things to Know about Objects

Essential concepts to remember

Learning more about objects and properties

Chapter 6: VBA Programming Fundamentals

VBA Language Elements: An Overview

Comments

Variables, Data Types, and Constants

Defining data types

Declaring variables

Scoping variables

Working with constants

Working with strings

Working with dates

Assignment Statements

Arrays

Declaring arrays

Declaring multidimensional arrays

Declaring dynamic arrays

Object Variables

User-Defined Data Types

Built-in Functions

Manipulating Objects and Collections

With-End With constructs

For Each-Next constructs

Controlling Code Execution

GoTo statements

If-Then constructs

Select Case constructs

Looping blocks of instructions

Chapter 7: Working with VBA Sub Procedures

About Procedures

Declaring a Sub procedure

Scoping a procedure

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

Trapping errors

Error-handling examples

A Realistic Example That Uses Sub Procedures

The goal

Project requirements

What you know

The approach

What you need to know

Some preliminary recording

Initial setup

Code writing

Writing the Sort procedure

More testing

Fixing the problems

Utility availability

Evaluating the project

Chapter 8: Creating Function Procedures

Sub Procedures versus Function Procedures

Why Create Custom Functions?

An Introductory Function Example

Using the function in a worksheet

Using the function in a VBA procedure

Analyzing the custom function

Function Procedures

A function's scope

Executing function procedures

Function Arguments

Function Examples

Functions with no argument

A function with one argument

A function with two arguments

A function with an array argument

A function with optional arguments

A function that returns a VBA array

A function that returns an error value

A function with an indefinite number of arguments

Emulating Excel's SUM function

Extended Date Functions

Debugging Functions

Dealing with the Insert Function Dialog Box

Using the MacroOptions method

Specifying a function category

Adding a function description manually

Using Add-ins to Store Custom Functions

Using the Windows API

Windows API examples

Determining the Windows directory

Detecting the Shift key

Learning more about API functions

Chapter 9: VBA Programming Examples and Techniques

Learning by Example

Working with Ranges

Copying a range

Moving a range

Copying a variably sized range

Selecting or otherwise identifying various types of ranges

Resizing a range

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

Saving all workbooks

Saving and closing all workbooks

Hiding all but the selection

Creating a hyperlink table of contents

Synchronizing worksheets

VBA Techniques

Toggling a Boolean property

Displaying the date and time

Displaying friendly time

Getting a list of fonts

Sorting an array

Processing a series of files

Some Useful Functions for Use in Your Code

The FileExists function

The FileNameOnly function

The PathExists function

The RangeNameExists function

The SheetExists function

The WorkbookIsOpen function

Retrieving a value from a closed workbook

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

Determining the last nonempty cell in a column or row

Does a string match a pattern?

Extracting the nth element from a string

Spelling out a number

A multifunctional function

The SHEETOFFSET function

Returning the maximum value across all worksheets

Returning an array of nonduplicated random integers

Randomizing a range

Sorting a range

Windows API Calls

Determining file associations

Determining disk drive information

Determining default printer information

Determining video display information

Reading from and writing to the Registry

Part III: Working with UserForms

Chapter 10: Custom Dialog Box Alternatives

Before You Create That UserForm . . .

Using an Input Box

The VBA InputBox function

The Excel InputBox method

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

Making the data form accessible

Displaying a data form by using VBA

Chapter 11: Introducing UserForms

How Excel Handles Custom Dialog Boxes

Inserting a New UserForm

Adding Controls to a UserForm

Toolbox Controls

CheckBox

ComboBox

CommandButton

Frame

Image

Label

ListBox

MultiPage

OptionButton

RefEdit

ScrollBar

SpinButton

TabStrip

TextBox

ToggleButton

Adjusting UserForm Controls

Adjusting a Control's Properties

Using the Properties window

Common properties

Accommodating keyboard users

Displaying a UserForm

Adjusting the display position

Displaying a modeless UserForm

Displaying a UserForm based on a variable

Loading a UserForm

About event-handler procedures

Closing a UserForm

Creating a UserForm: An Example

Creating the UserForm

Writing code to display the dialog box

Testing the dialog box

Adding event-handler procedures

Validating the data

The finished dialog box

Understanding UserForm Events

Learning about events

UserForm events

SpinButton events

Pairing a SpinButton with a TextBox

Referencing UserForm Controls

Customizing the Toolbox

Adding new pages to the Toolbox

Customizing or combining controls

Adding new ActiveX controls

Creating UserForm Templates

A UserForm Checklist

Chapter 12: UserForm Examples

Creating a UserForm “Menu”

Using CommandButtons in a UserForm

Using a ListBox in 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

Adding items to a ListBox control

Determining the selected item in a ListBox

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

Chapter 13: Advanced UserForm Techniques

A Modeless Dialog Box

Displaying a Progress Indicator

Creating a stand-alone progress indicator

Showing a progress indicator by using a MultiPage control

Showing a progress indicator without using a MultiPage control

Creating Wizards

Setting up the MultiPage control for the wizard

Adding the buttons to the wizard's UserForm

Programming the wizard's buttons

Programming dependencies in a wizard

Performing the task with the wizard

Emulating the MsgBox Function

MsgBox emulation: MyMsgBox code

How the MyMsgBox function works

Using the MyMsgBox function

A UserForm with Movable Controls

A UserForm with No Title Bar

Simulating a Toolbar with a UserForm

Emulating a Task Pane with a UserForm

A Resizable UserForm

Handling Multiple UserForm Controls with One Event Handler

Selecting a Color in a UserForm

Displaying a Chart in a UserForm

Saving a chart as a GIF file

Changing the Image control's Picture property

Making a UserForm Semitransparent

An Enhanced Data Form

About the Enhanced Data Form

Installing the Enhanced Data Form add-in

A Puzzle on a UserForm

Video Poker on a UserForm

Part IV: Advanced Programming Techniques

Chapter 14: 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

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

Chapter 15: Working with Pivot Tables

An Introductory Pivot Table Example

Creating a pivot table

Examining the recorded code for the pivot table

Cleaning up the recorded pivot table code

Creating a More Complex Pivot Table

The code that created the pivot table

How the more complex pivot table works

Creating Multiple Pivot Tables

Creating a Reverse Pivot Table

Chapter 16: Working with Charts

Getting the Inside Scoop on Charts

Chart locations

The macro recorder and charts

The Chart object model

Creating an Embedded Chart

Creating a Chart on a Chart Sheet

Modifying Charts

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

Creating Lots of Charts

Exporting a Chart

Exporting all graphics

Changing the Data Used in a Chart

Changing chart data based on the active cell

Using VBA to determine the ranges used in a chart

Using VBA to Display Arbitrary Data Labels on a Chart

Displaying a Chart in a UserForm

Understanding Chart Events

An example of using Chart events

Enabling events for an embedded chart

Example: Using Chart events with an embedded chart

Discovering VBA Charting Tricks

Printing embedded charts on a full page

Hiding series by hiding columns

Creating unlinked charts

Displaying text with the MouseOver event

Animating Charts

Scrolling a chart

Creating a hypocycloid chart

Creating a clock chart

Creating an Interactive Chart without VBA

Getting the data to create an interactive chart

Creating the Option Button controls for an interactive chart

Creating the city lists for the interactive chart

Creating the interactive chart data range

Creating the interactive chart

Working with Sparkline Charts

Chapter 17: Understanding Excel's Events

What You Should Know about Events

Understanding event sequences

Where to put event-handler procedures

Disabling events

Entering event-handler code

Event-handler procedures that use arguments

Getting Acquainted with Workbook-Level Events

The Open event

The Activate event

The SheetActivate event

The NewSheet event

The BeforeSave event

The Deactivate event

The BeforePrint event

The BeforeClose event

Examining Worksheet Events

The Change event

Monitoring a specific range for changes

The SelectionChange event

The BeforeDoubleClick event

The BeforeRightClick event

Checking Out Chart Events

Monitoring with Application Events

Enabling Application-level events

Determining when a workbook is opened

Monitoring Application-level events

Using UserForm Events

Accessing Events Not Associated with an Object

The OnTime event

The OnKey event

Chapter 18: Interacting with Other Applications

Starting an Application from Excel

Using the VBA Shell function

Displaying a folder window

Using the Windows ShellExecute API function

Activating an Application with Excel

Using AppActivate

Activating a Microsoft Office application

Running Control Panel Dialog Boxes

Using Automation in Excel

Working with foreign objects using automation

Early versus late binding

A simple example of late binding

Controlling Word from Excel

Controlling Excel from another application

Sending Personalized E-Mail via Outlook

Sending E-Mail Attachments from Excel

Chapter 19: Creating and Using Add-Ins

What Is an Add-In?

Comparing an add-in with a standard workbook

Why create add-ins?

Understanding Excel's Add-In Manager

Creating an Add-in

An Add-In Example

Adding descriptive information for the example add-in

Creating an add-in

Installing an add-in

Testing the add-in

Distributing an add-in

Modifying an add-in

Comparing XLAM and XLSM Files

XLAM file VBA collection membership

Visibility of XLSM and XLAM files

Worksheets and chart sheets in XLSM and XLAM files

Accessing VBA procedures in an add-in

Manipulating Add-Ins with VBA

AddIn object properties

Accessing an add-in as a workbook

AddIn object events

Optimizing the Performance of Add-ins

Special Problems with Add-Ins

Ensuring that an add-in is installed

Referencing other files from an add-in

Detecting the proper Excel version for your add-in

Part V: Developing Applications

Chapter 20: Working with the Ribbon

Ribbon Basics

Using VBA with the Ribbon

Accessing a Ribbon control

Working with the Ribbon

Activating a tab

Customizing the Ribbon

A simple RibbonX example

A simple Ribbon example, take 2

Another RibbonX example

Ribbon controls demo

A dynamicMenu Control Example

More on Ribbon customization

Creating an Old-Style Toolbar

Limitations of old-style toolbars in Excel 2007 and later

Code to create a toolbar

Chapter 21: Working with Shortcut Menus

CommandBar Overview

CommandBar types

Listing shortcut menus

Referring to CommandBars

Referring to controls in a CommandBar

Properties of CommandBar controls

Displaying all shortcut menu items

Using VBA to Customize Shortcut Menus

What's different in Excel 2013

Resetting a shortcut menu

Disabling a shortcut menu

Disabling shortcut menu items

Adding a new item to the Cell shortcut menu

Adding a submenu to a shortcut menu

Limiting a shortcut menu to a single workbook

Shortcut Menus and Events

Adding and deleting menus automatically

Disabling or hiding shortcut menu items

Creating a context-sensitive shortcut menu

Chapter 22: Providing Help for Your Applications

Help for Your Excel Applications

Help Systems That Use Excel Components

Using cell comments for help

Using a text box for help

Using a worksheet to display help text

Displaying help in a UserForm

Displaying Help in a Web Browser

Using HTML files

Using an MHTML file

Using the HTML Help System

Using the Help method to display HTML Help

Associating a help file with your application

Associating a help topic with a VBA function

Chapter 23: 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

How the Loan Amortization Wizard works

Potential enhancements for the Loan Amortization Wizard

Application Development Concepts

Part VI: Other Topics

Chapter 24: Compatibility Issues

What Is Compatibility?

Types of Compatibility Problems

Avoid Using New Features

But Will It Work on a Mac?

Dealing with 64-bit Excel

Creating an International Application

Multilanguage applications

VBA language considerations

Using local properties

Identifying system settings

Date and time settings

Chapter 25: Manipulating Files with VBA

Performing Common File Operations

Using VBA file-related statements

Using the FileSystemObject object

Displaying Extended File Information

Working with Text Files

Opening a text file

Reading a text file

Writing a text file

Getting a file number

Determining or setting the file position

Statements for reading and writing

Text File Manipulation Examples

Importing data in a text file

Exporting a range to a text file

Importing a text file to a range

Logging Excel usage

Filtering a text file

Exporting a range to HTML format

Exporting a range to an XML file

Zipping and Unzipping Files

Zipping files

Unzipping a file

Working with ADO

Chapter 26: Manipulating Visual Basic Components

Introducing IDE

The IDE Object Model

The VBProjects collection

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

Design-time versus runtime UserForm manipulations

Adding 100 CommandButtons at design time

Creating UserForms Programmatically

A simple runtime UserForm example

A useful (but not simple) dynamic UserForm example

Chapter 27: Understanding Class Modules

What Is a Class Module?

Example: Creating a NumLock Class

Inserting a class module

Adding VBA code to the class module

Using the NumLockClass class

More about Class Modules

Programming properties of objects

Programming methods for objects

Class module events

Example: A CSV File Class

Class module–level variables for the CSVFileClass

Property procedures for the CSVFileClass

Method procedures for the CSVFileClass

Using the CSVFileClass object

Chapter 28: Working with Colors

Specifying Colors

The RGB color system

The HSL color system

Converting colors

Understanding Grayscale

Converting colors to gray

Experimenting with Colors

Understanding Document Themes

About document themes

Understanding document theme colors

Displaying all theme colors

Working with Shape Objects

A shape's background color

Shapes and theme colors

Modifying Chart Colors

Chapter 29: Frequently Asked Questions about Excel Programming

Getting the Scoop on FAQs

General Excel Questions

Visual Basic Editor

Procedures

Functions

Objects, Properties, Methods, and Events

Security-Related Issues

UserForms

Add-Ins

User Interface

Part VII: Appendixes

Appendix A: VBA Statements and Functions Reference

Appendix B: VBA Error Codes

Appendix C: This Book's Website

Excel® 2013 Power Programming with VBA

Published byJohn Wiley & Sons, Inc.111 River StreetHoboken, NJ 07030-5774

www.wiley.com

Copyright © 2013 by John Wiley & Sons, Inc., Hoboken, New Jersey

Published by John Wiley & Sons, Inc., Hoboken, New Jersey

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries, and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc., is not associated with any product or vendor mentioned in this book.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read. FULFILLMENT OF EACH COUPON OFFER IS THE SOLE RESPONSIBILITY OF THE OFFEROR.

For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax [phone].

For technical support, please visit www.wiley.com/techsupport.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

Library of Congress Control Number: 2013932111

ISBN 978-1-118-49039-6 (pbk); ISBN 978-1-118-49040-2 (ebk); ISBN 978-1-118-49180-5 (ebk); ISBN 978-1-118-49182-9 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

About the Author

John Walkenbach is the author of more than 50 spreadsheet books and lives in southern Arizona. Visit his website: http://spreadsheetpage.com.

Publisher's Acknowledgments

We're proud of this book; please send us your comments at http://dummies.custhelp.com. For other comments, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax [phone].

Some of the people who helped bring this book to market include the following:

Acquisitions and Editorial

Project Editor: Susan Pink

Acquisitions Editor: Katie Mohr

Technical Editor: Niek Otten

Editorial Manager: Jodi Jensen

Editorial Assistant: Annie Sullivan

Sr. Editorial Assistant: Cherie Case

Composition Services

Project Coordinator: Kristie Rees

Layout and Graphics: Jennifer Henry, Andrea Hornberger, Jennifer Mayberry

Proofreader: Christine Sabooni

Indexer: BIM Indexing & Proofreading Services

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary Bednarek, Executive Acquisitions Director

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Diane Graves Steele, Vice President and Publisher

Composition Services

Debbie Stailey, Director of Composition Services

Part I: Some Essential Background

Chapter 1: Excel in a Nutshell

Chapter 2: Formula Tricks and Techniques

Chapter 3: Understanding Excel Files

Chapter 4: Essentials of Spreadsheet Application Development

Chapter 1: Excel in a Nutshell

In This Chapter

• Introducing Excel's object orientation

• Gaining a conceptual overview of Excel, including a description of its major features

• Discovering the new features in Excel 2013

• Taking advantage of helpful tips and techniques

About Excel

Excel is, by far, the most commonly used spreadsheet product in the world. Because you're reading this book, you are probably familiar with Excel and have used the product for several years. But even a veteran user sometimes needs a refresher course — especially if your experience is mostly with Excel 2003 or earlier versions.

In this chapter, I provide a quick overview of Excel and introduce the concept of objects — an essential component in mastering VBA programming.

Thinking in Terms of Objects

When you're developing applications with Excel (especially when you're dabbling with Visual Basic for Applications — VBA), it's helpful to think in terms of objects, or Excel elements that you can manipulate manually or via a macro. Here are some examples of Excel objects:

• The Excel application

• An Excel workbook

• A worksheet in a workbook

• A range or a table in a worksheet

• A ListBox control on a UserForm (a custom dialog box)

• A chart embedded in a worksheet

• A chart series in a chart

• A particular data point in a chart

You may notice that an object hierarchy exists here: The Excel object contains workbook objects, which contain worksheet objects, which contain range objects. This hierarchy makes up Excel's object model. Excel has more than 200 classes of objects that you can control directly or by using VBA. Other Microsoft Office products have their own object models.

Controlling objects is fundamental to developing applications. Throughout this book, you find out how to automate tasks by controlling Excel's objects, and you do so by using VBA. This concept becomes clearer in subsequent chapters.

Workbooks

The most common Excel object is a workbook. Everything that you do in Excel takes place in a workbook, which is stored in a file that, by default, has an XLSX extension. An Excel workbook can hold any number of sheets (limited only by memory). There are four types of sheets:

• Worksheets

• Chart sheets

• Excel 4.0 XLM macro sheets (obsolete, but still supported)

• Excel 5.0 dialog sheets (obsolete, but still supported)

You can open or create as many workbooks as you like (each in its own window), but only one workbook is the active workbook at any given time. Similarly, only one sheet in a workbook is the active sheet. To activate a sheet, click its sheet tab at the bottom of the screen. To change a sheet's name, double-click the tab and enter the new text. Right-clicking a tab brings up a shortcut menu with additional options for the sheet, including changing its tab color and hiding the sheet.

You can also hide the window that contains a workbook by using the View⇒Window⇒Hide command. A hidden workbook window remains open, but it isn't visible to the user. Use the View⇒Window⇒Unhide command to make the window visible again.

A single workbook can display in multiple windows (choose View⇒Window⇒New Window). Each window can display a different sheet or a different area of the same sheet.

Worksheets

The most common type of sheet is a worksheet, which is what people normally think of when they think of a spreadsheet. Worksheets contain cells, and the cells store data and formulas.

 How big is a worksheet?

Stop and think about the actual size of a worksheet. Do the arithmetic (16,384 × 1,048,576), and you'll see that a worksheet has 17,179,869,184 cells. Remember that this is in just one worksheet — a single workbook can hold more than one worksheet.

If you're using a 1920 x 1200 video mode with the default row heights and column widths, you can see 29 columns and 47 rows (or 1,363 cells) at a time — which is about .0000079 percent of the entire worksheet. In other words, more than 12.6 million screens of information reside in a single worksheet.

If you entered a single digit into each cell at the relatively rapid clip of one cell per second, it would take you over 500 years, nonstop, to fill up a worksheet. To print the results of your efforts would require more than 36 million sheets of paper — a stack about 12,000 feet high. (That's ten Empire State Buildings stacked on top of each other.)

As you might suspect, filling an entire workbook with values is impossible. Even if you use the 64-bit version of Excel (which accommodates much larger workbooks), you'd soon run out of memory, and Excel would probably crash.

Excel 2013 worksheets have 16,384 columns and 1,048,576 rows. You can hide unneeded rows and columns to keep them out of view, but you can't increase or decrease the number of rows or columns.

Versions prior to Excel 2007 used the XLS binary format, and worksheets had only 65,536 rows and 256 columns. If you open such a file, Excel 2013 automatically enters compatibility mode to work with the smaller worksheet grid. To convert such a file to the new format, save it as an XLSX or XLSM file. Then close the workbook and reopen it.

The real value of using multiple worksheets in a workbook isn't access to more cells. Rather, multiple worksheets enable you to organize your work better. Back in the old days, when a file comprised a single worksheet, developers wasted a lot of time trying to organize the worksheet to hold their information efficiently. Now you can store information on any number of worksheets and still access it instantly by clicking a sheet tab.

A worksheet cell can hold a constant value — a number, a date, a Boolean value (True or False), or text — or the result of a formula. Every worksheet also has an invisible drawing layer, which enables you to insert graphic objects, such as charts, shapes, SmartArt, UserForm controls, pictures, and other embedded objects.

You control the column widths and row heights — you can even hide rows and columns (as well as entire worksheets). You can specify any font size, and you control the colors. You can display text in a cell vertically (or at an angle) and even wrap it around to occupy multiple lines. In addition, you can merge a group of cells to create a single larger cell.

In the past, Excel was limited to a palette of 56 colors. Beginning with Excel 2007, the number of colors has been virtually unlimited. In addition, Excel 2007 introduced document themes. A single click lets you apply a new theme to a workbook, which can give it an entirely different look.

Chart sheets

A chart sheet holds a single chart. Many users ignore chart sheets, preferring to store charts on the worksheet's drawing layer. Using chart sheets is optional, but they make it a bit easier to print a chart on a page by itself and are especially useful for presentations. Figure 1-1 shows a pie chart on a chart sheet.

Figure 1-1: A pie chart on a chart sheet.

XLM macro sheets

An XLM macro sheet (also known as an MS Excel 4 macro sheet) is essentially a worksheet but with some different defaults. More specifically, an XLM macro sheet displays formulas rather than the results of formulas. In addition, the default column width is larger than in a normal worksheet.

As the name suggests, an XLM macro sheet is designed to hold XLM macros, which were used in Excel 4.0 and earlier. Excel 2013 continues to support XLM macros for compatibility purposes. This book doesn't cover the XLM macro system.

Excel 5 and 95 dialog sheets

In Excel 5 and Excel 95, you created a custom dialog box by inserting a special dialog sheet. Excel 97 and later versions still support these dialog sheets, but a much better alternative is available: UserForms. You work with UserForms in Visual Basic Editor (VBE).

If you open a workbook that contains an Excel 5 or 95 dialog sheet, you can access the dialog sheet by clicking its tab. I don't discuss Excel 5 and Excel 95 dialog sheets in this book.

 What’s new in Excel 2013?

When a new version of Microsoft Office is released, Excel sometimes gets lots of new features and other times gets few new features. In the case of Office 2013, Excel got quite a few new features — but nothing truly earth-shattering.

Here's a quick summary of what's new in Excel 2013, relative to Excel 2010:

• Cloud storage: Excel is tightly integrated with Microsoft's Skyview web-based storage.

• Support for other devices: Excel is available for other devices, including touch-sensitive Windows RT tablets and Windows phones.

• New aesthetics: Excel has new “flat” look and displays an optional graphic in the title bar. Color schemes are limited to white, light gray, and dark gray.

• Single document interface: Excel no longer supports the option to display multiple workbooks in a single window. Each workbook has its own top-level Excel window and Ribbon.

• New types of assistance: Excel provides recommended pivot tables and recommended charts.

• Fill Flash: This feature is a new way to extract (by example) relevant data from text strings. You can also use this feature to combine data in multiple columns.

• Support for Apps for Office: You can download or purchase apps that can be embedded in a workbook file.

• Improved Slicer option: The Slicer feature, introduced in Excel 2010 for use with pivot tables, has been expanded and now works with tables.

• Timeline filtering: Similar to Slicers, a Timeline makes it easy to filter pivot tabledata by dates.

• Quick Analysis: This feature provides single-click access to various data analysis tools.

• Enhanced chart formatting: Modifying and fine-tuning charts is significantly easier.

• Increased use of task panes: Task panes play a larger role in Excel 2013. For example, every aspect of a chart can be modified using task panes.

• New worksheet functions: Excel 2013 supports dozens of new worksheet functions, most of which are esoteric or special-purpose.

• Restructured Backstage: The Backstage screen has been reorganized and is easier to use.

• New add-ins: Office Professional Plus has three new add-ins: PowerPivot, Power View, and Inquire.

Excel's User Interface

A user interface (UI) is the means by which an end user communicates with a computer program. Generally speaking, a UI includes elements such as menus, toolbars, dialog boxes, and keystroke combinations.

The release of Office 2007 signaled the end of traditional menus and toolbars. The UI for Excel consists of the following elements:

• Ribbon

• Quick Access Toolbar

• Right-click shortcut menus

• Mini toolbar

• Dialog boxes

• Keyboard shortcuts

• Task pane

Excel 2013 can also be run on touch-enabled devices. This book assumes that the reader has a traditional keyboard and mouse, and it does not cover the touch-related commands.

About the Ribbon

In Office 2007, Microsoft introduced a new UI for its product. Menus and toolbars were replaced with a tab and Ribbon UI. Click a tab along the top (that is, a word such as Home, Insert, or Page Layout), and the Ribbon displays the commands for that tab. Office 2007 was the first software in history to use this new interface; a few other companies have incorporated this new UI style in their products.

The appearance of the commands on the Ribbon varies, depending on the width of the Excel window. When the window is too narrow to display everything, some commands may seem to be missing, but they are still available. Figure 1-2 shows the Home tab of the Ribbon as it appears for three different window widths.

On the top Ribbon, all controls are fully visible. The middle Ribbon is when Excel's window is narrower. Note that some descriptive text is gone, but the icons remain. The bottom Ribbon appears when the window is very narrow. Some groups display a single icon; click that icon, and all the group commands become available.

If you'd like to hide the Ribbon to increase your worksheet view, just double-click any tab. The Ribbon goes away, and you'll be able to see about four additional rows of your worksheet. When you need to use the Ribbon again, just click any tab, and the Ribbon comes back. You can also press Ctrl+F1 to toggle the Ribbon display or use the Ribbon Display Option control, located in the window's title bar.

Figure 1-2: The Home tab of the Ribbon, for three window widths.

Contextual tabs

In addition to the standard tabs, Excel includes contextual tabs. Whenever an object (such as a chart, a table, a picture, or SmartArt) is selected, tools for working with that specific object are made available on the Ribbon.

Figure 1-3 shows the contextual tabs that appear when an embedded equation is selected. In this case, Excel displays two contextual tabs: Format (for working with object) and Design (for working with the equation). Notice that the contextual tabs contain a description (Drawing Tools and Equation Tools) in Excel's title bar. When contextual tabs are displayed, you can continue to use all the other tabs.

Figure 1-3: When you select an object, contextual tabs contain tools for working with that object.

Types of commands on the Ribbon

For the most part, the commands on the Ribbon work just as you'd expect them to. You'll encounter several different styles of commands on the Ribbon:

• Simple buttons: Click the button, and it does its thing. An example of a simple button is the Increase Font Size button in the Font group of the Home tab. Some buttons perform the action immediately; others display a dialog box so that you can enter additional information. Button controls may or may not be accompanied by text.

• Toggle buttons: A toggle button is clickable and also conveys some type of information by the color it displays. An example is the Bold button in the Font group of the Home tab. If the active cell isn't bold, the Bold button displays in its normal color. But if the active cell is already bold, the Bold button displays a different background color. If you click this button, it toggles the Bold attribute for the selection.

• Simple drop-downs: If the Ribbon command has a small downward-pointing arrow, the command is a drop-down list. An example is the Orientation control in the Alignment group of the Home tab. Click the control and additional commands appear below it.

• Split buttons: A split button control combines a one-click button with a drop-down. If you click the button part, the command is executed. If you click the drop-down part, you choose from a list of related commands. An example of a split button is the Paste command in the Clipboard group of the Home tab. Clicking the top part of this control pastes the information from the Clipboard. If you click the bottom part of the control, you get a list of paste-related commands (see Figure 1-4).

• Check boxes:A check box control turns something on or off. An example is the Gridlines control in the Show/Hide group of the View tab. When the Gridlines check box is selected, the sheet displays gridlines. When the control isn't selected, the sheet gridlines aren't displayed.

• Spinners: An example of a spinner control is in the Scale to Fit group of the Page Layout tab. Click the top part of the spinner to increase the value; click the bottom part of the spinner to decrease the value.

Refer to Chapter 20 for information about customizing Excel's Ribbon.

Some Ribbon groups contain a small icon in the lower-right corner, known as a dialog launcher. For example, if you examine the Home⇒Alignment group, you'll see this icon (refer to Figure 1-5). Click it, and it displays the Format Cells dialog box, with the Number tab preselected. This dialog box provides options that aren't available on the Ribbon.

Figure 1-4: The Paste command is a split button control.

Figure 1-5: This small dialog launcher icon, when clicked, displays a dialog box that has additional options.

The Quick Access toolbar

The Quick Access toolbar is a place to store commonly used commands. The Quick Access toolbar is always visible, regardless of which Ribbon tab you select. Normally, the Quick Access toolbar appears on the left side of the title bar. Alternatively, you can display the Quick Access toolbar below the Ribbon by right-clicking the Quick Access toolbar and choosing Show Quick Access Toolbar Below the Ribbon.

By default, the Quick Access toolbar contains three tools: Save, Undo, and Redo. You can customize the Quick Access toolbar by adding other commands that you use often. To add a command on the Ribbon to your Quick Access toolbar, right-click the command and choose Add To Quick Access toolbar.

Excel has quite a few commands that aren't available on the Ribbon. In most cases, the only way to access these commands is to add them to your Ribbon or Quick Access toolbar. Figure 1-6 shows the Quick Access toolbar section of the Excel Options dialog box. This area is your one-stop shop for Quick Access toolbar customization. A quick way to display this dialog box is to right-click the Quick Access toolbar and choose Customize Quick Access toolbar.

Figure 1-6: Add new icons to your Quick Access toolbar by using the Quick Access toolbar section of the Excel Options dialog box.

Accessing the Ribbon by using your keyboard

At first glance, you may think that the Ribbon is completely mouse-centric. After all, none of the commands has the traditional underlined letter to indicate the Alt+keystrokes. But, in fact, the Ribbon is very keyboard friendly. The trick is to press the Alt key to display pop-up keytips. Each Ribbon control has a letter (or series of letters) that you type to issue the command.

You don't 'need to hold down the Alt key as you type the keytip letters.

Figure 1-7 shows how the Ribbon looks after I press the Alt key, followed by M to display keytips in the Formulas tab. If you press one of the keytips, the screen then displays more keytips. For example, to use the keyboard to align the cell contents to the left, press Alt, followed by H (for Home) and then press AL (for Align Left). If you're a keyboard fan (like me), you'll memorize the keystrokes required for common commands after just a few times.

Figure 1-7: The Ribbon, with keytips displayed.

After you press Alt, you can also use the left- and right-arrow keys to scroll through the tabs. When you reach the proper tab, press the down-arrow key to enter the Ribbon. Then use the left- and right-arrow keys to scroll through the Ribbon commands. When you reach the command you need, press Enter to execute it. This method isn't as efficient as using the keytips, but it's a quick way to take a look at the choices on the Ribbon.

Excel 2013 supports the menu-oriented keyboard shortcuts from Excel 2003. This is handy if you've memorized key sequences, such as Alt+ES (to display the Paste Special dialog box).

 An excursion into versions

If you plan to develop VBA macros, you should have some understanding of Excel’s history. Many different versions of Excel have been released, and quite a few are still commonly used. Because of this, compatibility between versions can be a problem. See Chapter 24 for a discussion of compatibility.

Here are all the major Excel for Windows versions that have been released:

• Excel 2: The original version of Excel for Windows was called Version 2 (rather than 1) so that it would correspond to the Macintosh version. Excel 2 first appeared in 1987.

• Excel 3: Released in late 1990, this version featured the XLM macro language.

• Excel 4: This version was released in early 1992. It also uses the XLM macro language.

• Excel 5: This version came out in early 1994. It was the first version to use VBA (but it also supports XLM). It's been years since I've heard from anyone who uses Excel 5.

• Excel 95: Technically known as Excel 7 (there is no Excel 6), this version began shipping in the summer of 1995. It's rarely used anymore.

• Excel 97: This version (also known as Excel 8) was released in early 1997. It has many enhancements and features a new interface for programming VBA macros. Excel 97 also uses a new file format (which previous Excel versions cannot open).

• Excel 2000: With this version, the numbering scheme jumped to four digits. Excel 2000 (also known as Excel 9) made its debut in June 1999. It includes only a few enhancements from a programmer's perspective. Excel 2000 is rarely used.

• Excel 2002: This version (also known as Excel 10 or Excel XP) appeared in late 2001. Perhaps this version's most significant feature is the capability to recover your work when Excel crashes. Some people still use it.

• Excel 2003: Of all the Excel upgrades, Excel 2003 has the fewest new features. In other words, most hard-core Excel users were disappointed with Excel 2003. As I write this, Excel 2003 is still a commonly used version. It's also the last “pre-Ribbon” version of Excel.

• Excel 2007: Excel 2007 signaled the beginning of a new era. Excel 2007 replaced the old menu and toolbar interface and introduced the Ribbon. I was disappointed to discover that you can't modify the Ribbon by using VBA. But this version of Excel had enough new features to satisfy me, such as a new file format and support for much larger worksheets — more than a million rows.

• Excel 2010: This version includes lots of new features (such as Sparkline graphics) and performs quite a bit better in some areas. And if you need really huge workbooks, you can install the 64-bit version. But again, I was disappointed because you still can't modify the Ribbon using VBA.

• Excel 2013: The latest version is the one I used while writing this edition of the book. Excel 2013 is available also in an online version (the Excel web app) and for devices that run on Windows RT ARM-based devices. The Ribbon is still around, but it now has a flat look — and you still can't modify it using VBA!

Shortcut menus and the Mini toolbar

Apart from the menus in Visual Basic Editor, the only menus that remain in Excel are shortcut menus. These menus appear when you right-click your mouse. The shortcut menus are context sensitive. In other words, the menu that appears depends on the location of the mouse pointer when you right-click. You can right-click just about anything — a cell, a row or column border, a workbook title bar, an element in a chart, and so on.

Right-clicking some objects displays a Mini toolbar above the shortcut menu. This toolbar provides quick access to commonly used formatting commands. Figure 1-8 shows the Mini toolbar when a cell is right-clicked.

Although you can't customize the Ribbon by using VBA, you can use VBA to customize any of the shortcut menus. You can't, however, modify the Mini toolbar.

Refer to Chapter 21 for more information about customizing shortcut menus. Note, however, that the new single document interface in Excel 2013 makes customizing shortcut menus more challenging.

Dialog boxes

Some Ribbon commands display a dialog box, from which you can specify options or issue other commands. You'll find two general classes of dialog boxes in Excel:

• Modal dialog boxes:When a modal dialog box is displayed, it must be closed to execute the commands. An example is the Format Cells dialog box. None of the options you specify are executed until you click OK. Use the Cancel button to close the dialog box without making any changes.

• Modeless dialog boxes: These stay-on-top dialog boxes remain visible as you continue to work. An example is the Find and Replace dialog box. Modeless dialog boxes usually have a Close button rather than OK and Cancel buttons.

Some Excel dialog boxes use a notebook tab metaphor, which makes a single dialog box function as several different dialog boxes. An example is the Format Cells dialog box, shown in Figure 1-9.

Figure 1-8: Right-clicking some objects displays a Mini toolbar in addition to a shortcut menu.

Figure 1-9: Tabbed dialog boxes make many options accessible without overwhelming the user.

Developers can create custom dialog boxes by using the UserForm feature. As you'll see, you can create a wide variety of dialog boxes, including modeless dialog boxes and tabbed dialog boxes.