Excel VBA Programming For Dummies E-Book

John Walkenbach

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch
• Veröffentlichungsjahr: 2011

Seitenzahl: 424

Beschreibung

Having Excel and just using it for standard spreadsheets is alittle like getting the ultimate cable system and a 50" flatpanel plasma HDTV and using it exclusively to watch Lawrence Welkreruns. With Visual Basic for Applications (VBA) programming, youcan take advantage of numerous Excel options such as: creating newworksheet functions; automating tasks and operations; creating newappearances, toolbars, and menus; designing custom dialog boxes andadd-ins; and much more. This guide is not for rank Excel amateurs. It's forintermediate to advanced Excel users who want to learn VBAprogramming (or whose bosses want them to learn VBA programming).You need to know your way around Excel before you start creatingcustomized short cuts or systems for speeding through Excelfunctions. If you're an intermediate or advanced Excel user,Excel VBA For Dummies helps you take your skills (and yourspreadsheets) to the next level. It includes: * An introduction to the VBA language * A hands-on, guided, step-by-step walk through developing auseful VBA macro, including recording, testing, and changing it,and testing it * The essential foundation, including the Visual Basic Editor(VBE) and its components, modules, Excel object model, subroutinesand functions, and the Excel macro recorder * The essential VBA language elements, including comments,variables and constants, and labels * Working with Range objects and discovering useful Rangeobjective properties and methods * Using VBA and worksheet functions, including a list andexamples * Programming constructions, including the GoTo statement, theIf-Then structure, Select Case, For-Next loop, Do-While loop, andDo-Until loop * Automatic procedures and Workbook events, including a table andevent-handler procedures * Error-handling and bug extermination techniques, and using theExcel debugging tools * Creating custom dialog boxes, also known as UserForms, with atable of the toolbox controls and their capabilities, how-to forthe dialog box controls, and UserForm techniques and tricks * Customizing the Excel toolbars * Using VBA code to modify the Excel menu system * Creating worksheet functions and working with various types ofarguments * Creating Excel add-ins such as new worksheet functions you canuse in formulas or new commands or utilities Author John Walkenbach is a leading authority on spreadsheetsoftware and the author of more than 40 spreadsheet books includingExcel 2003 Bible and Excel 2003 Power Programming withVBA. While this guide includes tons of examples andscreenshots, Walkenbach knows there's no substitute forhands-on learning. The book is complete with: * A dedicated companion Web site that includes bonus chaptersplus all sample programs to save you a lot of typing and let youplay around and experiment with various changes * Information to help you make the most of Excel's built-inHelp system so you can find out other stuff you may need toknow What are you waiting for? Sure, learning to do VBA programmingtakes a little effort, but it's a Very BigAccomplishment.

Leseprobe

by John Walkenbach

Excel VBA Programming For Dummies®

Published by Wiley Publishing, Inc. 111 River Street Hoboken, NJ 07030-5774

Copyright © 2004 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], e-mail: [email protected].

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. 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 or to obtain technical support, 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].

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: 2004107892

ISBN: 978-0-7645-7412-2

Manufactured in the United States of America

15 14 13 12 11 10 9

1B/RS/QW/QY/IN

About the Author

John Walkenbach is the author of more than 40 spreadsheet books and lives in southern Arizona. Visit his Web site at http://j-walk.com.

Author’s Acknowledgments

Thanks to all of the talented people at Wiley Publishing for making it so easy to write these books. Special thanks to Dick Kusleika, the technical editor for this book. Dick uncovered quite a few errors and set me straight on a few things.

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: Tonya Cupp

Technical Editor: Dick Kusleika

Editorial Manager: Leah Cameron

Media Development Specialist: Kit Malone

Media Development Manager: Laura VanWinkle

Media Development Supervisor: Richard Graves

Editorial Assistant: Amanda Foxworth

Cartoons: Rich Tennant, www.the5thwave.com

Composition

Project Coordinator: Adrienne Martinez

Layout and Graphics: Amanda Carter, Andrea Dahl, Lauren Goddard, Stephanie D. Jumper, Michael Kruzil, Lynsey Osborn, Jacque Roth

Proofreaders: Laura Albert, TECHBOOKS Production Services

Indexer: TECHBOOKS Production 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

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

What You’ll Be Doing

Taking the First Steps

Recording the Macro

Testing the Macro

Examining the Macro

Modifying the Macro

More about the ConvertFormulas Macro

Part II : How VBA Works with Excel

Chapter 3: Introducing 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 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

Part IV : Developing Custom Dialog Boxes

Chapter 15: Custom Dialog Box Alternatives

Why Create Dialog Boxes?

The MsgBox Function

The InputBox Function

The GetOpenFilename Method

The GetSaveAsFilename Method

Displaying Excel’s Built-in Dialog Boxes

Chapter 16: Custom Dialog Box Basics

Knowing When to Use a Custom Dialog Box (Also Known as UserForm)

Creating Custom Dialog Boxes: An Overview

Working with UserForms

A Custom Dialog Box Example

Chapter 17: Using Dialog Box 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

Part V : Creating Custom Toolbars and Menus

Chapter 19: Customizing the Excel Toolbars

Introducing CommandBars

Customizing Toolbars

Working with Toolbars

Adding and Removing Toolbar Controls

Distributing Toolbars

Using VBA to Manipulate Toolbars

VBA Examples

Chapter 20: When the Normal Excel Menus Aren’t Good Enough

Defining Menu Lingo

How Excel Handles Menus

Customizing Menus Directly

Looking Out for the CommandBar Object

Would You Like to See Our Menu Examples?

Working with Shortcut Menus

Finding Out More

Part VI : Putting It All Together

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

Using the Insert Function Dialog Box

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

Chapter 23: Interacting with Other Office Applications

Starting Another Application from Excel

Using Automation in Excel

Sending Personalized E-mail Using Outlook

Working with ADO

Part VII : The Part of Tens

Chapter 24: Ten VBA Questions (And Answers)

The Top Ten Questions about VBA

Chapter 25: (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.

Chapter 1

What Is VBA?

In This Chapter

Gaining a conceptual overview of VBA

Finding out what you can do with VBA

Discovering the advantages and disadvantages of using VBA

Taking a mini-lesson on the history of Excel

T his chapter is completely devoid of any hands-on training material. It does, however, contain some essential background information that assists you in becoming an Excel programmer. In other words, this chapter paves the way for everything else that follows and gives you a feel for how Excel programming fits into the overall scheme of the universe.

Okay, So What Is VBA?

VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft — you know, the company run by the richest man in the world. Excel, along with the other members of Microsoft Office 2003, includes the VBA language (at no extra charge). In a nutshell, VBA is the tool that people like you and me use to develop programs that control Excel.

Don’t confuse VBA with VB (which stands for Visual Basic). VB is a programming language that lets you create standalone executable programs (those EXE files). Although VBA and VB have a lot in common, they are different animals.

What Can You Do with VBA?

You’re probably aware that people use Excel for thousands of different tasks. Here are just a few examples:

Keeping lists of things such as customer names, students’ grades, or holiday gift ideas

Budgeting and forecasting

Analyzing scientific data

Creating invoices and other forms

Developing charts from data

Yadda, yadda, yadda

The list could go on and on, but I think you get the idea. My point is simply that Excel is used for a wide variety of things, and everyone reading this book has different needs and expectations regarding Excel. One thing virtually every reader has in common is the That, dear reader, is what VBA is all about.