20,99 €
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
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 422
Veröffentlichungsjahr: 2011
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, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, 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 317-572-4002.
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
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.
“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.”
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.
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
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
Local User Groups
My Other Book
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.