Excel 2007 VBA Programming For Dummies E-Book

John Walkenbach

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 422

Veröffentlichungsjahr: 2011

Beschreibung

Step-by-step instructions for creating VBA macros Harness the power of VBA and create custom Excel applications Make Excel 2007 work for you! This clear, nonintimidating guide shows you how to use VBA to create Excel apps that look and work the way you want. Packed with plenty of sample programs, it explains how to work with range objects, control program flow, develop custom dialog boxes, create custom toolbars and menus, and much more. Discover how to * Grasp essential programming concepts * Use the Visual Basic Editor * Navigate the new Excel user interface * Communicate with your users * Deal with errors and bugs

Leseprobe

Excel 2007 VBA Programming For Dummies

by John Walkenbach

Excel 2007 VBA Programming For Dummies®

Published byWiley Publishing, Inc.111 River St.Hoboken, NJ 07030-5774www.wiley.com

Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana

Published by Wiley Publishing, Inc., Indianapolis, Indiana

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 Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related trade dress 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. Microsoft and Excel are registered trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, 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.

For general information on our other products and services, please contact our Customer Care Department within the U.S. at 800-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: 2006939593

ISBN: 978-0-470-04674-6

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 Web site at http://j-walk.com.

Dedication

“This book is dedicated to Jim Kloss and Esther Golton — my two favorite people in Matanuska-Susitna county. By putting their names in this book, I’m ensured of at least one sale in Alaska.”

Author’s Acknowledgments

Thanks to all of the talented people at Wiley Publishing for making it so easy to write these books. And special thanks to Jan Karel Pieterse for his assistance with this edition.

Publisher’s Acknowledgments

We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/.

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

Acquisitions, Editorial, and Media Development

Project Editor: Beth Taylor

Executive Editor: Greg Croy

Copy Editor: Beth Taylor

Technical Editor: Allen Wyatt

Editorial Manager: Jodi Jensen

Media Development Coordinator: Laura Atkinson

Media Project Supervisor: Laura Moss

Media Development Manager: Laura VanWinkle

Media Development Associate Producer: Richard Graves

Editorial Assistant: Amanda Foxworth

Sr. Editorial Assistant: Cherie Case

Cartoons: Rich Tennant (www.the5thwave.com)

Composition Services

Project Coordinator: Jennifer Theriot

Layout and Graphics: Carl Byers, Stephanie D. Jumper, Barbara Moore, Julie Trippetti

Proofreaders: Laura Albert, John Greenough, Techbooks

Indexer: Techbooks

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

Joyce Pepple, Acquisitions Director

Composition Services

Gerry Fahey, Vice President of Production Services

Debbie Stailey, Director of Composition Services

Contents

Title

Introduction

Is This the Right Book?

So You Want to Be a Programmer . . .

Why Bother?

What I Assume about You

Obligatory Typographical Conventions Section

Check Your Security Settings

How This Book Is Organized

Marginal Icons

Get the Sample Files

Now What?

Part I : Introducing VBA

Chapter 1: What Is VBA?

Okay, So What Is VBA?

What Can You Do with VBA?

Advantages and Disadvantages of VBA

VBA in a Nutshell

An Excursion into Versions

Chapter 2: Jumping Right In

First Things First

What You’ll Be Doing

Taking the First Steps

Recording the Macro

Testing the Macro

Examining the Macro

Modifying the Macro

Saving Workbooks that Contain Macros

Understanding Macro Security

More about the NameAndTime Macro

Part II : How VBA Works with Excel

Chapter 3: Working In the Visual Basic Editor

What Is the Visual Basic Editor?

Working with the Project Explorer

Working with a Code Window

Customizing the VBA Environment

Chapter 4: Introducing the Excel Object Model

Excel Is an Object?

Climbing the Object Hierarchy

Wrapping Your Mind around Collections

Referring to Objects

Diving into Object Properties and Methods

Finding Out More

Chapter 5: VBA Sub and Function Procedures

Subs versus Functions

Executing Sub procedures

Executing Function procedures

Chapter 6: Using the Excel Macro Recorder

Is It Live or Is It VBA?

Recording Basics

Preparing to Record

Relative or Absolute?

What Gets Recorded?

Recording Options

Is This Thing Efficient?

Part III : Programming Concepts

Chapter 7: Essential VBA Language Elements

Using Comments in Your VBA Code

Using Variables, Constants, and Data Types

Using Assignment Statements

Working with Arrays

Using Labels

Chapter 8: Working with Range Objects

A Quick Review

Other Ways to Refer to a Range

Some Useful Range Object Properties

Some Useful Range Object Methods

Chapter 9: Using VBA and Worksheet Functions

What Is a Function?

Using Built-in VBA Functions

Using Worksheet Functions in VBA

More about Using Worksheet Functions

Using Custom Functions

Chapter 10: Controlling Program Flow and Making Decisions

Going with the Flow, Dude

The GoTo Statement

Knocking Your Code for a Loop

Looping through a Collection

Chapter 11: Automatic Procedures and Events

Preparing for the Big Event

Where Does the VBA Code Go?

Writing an Event-Handler Procedure

Introductory Examples

Examples of Activation Events

Other Worksheet-Related Events

Events Not Associated with Objects

Chapter 12: Error-Handling Techniques

Types of Errors

An Erroneous Example

Handling Errors Another Way

Handling Errors: The Details

An Intentional Error

Chapter 13: Bug Extermination Techniques

Species of Bugs

Identifying Bugs

Debugging Techniques

About the Debugger

Bug Reduction Tips

Chapter 14: VBA Programming Examples

Working with Ranges

Changing Excel Settings

Working with Charts

VBA Speed Tips

Using the With-End With structure

Part IV : Communicating with Your Users

Chapter 15: Simple Dialog Boxes

Why Create UserForms?

The MsgBox Function

The InputBox Function

The GetOpenFilename Method

The GetSaveAsFilename Method

Getting a Folder Name

Displaying Excel’s Built-in Dialog Boxes

Chapter 16: UserForm Basics

Knowing When to Use a UserForm

Creating UserForms: An Overview

Working with UserForms

A UserForm Example

Chapter 17: Using UserForm Controls

Getting Started with Dialog Box Controls

Dialog Box Controls: The Details

Working with Dialog Box Controls

Dialog Box Aesthetics

Chapter 18: UserForm Techniques and Tricks

Using Dialog Boxes

A UserForm Example

More UserForm Examples

A Dialog Box Checklist

Chapter 19: Accessing Your Macros Through the User Interface

CommandBars and Excel 2007

Excel 2007 Ribbon Customization

Working with CommandBars

VBA Shortcut Menu Examples

Creating a Custom Toolbar

Part V : Putting It All Together

Chapter 20: Creating Worksheet Functions and Living to Tell about It

Why Create Custom Functions?

Understanding VBA Function Basics

Writing Functions

Working with Function Arguments

Function Examples

Functions That Return an Array

Using the Insert Function Dialog Box

Chapter 21: Creating Excel Add-Ins

Okay . . . So What’s an Add-In?

Why Create Add-Ins?

Working with Add-Ins

Add-in Basics

An Add-in Example

Part VI : The Part of Tens

Chapter 22: Ten VBA Questions (And Answers)

The Top Ten Questions about VBA

Chapter 23: (Almost) Ten Excel Resources

The VBA Help System

Microsoft Product Support

Internet Newsgroups

Internet Web Sites

Excel Blogs

Google

Local User Groups

My Other Book

Part I

Introducing VBA

In this part . . .

E very book must start somewhere. This one starts by introducing you to Visual Basic for Applications (and I’m sure you two will become very good friends over the course of a few dozen chapters). After the introductions are made, Chapter 2 walks you through a real-live Excel programming session.