Que - VBA and Macros for Microsoft Excel {Business Solution2.pdf - Excel VB - nien0r

C ontents at a Glance

usiness solutions

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

I First Steps up the VBA Learning Curve

1 Unleash the Power of Excel with VBA! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar? . . . . . . . . . 29

3 Referring to Ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61

4 User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75

5 Looping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103

6 R1C1 Style Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117

7 Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131

8 Event Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141

9 UserForms—An Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161

VBA and Macros

fo r

Microsoft Excel

II Automating Excel Power in VBA

10 Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175

11 Data Mining with Advanced Filter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207

12 Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235

13 Excel Power . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291

14 Reading from and Writing to the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331

15 XML in Excel 2003 Professional . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349

16 Automating Word . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359

III Techie Stuff You Will Need to Produce Applications

for the Administrator to Run

17 Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379

18 Te xt File Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387

19 Using Access as a Back End to Enhance Multi-User

Access to Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401

20 Creating Classes, Records, and Collections . . . . . . . . . . . . . . . . . . . . . . . . 415

21 Advanced UserForm Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 433

22 Windows Application Programming Interface (API) . . . . . . . . . . . . . 453

23 Handling Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 467

24 Using Custom Menus to Run Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481

25 Add-Ins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497

26 Case Study—Designing an Excel Application . . . . . . . . . . . . . . . . . . . . 505

Bill Jelen, Mr. Excel

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 517

Tracy Syrstad

800 East 96th Street

Indianapolis, Indiana 46240

VBA and Macros for Microsoft Excel

Associate Publisher

Michael Stephens

Acquisitions Editor

Loretta Yates

Development Editor

Sean Dixon

Managing Editor

Charlotte Clapp

Project Editor

Andy Beaster

Copy Editor

Margo Catts

Indexer

Erika Millen

Proofreader

Kathy Bidwell

Technical Editor

Tom Urtis

Publishing Coordinator

Cindy Teeters

Book Designer

Anne Jones

Page Layout

Bronkella Publishing

Kelly Maish

Michelle Mitchell

stored in a retrieval system, or transmitted by any means, elec-

tronic, mechanical, photocopying, recording, or otherwise, with-

out written permission from the publisher. No patent liability is

assumed with respect to the use of the information contained

herein. Although every precaution has been taken in the prepara-

tion of this book, the publisher and author assume no responsibil-

ity for errors or omissions. Nor is any liability assumed for

damages resulting from the use of the information contained

herein.

International Standard Book Number: 0-7897-3129-0

Library of Congress Catalog Card Number: 2004102247

Printed in the United States of America

First Printing: April 2004

06 05 04 03

4321

Trademarks

All terms mentioned in this book that are known to be trademarks

or service marks have been appropriately capitalized. Sams

Publishing cannot attest to the accuracy of this information. Use

of a term in this book should not be regarded as affecting the

validity of any trademark or service mark.

Warning and Disclaimer

Every effort has been made to make this book as complete and as

accurate as possible, but no warranty or fitness is implied. The

information provided is on an “as is” basis. The authors and the

publisher shall have neither liability nor responsibility to any per-

son or entity with respect to any loss or damages arising from the

information contained in this book.

Bulk Sales

Sams Publishing offers excellent discounts on this book when

ordered in quantity for bulk purchases or special sales. For more

information, please contact

U.S. Corporate and Government Sales

1-800-382-3419

corpsales@pearsontechgroup.com

For sales outside of the U.S., please contact

International Sales

1-317-428-3341

international@pearsontechgroup.com

2004 by Sams Publishing

CONTENTS

III

Contents

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1

Getting Results with VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1

What Is in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

Getting Up the Learning Curve . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

Excel VBA Power . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

The Techie Stuff Needed to Produce Applications for Others . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

Will This Book Teach Excel? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4

A Brief History of Spreadsheets and Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4

The Future of VBA and Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5

Special Elements and Typographical Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6

Next Steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7

I FIRST STEPS UP THE VBA LEARNING CURVE

1 Unleash the Power of Excel with VBA! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11

The Power of Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11

Barriers to Entry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11

The Macro Recorder Doesn’t Work! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11

Visual Basic Is Not Like BASIC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12

The Good News—It Is Easy to Climb the Learning Curve . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12

The Great News—Excel with VBA Is Worth the Effort . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12

Knowing Your Tools—The Visual Basic Toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13

Macro Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14

Very High Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14

High Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15

Medium Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15

Low Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15

Overview of Recording, Storing, and Running a Macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15

Filling Out the Record Macro Dialog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15

Running a Macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .16

Creating a Macro Button . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .16

Assigning a Macro to a Form Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .17

Understanding the Visual Basic Editor (VBE) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18

VBE Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18

The Project Explorer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .19

The Properties Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .20

VBA and Macros for Microsoft Excel

Examining Code in the Programming Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22

Running the Macro on Another Day Produces Undesired Results . . . . . . . . . . . . . . . . . . . . . . . . . .24

A Possible Solution: Using Relative References when Recording . . . . . . . . . . . . . . . . . . . . . . .25

Frustration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .28

Next Steps: Learning VBA Is the Solution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .28

2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar? . . . . . . . . . . . . . . . . .29

I Can’t Understand This Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29

Understanding the Parts of VBA “Speech” . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30

Is VBA Really This Hard? No! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .33

VBA Help Files—Using F1 to Find Anything . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .33

Using Help Topics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35

Examining Recorded Macro Code—Using the VB Editor and Help . . . . . . . . . . . . . . . . . . . . . . . . .36

Optional Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37

Defined Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37

Properties Can Return Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41

Using Debugging Tools to Figure Out Recorded Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42

Stepping Through Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42

More Debugging Options—Breakpoints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44

Backing Up or Moving Forward in Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45

Not Stepping Through Each Line of Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45

Querying Anything While Stepping Through Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45

Using a Watch to Set a Breakpoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .50

Using a Watch on an Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .50

The Ultimate Reference to All Objects, Methods, Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51

Five Easy Tips for Cleaning Up Recorded Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53

Tip 1: Don’t Select Anything . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53

Tip 2: Ride the Range from the Bottom to Find Last Row . . . . . . . . . . . . . . . . . . . . . . . . . . . . .54

Tip 3: Use Variables to Avoid Hard-coding Rows and Formulas . . . . . . . . . . . . . . . . . . . . . . . . .55

Tip 4: Learn to Copy and Paste in a Single Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .55

Tip 5: Use With...End With If You Are Performing Multiple Actions to the Same

Cell or Range of Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56

Putting It All Together—Fixing the Recorded Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56

Next Steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59

Que - VBA and Macros for Microsoft Excel {Business Solution2.pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: