[Office.Books].Visual.Basic.For.Application.Unleashed.pdf

Paul McFedries

201 West 103rd Street

Indianapolis, IN 46290

Visual Basic for Applications

U NLEASHED

FIRST EDITION

retrieval system, or transmitted by any means, electronic, mechanical,

photocopying, recording, or otherwise, without 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 preparation of this book, the publisher and author assume no responsi-

bility for errors or omissions. Neither is any liability assumed for damages

resulting from the use of the information contained herein. For informa-

tion, address Sams Publishing, 201 W. 103rd St., Indianapolis, IN 46290.

International Standard Book Number: 0-672-31046-5

Library of Congress Catalog Card Number: 96-72008

2000 99 98 97 4 3 2 1

Interpretation of the printing code: the rightmost double-digit number is

the year of the book’s printing; the rightmost single-digit, the number of

the book’s printing. For example, a printing code of 97-1 shows that the

first printing of the book occurred in 1997.

Composed in AGaramond and MCPdigital by Macmillan Computer

Publishing

Printed in the United States of America

Acquisitions Editor

Dean Miller

Development Editor

Brian-Kent Proffitt

Software Development Specialists

Patricia J. Brooks

John Warriner

Production Editor

Gayle L. Johnson

Copy Editor

Bart Reed

Indexers

Bruce Clingaman

Erika Millen

Technical Reviewer

John Charlesworth

Formatter

Katie Wise

Technical Edit Coordinator

Lynette Quinn

Editorial Assistants

Carol Ackerman

Andi Richter

Rhonda Tinch-Mize

Cover Designer

Jason Grisham

Book Designer

Gary Adair

Production Team Supervisors

Brad Chinn

Charlotte Clapp

Production

Georgiana Briggs

Shawn Ring

Janet Seib

Mary Ellen Stephenson

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.

Publisher and President Richard K. Swadley

Publishing Manager Dean Miller

Director of Editorial Services Cindy Morrow

Managing Editor Mary Inderstrodt

Director of Marketing Kelli S. Spencer

Assistant Marketing Managers Kristina Perry

Rachel Wolfe

For Karen (purr)

Overview

iii

Introduction xxvi

Part I Unleashing the VBA Programming Language

1 Introducing VBA 3

2 Understanding Variables 39

3 Building VBA Expressions 59

4 Working with Objects 93

5 Controlling Your VBA Code 115

Part II Unleashing Microsoft Office Objects

6 Word for Windows VBA Programming 139

7 Manipulating Excel with VBA 171

8 Programming PowerPoint Presentations 217

9 VBA and Access 247

Part III Unleashing VBA User Interface Design

10 Interacting with the User 277

11 Working with Microsoft Forms 295

12 Creating Custom Menus and Toolbars 333

Part IV Unleashing Application Integration

13 Working with Other Applications 359

14 Programming OLE and ActiveX Objects 381

15 Controlling Applications Via OLE Automation 409

16 Rolling Your Own Objects with Class Modules 423

Part V Unleashing VBA Database Programming

17 Using VBA to Work with Excel Lists 445

18 Programming Data Access Objects 469

19 Advanced Database Programming 493

Part VI Unleashing VBA Internet and Intranet Programming

20 Internet and Intranet Programming Topics 535

21 Web Page Programming: ActiveX and VBScript 567

22 E-Mail and Groupware Programming with Outlook 603

Visual Basic for Applications

U NLEASHED

Part VII Unleashing Advanced VBA Programming

23 Trapping Program Errors 653

24 Debugging VBA Procedures 669

25 Programming the Windows API 689

26 VBA Tips and Techniques 727

Part VIII Unleashing VBA Applications

27 A Check Book Application 769

28 Making Backups as You Work 799

29 Access and Outlook: E-Mail Merge 833

A VBA Statements 865

B VBA Functions 873

C The Windows ANSI Character Set 883

D An HTML Primer 891

Index 913

Contents

Introduction xxvi

Part I Unleashing the VBA Programming Language

1 Introducing VBA 3

What Is a Macro? ............................................................................. 4

How Does VBA Fit In? ............................................................... 4

The Three Types of Procedures ................................................... 5

Recording a VBA Macro .................................................................. 5

Viewing the Resulting Module .................................................... 7

Editing a Recorded Macro ........................................................... 8

Touring the Visual Basic Editor ....................................................... 8

The Project Explorer .................................................................... 8

The Properties Window ............................................................... 9

The Work Area .......................................................................... 10

Writing Your Own Command Macro ............................................ 11

Creating a New Module ............................................................ 11

Writing a Command Macro ...................................................... 12

Running a VBA Macro .................................................................. 13

Creating User-Defined Functions with VBA .................................. 13

Understanding User-Defined Functions .................................... 14

Writing User-Defined Functions ............................................... 16

Employing User-Defined Functions .......................................... 16

Working with Procedures ............................................................... 17

The Structure of a Procedure ..................................................... 17

Calling a Procedure ................................................................... 18

Public Versus Private Procedures ............................................... 20

Another Method for Inserting a Procedure ................................ 20

Visual Basic Editor Techniques for Easier Coding ..................... 21

Finding Code ............................................................................ 26

Navigating Modules with Bookmarks ........................................ 29

Working with Modules .................................................................. 30

Renaming a Module .................................................................. 30

Exporting a Module ................................................................... 30

Importing a Module .................................................................. 31

Removing a Module .................................................................. 32

Setting Visual Basic Editor Options ............................................... 32

The Editor Tab .......................................................................... 33

The Editor Format Tab ............................................................. 34

The General Tab ....................................................................... 34

The Docking Tab ...................................................................... 35

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: