Table of Content

Table of Content ii

Preface viii

Preface to the Second Edition viii

The Book's Audience x

Organization of This Book x

The Book's Text and Sample Code xi

About the Code xi

Conventions in this Book xii

Obtaining the Sample Programs xiii

How to Contact Us xiii

Acknowledgments xiii

Chapter 1. Introduction 1

1.1 Selecting Special Cells 1

1.2 Setting a Chart's Data Point Labels 2

1.3 Topics in Learning Excel Programming 4

Part I: The VBA Environment 6

Chapter 2. Preliminaries 7

2.1 What Is a Programming Language? 7

2.2 Programming Style 8

Chapter 3. The Visual Basic Editor, Part 1 13

3.1 The Project Window 13

3.2 The Properties Window 17

3.3 The Code Window 18

3.4 The Immediate Window 20

3.5 Arranging Windows 21

Chapter 4. The Visual Basic Editor, Part II 23

4.1 Navigating the IDE 23

4.2 Getting Help 25

4.3 Creating a Procedure 25

4.4 Run Time, Design Time, and Break Mode 26

4.5 Errors 27

4.6 Debugging 30

4.7 Macros 35

Part II: The VBA Programming Language 38

Chapter 5. Variables, Data Types, and Constants 39

5.1 Comments 39

5.2 Line Continuation 39

5.3 Constants 39

5.4 Variables and Data Types 42

5.5 VBA Operators 57

Chapter 6. Functions and Subroutines 59

6.1 Calling Functions 59

6.2 Calling Subroutines 60

6.3 Parameters and Arguments 61

6.4 Exiting a Procedure 65

6.5 Public and Private Procedures 65

6.6 Project References 65

Chapter 7. Built-in Functions and Statements 67

7.1 The MsgBox Function 68

7.2 The InputBox Function 69

7.3 VBA String Functions 70

7.4 Miscellaneous Functions and Statements 74

7.5 Handling Errors in Code 77

Chapter 8. Control Statements 81

8.1 The If...Then Statement 81

8.2 The For Loop 81

8.3 The For Each Loop 83

8.4 The Do Loop 84

8.5 The Select Case Statement 85

8.6 A Final Note on VBA 86

Part III: Excel Applications and the Excel Object Model 88

Chapter 9. Object Models 89

9.1 Objects, Properties, and Methods 89

9.2 Collection Objects 90

9.3 Object Model Hierarchies 92

9.4 Object Model Syntax 93

9.5 Object Variables 94

Chapter 10. Excel Applications 100

10.1 Providing Access to an Application's Features 100

10.2 Where to Store an Application 103

10.3 An Example Add-In 110

Chapter 11. Excel Events 113

11.1 The EnableEvents Property 113

11.2 Events and the Excel Object Model 113

11.3 Accessing an Event Procedure 113

11.4 Worksheet Events 114

11.5 WorkBook Events 115

11.6 Chart Events 116

11.7 Application Events 116

11.8 QueryTable Refresh Events 118

Chapter 12. Custom Menus and Toolbars 119

12.1 Menus and Toolbars: An Overview 119

12.2 The CommandBars Collection 121

12.3 Creating a New Menu Bar or Toolbar 123

12.4 Command-Bar Controls 124

12.5 Built-in Command-Bar-Control IDs 125

12.6 Example: Creating a Menu 128

12.7 Example: Creating a Toolbar 129

12.8 Example: Adding an Item to an Existing Menu 131

12.9 Augmenting the SRXUtils Application 131

Chapter 13. Built-In Dialog Boxes 139

13.1 The Show Method 141

Chapter 14. Custom Dialog Boxes 143

14.1 What Is a UserForm Object? 143

14.2 Creating a UserForm Object 143

14.3 ActiveX Controls 144

14.4 Adding UserForm Code 145

14.5 Excel's Standard Controls 146

14.6 Example: The ActivateSheet Utility 147

14.7 ActiveX Controls on Worksheets 152

Chapter 15. The Excel Object Model 157

15.1 A Perspective on the Excel Object Model 157

15.2 Excel Enums 159

15.3 The VBA Object Browser 161

Chapter 16. The Application Object 163

16.1 Properties and Methods of the Application Object 165

16.2 Children of the Application Object 189

Chapter 17. The Workbook Object 194

17.1 The Workbooks Collection 194

17.2 The Workbook Object 199

17.3 Children of the Workbook Object 206

17.4 Example: Sorting Sheets in a Workbook 208

Chapter 18. The Worksheet Object 211

18.1 Properties and Methods of the Worksheet Object 211

18.2 Children of the Worksheet Object 219

18.3 Protection in Excel XP 222

18.4 Example: Printing Sheets 224

Chapter 19. The Range Object 229

19.1 The Range Object as a Collection 230

19.2 Defining a Range Object 231

19.3 Additional Members of the Range Object 237

19.4 Children of the Range Object 266

19.5 Example: Getting the Used Range 279

19.6 Example: Selecting Special Cells 280

Chapter 20. Pivot Tables 291

20.1 Pivot Tables 291

20.2 The PivotTable Wizard 293

20.3 The PivotTableWizard Method 296

20.4 The PivotTable Object 298

20.5 Properties and Methods of the PivotTable Object 303

20.6 Children of the PivotTable Object 317

20.7 The PivotField Object 317

20.8 The PivotCache Object 333

20.9 The PivotItem Object 334

20.10 PivotCell and PivotItemList Objects 338

20.11 Calculated Items and Calculated Fields 342

20.12 Example: Printing Pivot Tables 345

Chapter 21. The Chart Object 349

21.1 Chart Objects and ChartObject Objects 349

21.2 Creating a Chart 350

21.3 Chart Types 356

21.4 Children of the Chart Object 359

21.5 The Axes Collection 360

21.6 The Axis Object 363

21.7 The ChartArea Object 373

21.8 The ChartGroup Object 374

21.9 The ChartTitle Object 378

21.10 The DataTable Object 378

21.11 The Floor Object 379

21.12 The Legend Object 379

21.13 The PageSetup Object 381

21.14 The PlotArea Object 381

21.15 The Series Object 382

21.16 Properties and Methods of the Chart Object 388

21.17 Example: Scrolling Through Chart Types 392

21.18 Example: Printing Embedded Charts 395

21.19 Example: Setting Data Series Labels 399

Chapter 22. Smart Tags 407

22.1 What Are Smart Tags? 407

22.2 SmartTagRecognizer Object 408

22.3 SmartTag Object 408

22.4 SmartTagAction Object 409

22.5 SmartTagOptions Object 410

Part IV: Appendixes 411

Appendix A. The Shape Object 412

A.1 What Is the Shape Object? 412

A.2 Z-Order 412

A.3 Creating Shapes 413

A.4 Diagram, DiagramNode, and DiagramNodeChildren Objects 420

Appendix B. Getting the Installed Printers 423

Appendix C. Command Bar Controls 426

C.1 Built-in Command-Bar Controls 426

Appendix D. Face IDs 444

Appendix E. Programming Excelfrom Another Application 450

E.1 Setting a Reference to the Excel Object Model 450

E.2 Getting a Reference to the Excel Application Object 450

Appendix F. High-Level and Low-Level Languages 454

F.1 BASIC 455

F.2 Visual Basic 456

F.5 Pascal 459

F.6 FORTRAN 460

F.7 COBOL 460

F.8 LISP 461

Appendix G. New Objects in Excel XP 463

AllowEditRange Object 463

AutoRecover Obj ect 463

CalculatedMember Object 464

CellFormat Object 464

CustomProperty Object 465

Diagram, DiagramNode and DiagramNodeChildren Objects 465

Error Object 466

ErrorCheckingOptions Object 468

Graphic Object 468

IRTDServer and IRTDUpdateEvent Objects 469

PivotCell and PivotItemList Objects 469

Protection Object 470

RTD Object 470

SmartTag Related Objects 471

Speech Object 471

SpellingOptions Object 473

Tab Object 473

UsedObjects Object 473

UserAccessList andUserAccess Objects 474

Watch Object 474

Colophon 476

Copyright © 2002, 1999 O'Reilly & Associates, Inc. All rights reserved. Originally published under the title Writing Excel Macros. Printed in the United States of America.

Published by O'Reilly & Associates, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472.

O'Reilly & Associates books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://safari.oreilly.com). For more information contact our corporate/institutional sales department: 800-998-9938 or [email protected] com.

Nutshell Handbook, the Nutshell Handbook logo, and the O'Reilly logo are registered trademarks of O'Reilly & Associates, Inc. Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and O'Reilly & Associates, Inc. was aware of a trademark claim, the designations have been printed in caps or initial caps. The association between the image of a blue jay and the topic of Excel macros is a trademark of O'Reilly & Associates, Inc.

While every precaution has been taken in the preparation of this book, the publisher and the author assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein.

0 0

Post a comment