Microsoft+Office+Programming+Guide+fo

User Manual: Pdf

Open the PDF directly: View PDF PDF.
Page Count: 725

DownloadMicrosoft+Office+Programming+Guide+fo
Open PDF In BrowserView PDF
Microsoft Office
Programming:
A Guide for
Experienced Developers
ROD STEPHENS

APress Media, LLC

nafisspour@bluewin.ch

Microsoft Office Programming: A Guide for Experienced Developers
Copyright © 2003 by Rod Stephens
Originally published by Apress in 2003
All rights reserved. No part of this work may be reproduced or transmitted in any form or by
any means, electronic or mechanical, including photocopying, recording, or by any information
storage or retrieval system, without the prior written permission of the copyright owner and the
publisher.
ISBN 978-1-4302-0795-5 (eBook)
ISBN 978-1-59059-121-5
DOI 10.1007/978-1-4302-0795-5
Trademarked names may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, we use the names only in an editorial fashion and to the
benefit of the trademark owner, with no intention of infringement of the trademark.
Technical Reviewer: John Mueller
Editorial Board: Dan Appleman, Craig Berry, Gary Cornell, Tony Davis, Steven Rycroft,
Julian Skinner, Martin Streicher, Jim Sumser, Karen Watterson, Gavin Wray, John Zukowski
Assistant Publisher: Grace Wong
Project Manager: Sofia Marchant
Copy Editor: Scott Carter
Production Editor: Janet Vail
Proofreader: Lori Bring
Compositor: Diana Van Wmkle, Van Wmkle Design Group
Indexer: Kevin Broccoli
Manufacturing Manager: Tom Debolski
Distributed to the book trade in the United States by Springer-Verlag New York, Inc., 175 Fifth
Avenue, New York, NY 10010 and outside the United States by Springer-Verlag GmbH & Co. KG,
Tiergartenstr. 17, 69112 Heidelberg, Germany.
In the United States: phone 1-800-SPRINGER, email orders@springer -ny. com, or visit
http: I lwww. springer-ny. com. Outside the United States: fax +49 6221 345229, email
orders@springer. de, or visit http: I lwww. springer. de.

The information in this book is distributed on an "as is" basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall
have any liability to any person or entity with respect to any loss or damage caused or alleged to
be caused directly or indirectly by the information contained in this work.

nafisspour@bluewin.ch

For Michelle and Ken

nafisspour@bluewin.ch

Contents at a Glance
About the Author................................................................................................. xv
About the Technical Reviewer .................................................................... xvii
Acknowledgments ...............................................................................................xviii

Chapter

1

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

Chapter

2

Macros ....................................................................................... 13

Chapter 3 Customizing Office ........................................................ 55
Chapter 4 Automatic Customization ........................................... 87
Chapter 5 Office Programming the Easy Way: OL£ ........ 141
Chapter 6 Introduction to Office XP Object Models ...223
Chapter

7

Word ..........................................................................................261

Chapter 8 Excel ........................................................................................ 343
Chapter 9 PowerPoint ........................................................................... 411
Chapter 10 Access ...................................................................................... 469
Chapter

11

Access and ADO .................................................................523

Chapter 12 Outlook .................................................................................. 555
Chapter 13 Outlook) MAPI) and CDO ............................................ 591
Chapter 14 Smart Tags ........................................................................... 611
Chapter 15 Office 2003 ........................................................................ 637
Index .................................................................................................................... 685

v
nafisspour@bluewin.ch

Contents
About the Author................................................................................................. xv
About the Technical Reviewer ....................................................................xvii
Acknowledgments ............................................................................................... xviii

Chapter 1 Introduction ......................................................................... 1
This Book's Approach ......................................................................................... 1
Why You Should Learn Office Programming ............................................... 3
Office Synergy ..................................................................................................... 3
Office Automation .............................................................................................. 4
Macros and More ................................................................................................ 4
The Bigger Picture .............................................................................................. 5
What This Book Covers (and What It Doesn't) ...................................... 6
Intended Audience ............................................................................................... 7
Chapters Overview ............................................................................................... 7
How to Use This Book ......................................................................................... 9
Same Play, Different Playbook ..................................................................... 9
Required Equipment and Software ............................................................... 10
The Book's Web Site ......................................................................................... 11
Contacting the Author ..................................................................................... 12
Summary ................................................................................................................... 12

Chapter

2

Macros ....................................................................................... 13

When Not to Write Macros .............................................................................. 14
One-TimeTasks ................................................................................................ 15
Easy Tasks .......................................................................................................... 16
Reinventing the Wheel ..................................................................................... 17
Transferring Data ............................................................................... .............. 18
Handling Non-Native Data Formats ............................................................... 20
When to Write Macros ....................................................................................... 20
Complex Tasks .............................................................................................. .... 23
Throwaway Macros ......................................................................................... .24
Coding for Other Users ................................................................................ ....26

vii
nafisspour@bluewin.ch

Contents

Writing Macros ....................................................................................................27
Managing Macros .............................................................................................27
Recording Macros ............................................................................................. 30
Guarding Against Macro Viruses ..................................................................... 36
The Visual Basic Editor .................................................................................... 47
Summary ...................................... ,............................................................................53

Chapter 3 Customizing Office ........................................................ 55
Customizing Word .......................... ,.....................................................................56
Keyboard Shortcuts ...................................................... ,................................... 56
Keyboard Confusion ........... ,............................................................................ 58
Menu Items ............................................................................ ,.......................... 58
Customizing Menu Items ............................. ,.................................................. 59
Toolbar Buttons ................................................................................................ 62
Menu Item and Toolbar Button Shortcuts ...................................................... 63
Replacing Standard Word Functions .............................................................. 64
Removing Word Customizations ..................................................................... 69
Customizing Excel ............................................................................................. 70
Keyboard Shortcuts .......................................................................................... 70
Menultems ....................................................................................................... 71
Customizing Menu Items ................................................................................ 72
Toolbar Buttons ................................................................................................ 73
Removing Excel Customizations ..................................................................... 73

Customizing PowerPoint .................................................................................. 73
Keyboard Shortcuts .......................................................................................... 74

Customizing Access ........................................................................................... 74

Access Macros ................................................................................................... 75
Menu Items ....................................................................................................... 78
Customizing Menu Items ................................................................................ 79
Toolbar Buttons ................................................................................................ 81
Keyboard Shortcuts .......................................................................................... 81
Removing Access Customizations .................................................................. 84
Customizing Outlook ......................................................................................... 84
Keyboard Shortcuts .......................................................................................... 85
Removing Outlook Customizations ................................................................ 85
Summary ................................................................................................................... 86

viii
nafisspour@bluewin.ch

Contents

Chapter 4 Automatic Customization ........................................... 87
Office Application Differences ................................................................. 89
Self-Customizing Documents .......................................................................... 90
Word .................................................................................................................. 90
Excel ................................................................................................................ 101
PowerPoint ...................................................................................................... 102
Access .............................................................................................................. 108
Outlook ............................................................................................................ 115
Multiple Self-Customizing Documents .................................................... 117
Disappearing Toolbars ................................................................................... 122
Verifying Document Type .............................................................................. 123
Documents Containing Controls ................................................................. 127
Word ................................................................................................................ 128
Excel ................................................................................................................ 131
PowerPoint ...................................................................................................... 132
Access .............................................................................................................. 133
Outlook ............................................................................................................ 137
When to Use Embedded Controls ................................................................. 139
Summary ................................................................................................................. 140

Chapter 5 Office Programming the Easy Way: OLE ....... 141
What Is OLE? ...................................................................................................... 142
Linking Versus Embedding ............................................................................ 144
OLE in Word, Excel, and PowerPoint ...................................................... 146
Displaylcons .................................................................................................. 146
Embedded Objects ......................................................................................... 147
Linked Objects ................................................................................................ 149
Managing Object Links .................................................................................. 152
Copying and Pasting Excel Data .................................................................... 153
Copying and Pasting PowerPoint Slides ....................................................... 154
Object Sizes ..................................................................................................... 155
OLE in Access .................................................................................................... 157
OLE in Outlook .................................................................................................. 158
Programming OLE Objects .............................................................................. 159
A Word ofWarning .......................................................................................... 160
Programming OLE Objects in Word .............................................................. 161
Programming OLE Objects in Excel .............................................................. 175
Programming OLE Objects in PowerPoint ................................................... 187
Programming OLE Objects in Access ........................................................... 195
Programming OLE Objects in Outlook .........................................................212
Summary .................................................................................................................222

ix
nafisspour@bluewin.ch

Contents

Chapter 6 Introduction to Office XP Object Models ...223
Object Model Basics .......................................................................................223
The Application Class ................................................................................... 224
Same Play, Different Playbook .................................................................225
Object Models Resources ..............................................................................226
Online Help .....................................................................................................226
The Object Browser ........................................................................................229
The Global Object ...........................................................................................230
Do-It-Yourself Object Models ...................................................................232
A Separate Server Qass .................................................................................. 234
Separate Projects ............................................................................................234
A Separate Server Dll ....................................................................................236
A More Realistic Example ..............................................................................237
Server Benefits ................................................................................................238
Do-It-Yourself Scripting ............................................................................240
Cross-Application Development ................................................................. 245
Early Binding ..................................................................................................245
Late Binding .................................................................................................... 249
Picking Early or Late Binding ........................................................................252
Remote Macros ....................................................................................................253
Excel Calling Word ..........................................................................................254
Word Calling PowerPoint ...............................................................................256
PowerPoint Calling Excel ............................................................................... 258
Summary ................................................................................................................. 259

Chapter 7 Word ..........................................................................................261
Overview ...............................................................................................................261
Global Objects .................................................................................................. 262
Application .........................................................................................................264
Application Object Properties and Methods ................................................264
Application Object Events ............................................................................. 294
Document ...............................................................................................................297
Document Object Properties and Methods .................................................297
Document Object Events ...............................................................................326
Selection and Range .......................................................................................327
Object Selection .............................................................................................. 327
Properties ........................................................................................................ 328
Formatting Properties .................................................................................... 329
Movement and Extension ..............................................................................330
Find ........................................................................................................................337
Summary ................................................................................................................. 341

X

nafisspour@bluewin.ch

Contents

Chapter 8

Excel ........................................................................................343

Overview ............................................................................................................... 343
Code Storage ...................................................................................................... 344
Cell Addresses ..................................................................................................344
Al Notation ..................................................................................................... 345
Index Numbers ............................................................................................... 347
RlCl Notation ................................................................................................ 347
Referencing Other Worksheets and Workbooks ........................................... 348
Using Absolute and Relative References ...................................................... 348
Named Ranges ................................................................................................ 349

Application .........................................................................................................351

Properties ........................................................................................................ 352
Methods .......................................................................................................... 366
Events .............................................................................................................. 379

Workbook ...............................................................................................................382
Properties ........................................................................................................ 382
Methods .......................................................................................................... 388
Events .............................................................................................................. 391

Worksheet .............................................................................................................392
Worksheet Properties ..................................................................................... 393
Worksheet Methods ....................................................................................... 399
Worksheet Events ........................................................................................... 401

Range ...................................................................................................................... 401

Activate, Select, and Show ............................................................................. 402
Address ............................................................................................................ 402
Areas (C) .......................................................................................................... 403
Formatting Properties and Methods ........................................................... .403
Item, Cells, Range, and Count ....................................................................... 405
Row and Column Properties .......................................................................... 407
Value Properties .............................................................................................. 408
Clearing Methods ........................................................................................... 409
Fill Methods .................................................................................................... 410
Printing Methods ............................................................................................ 410
Sort .................................................................................................................. 410

Summary ................................................................................................................. 410
Chapter 9

PowerPoint ...........................................................................411

Overview ............................................................................................................... 412
Code Storage ...................................................................................................... 412
Application ......................................................................................................... 414
Properties ........................................................................................................ 415
Methods .......................................................................................................... 420
Events .............................................................................................................. 421
xi
nafisspour@bluewin.ch

Contents

Presentation ...................................................................................................... 422
Properties ........................................................................................................ 422
Methods .......................................................................................................... 435
Slide and SlideRange (C) ............................................................................ 437
Properties ........................................................................................................ 438
Methods .......................................................................................................... 447

Shape and ShapeRange (C) ............................................................................ 450
Properties ........................................................................................................ 450
Methods .......................................................................................................... 462

Selection ............................................................................................................. 464
Summary ................................................................................................................. 467
Chapter 10 Access ......................................................................................469

Overview ............................................................................................................... 471
Application ......................................................................................................... 471
Properties ........................................................................................................ 472
Methods .......................................................................................................... 484

Form ........................................................................................................................ 499
Properties ........................................................................................................ 499
Methods .......................................................................................................... 510

Report ...................................................................................................................510

Properties ........................................................................................................ 511
Methods .......................................................................................................... 517

Module ...................................................................................................................520
Summary .................................................................................................................521
Chapter 11 Access and ADO .................................................................523

Late and Early Binding ................................................................................ 524
Connection ......................................................................................................... 526
The ConnectionString Property .................................................................... 526
Using the ADO Data Control ......................................................................... 526
Using Data Link Files ..................................................................................... 531
An Example ..................................................................................................... 532

Recordset .............................................................................................................535
Field ......................................................................................................................540
Command .................................................................................................................541
SQL ..........................................................................................................................544

SELECT ............................................................................................................ 545
Joins ................................................................................................................. 547
INSERT ............................................................................................................ 548
UPDATE ..........................................................................................................549
DELETE ...........................................................................................................550
xii
nafisspour@bluewin.ch

Contents

Read-Only Databases .......................................................................................551
Database Construction ...................................................................................552
Summary ................................................................................................................. 553

Chapter 12 Outlook .................................................................................. 555
The MAPI Namespace .........................................................................................556
Outlook Items and the Items Collection ..............................................556
Mailltem .......................................................................................................... 557
Contactltem .................................................................................................... 560
Appointmentltem ........................................................................................... 561
Taskltem .......................................................................................................... 564
Application .........................................................................................................565
AdvancedSearch ............................................................................................. 565
CopyFile .......................................................................................................... 569
Createltem and ItemsAdd ............................................................................. 570
GetNameSpace ............................................................................................... 572
Quit .................................................................................................................. 573
Explorers (C) and Inspectors (C) ................................................................... 574
MAPIFolder ...........................................................................................................575
Items (C) ............................................................................................................. 577
Selection (C) .................................................................................................... 579
Views (C) .............................................................................................................580
AddressLists (C) .............................................................................................. 583
Actions (C) and Action ................................................................................584
Attachments (C) ................................................................................................586

Recipients (C) .................................................................................................. 587
RecurrencePattern ...........................................................................................588
Reminders (C) .................................................................................................... 589
Summary .................................................................................................................590

Chapter 13 Outlook, MAPI, and CDO ............................................591
ShellExecute ......................................................................................................591

coo ..........................................................................................................................595

Understanding the CDO Object Model ........................................................ 596
Sending Mail ................................................................................................... 597
SendingAttachments ..................................................................................... 601
Reading Mail ................................................................................................... 602
Deleting Mail .................................................................................................. 603
Finding Folders ............................................................................................... 605
Summary ................................................................................................................. 610

xiii
nafisspour@bluewin.ch

Contents

Chapter 14 Smart Tags ........................................................................... 611
Smart Tags in Different Applications .................................................. 612
Smart Tag Lists ................................................................................................ 614
Smart Tags with Visual Basic .NET ........................................................ 616
Start a New Project ......................................................................................... 618
Build the Recognizer Class ............................................................................ 620
Build the Action Class .................................................................................... 627
Register the Classes ........................................................................................ 634
Test the DLL .................................................................................................... 635
Summary ................................................................................................................. 636

Chapter 15 Office 2003 ........................................................................ 637
Introduction to XML ....................................................................................... 638
Word Tools for Manipulating XML ............................................................. 641
The XMLNode Object ..................................................................................... 642
The Document Object ................................................................................... 651
The XML Method ............................................................................................ 651
Excel Tools for Manipulating XML ........................................................... 652
Excel List Tools ............................................................................................ 657
Smart Tag Enhancements ................................................................................ 662
Visual Studio .NET Tools for Office ................................................... 667
Start a Project .................................................................................................. 668
Set Additional Security ................................................................................... 672
Prepare the Word Document ......................................................................... 676
Customize the Code ....................................................................................... 677
Test the Code .................................................................................................. 678
Ponder the Results .......................................................................................... 679
InfoPath .............................................................................................................. 679
Web Services Support ..................................................................................... 683
Smart Documents ................................................................................................ 683
Summary ................................................................................................................. 684

Index .................................................................................................................... 685

xiv
nafisspour@bluewin.ch

About the Author
In a previous incarnation, Rod Stephens was a mathematician.
During his stint at MIT, he discovered the joys of algorithms
and graphics, and has been programming professionally ever
since. During his career he has worked on an eclectic assortment of applications spanning such topics as repair dispatch,
telephone switch programming, tax processing, and training
for professional football players.
Rod has written more than a dozen books that have been
translated into half a dozen different languages, and more than 200 magazine articles covering Visual Basic, Visual Basic for Applications, Delphi, and Java. He is currently a columnist for Hardcore Visual Basic (http: //w. hardcorevisualbasic. com).
Rod's popular Web site, VB Helper (http ://www . vb- helper . com) receives several
million hits per month and contains more than a thousand pages of tips, tricks,
and example code for Visual Basic programmers. The site also contains example
code for this book.

XV

nafisspour@bluewin.ch

About the
Technical Reviewer
John Mueller owns DataCon Services and works as a consultant
to small- and medium-sized companies. He is also a freelance
author and technical editor. The 61 books and more than 200
articles he has written treat topics ranging from networking
to artificial intelligence, and from database management to
heads-down programming. Some of his recent books include
several C# developer guides, an accessible programming guide,
a book on Web Matrix, and several Windows XP user guides. His
technical editing skills have helped more than 33 authors refine their manuscripts.
John has provided technical editing services to both Data Based Advisor and Coast
Compute magazines. He has contributed articles to a number of magazines, including InformiT, SQL Server Professional, Visual C++ Developer, Visual Basic Developer,
and Hardcore Visual Basic. He is currently the editor of the .NET electronic newsletter for Pinnacle Publishing (http: I IWVM. freeenewsletters. com!}.
Contact John at JMueller@mwt. net or visit his Web site at http: I IWVM. mwt. net!
Njmueller.

xvii
nafisspour@bluewin.ch

Acknowledgments
THANKS TO Sofia Marchant, John Mueller, Julian Skinner, Scott Carter, and everyone

else who helped assemble this book. Special thanks to Karen Watterson, who's
been both editor and friend since I started writing.

xviii
nafisspour@bluewin.ch

CHAPTER

1

Introduction
THE MICROSOFI' OFFicE applications are powerful tools for performing everyday
office chores such as formatting documents, analyzing data with spreadsheets,
and making presentations to justify your salary increase, even though your current
project is failing. These applications are also useful for building less work-oriented
solutions such as movie databases, checkbook applications, and penny-stock
price forecasters.
Although the Office applications are powerful, they have their limitations.
Their features are generally applicable to a wide variety of situations, but they cannot anticipate all your specific needs. If you want to use polynomial curve fitting
to predict future sales, Excel can do that. If you want to add numerology and horoscopes to your predictions, you're pretty much on your own.
Fortunately, the Microsoft Office applications include a powerful programming language for extending the applications' functionality: VBA (Visual Basic for
Applications). UsingVBA, you can automate simple repetitive tasks that you would
otherwise need to perform manually. VBA includes a full set of looping statements
(For, Do While, Loop Until) and control statements (If Then, Else, Select Case) so
you can tackle much tougher problems. If you really want to use numerology in
your sales forecasts, you can use VBA to do it.
As its title implies, this book is about programming the Microsoft Office XP
and Microsoft Office 2003 applications. It tells how you can write everything from
simple macros to full-blown applications that interact with several different Office
programs, the user, and the operating system. It also explains other methods for
making Office applications work together, such as OLE (Object Linking and
Embedding).
The book tells when to use each of these techniques and, perhaps more importantly, when not to use them. For example, you could write a macro that capitalizes
the first letter of every word in a Word document. Word already has a command for
this function, however, so writing your own would be a waste of time.

This Book's Approach
If you scour the bookstores and the Web, you'll discover several books that cover
Office programming for a particular application. For example, you'll find books
about programming Excel, other books that tell how to program Word, and so
1
nafisspour@bluewin.ch

Chapter 1

forth. You'll even find a few books that talk about VBA programming in general
without becoming bogged down in the details of any particular Office application.
Unfortunately, such books assume that you know nothing about Visual Basic
(many assume you're a total moron) , so they all start at the absolute beginning.
They tell you what a variable is, how to declare a variable, how to build a For loop,
what an If statement is, and so forth.
If you already know how to use VBA in any Office application, or if you know
how to use VB Script or Visual Basic, all this material is wasted. If you already know
how to program Word and you want to learn how to program Excel, you get to
learn all about comments, variable declarations, subroutines, and the rest all over
again. You'll probably buy a 600-page book, skip 450 pages that explain arrays and
subroutine declarations, and barely get 150 pages of information out of what's left.
Then, if you want to learn about PowerPoint programming, you get to do it all over
again.

CAUTION Ifyou don't already know how to use VBA, Visual Basic, or VBScript,
you may find this book a harsh introduction. Walk over an aisle or two (or
start a new search ifyou're buying online) and pick up an introductory Visual
Basic book. Even a fast read through will make this book easier to follow.

This book is a no-nonsense guide to programming Microsoft Office XP and
Microsoft Office 2003. It assumes you already know the basics ofVisual Basic or
VBA programming and don't want to waste time reading about variable declarations again. The goal is to provide as much information about programming each
of the Microsoft Office applications as quickly as possible without rehashing the
same old material.
The way you use VBA is very similar for all Office applications. Once you
understand the basics, you don't need to relearn the same material for each application. The two main things you must learn about a new Office application are its
features and its object model. The application's features let you know what's possible. If you don't know that an application can do something, you can't use that feature in your applications.
An application's object model is the collection of objects and methods it
exposes for use in your programs. These determine what you can do programmatically with the application. Fortunately, the Microsoft developers included just
about everything they could think of in the Office object models, so you can do
programmatically pretty much anything you can do interactively and more.
Once you understand the application's features and object model, you are
ready to program that application. The early chapters in this book describe general

2
nafisspour@bluewin.ch

Introduction

approaches to Office programming. Later chapters concentrate on application
features and their object models.

Why You Should Learn Office Programming
Individually, the Microsoft Office applications are powerful but incomplete. Word
lets you build nicely formatted documents. Excel lets you analyze data using powerful spreadsheets, formulas, and graphs. PowerPoint lets you thrill and amaze
your management with presentations containing annoying animations and irritating sound effects. Access can build a database for tracking bug reports that are
never cleared, and Outlook lets you schedule the thirteen weekly progress meetings where you must explain why you aren't getting any work done.
Each of these products is intended for a specific purpose; if you stray too far
off course, you can get into trouble. For example, Word is designed to format documents, not to analyze data. Word's table commands let you calculate simple
totals and averages, but they are much less flexible than Excel's formulas. Sure you
could write some code to calculate standard deviations and hyperbolic cosines,
but if you really need those types of functions, you can just use Excel.
Similarly, you could probably coerce Excel or PowerPoint into formatting a
text document, but why should you? Word provides a lot more features for wrapping lines and paragraphs, controlling orphan and widow words, indexing, building tables of contents, and performing other typographic tasks. Using Excel to
format a text document would be like opening a can of soup with a rock: If you
beat on it long enough, it will probably work, but you may not like the result, and
you probably won't be eager to repeat the experience. It's better to use the appropriate tool for each task.
The following sections describe some of the advantages Office programming
offers. They explain in general terms how you can use Office applications together
to build powerful integrated tools for handling common workplace scenarios. The
rest of the book provides more detail.

Office Synergy
Although individual Office applications have limitations, together they can form
incredibly powerful combinations. Using OLE, you can include features from one
application inside another. For example, you can place an Excel spreadsheet
inside a Word document or PowerPoint presentation.
You can include these objects either as links to the original file (so the linked
view is updated whenever the original changes) or as copies of original files.

3
nafisspour@bluewin.ch

Chapter 1

You can even double-click on some of these objects to open them using their
original application. For example, suppose you include a copy of an Excel spreadsheet in a Word document. If you double-click on the spreadsheet, Word opens the
spreadsheet using Excel. You can modify the data and close Excel to update the
copy in Word.
With no programming whatsoever, you can use OLE to combine features of
more than one Office application. You can consider this as programming the
cheap-and-easy way.

NOTE You can control OLE programmatically, too. You can use VBA to build
and manipulate OLE objects at run time.

Unfortunately, OLE is a rather heavy-handed solution. To use a Word document in Excel, Excel must essentially load Word and all its user interface paraphernalia. If you want to examine the document programmatically but don't need to
display it to the user, you can cut out some overhead by using Office automation.

Office Automation
Microsoft has gone out of its way to make the Office applications "automation
servers." An automation server is an application that lets other applications use it
programmatically. It provides services for automating whatever tasks the server
usually performs.
For example, a Visual Basic program can use a Word server to manipulate
Word documents. Similarly, a VBA macro in Word can open an Excel spreadsheet
and copy data into the spreadsheet's cells. Or a PowerPoint macro can open an
Access database and extract data to use for building slides.
A single VBA program could open Access to get a customer list, open Word to
print mailing labels for the customers, use Word again to print form letters, and
use Outlook to send e-mail messages to the customers to say the form letters were
on their way.

Macros and More
All Office applications use VBA as a macro programming language. (Before you
curl your lip back in a haughty sneer, be aware that "real programmers" do write

4
nafisspour@bluewin.ch

Introduction

macros. Real programmers write in every language they can get their hands on.
And today's macros are a lot more sophisticated than the original Lotus macros!)
UsingVBA, you can automate simple tasks that you might normally perform
manually. For example, you could write a verbosifier that translates your ordinary
project status documents into manager-speak. It would replace words like for with
phrases like for the purpose of, to with in order to, and manager with annoyance.
You could do all this by hand but wrapping the steps into a macro would make the
process easier.
Macro recorders in most Office applications make it easier to build this kind
of macro that combines several simple but tedious tasks. Just turn recording on,
make a few substitutions, and turn recording off. Now you can replay the recorded
macro with another document to make the same set of substitutions.

TIP You can examine the macro's code to see how it works, then use it as a

basis for your own macros.

VBA can automate simple tasks, but it can also perform much more complex
functions. VBA is a powerful programming language comparable to Visual Basic,
so you can build almost any application you can describe. A VBA program can
prompt the user for data, interact with the Windows operating system, upload and
download files from the Internet, and even track your stock portfolio in real time.

The Bigger Picture
I once met a guy who was an Emacs Lisp programmer. Emacs is a word processor
that generally runs on Unix systems, and it uses Lisp as a macro programming language. That's all this guy did. He wrote macros for Emacs. He could write a pretty
fancy macro, and you can find hundreds of Emacs Lisp programs on the Web
(chess, hangman, music editors, abacus, interface to French radio, astronomical
calculators, you name it).
However, that's hardly enough to fill a resume. This guy wouldn't stoop to using
a "lesser" language such as C, C++, Pascal, Fortran, or even non-Emacs flavors of
Lisp. A description ofVisual Basic probably would have sent him into uncontrollable fits oflaughter. He was happy in his tiny little universe, and that's where he
wanted to stay. Since I met him, this guy has probably been dragged kicking and
screaming from his lab and into the real world. Now he's probably a Java programmer trying to avoid learning too much about ASP (Active Server Pages).

5
nafisspour@bluewin.ch

Chapter 1

Emacs Lisp and VBA are both macro languages, but VBA fits into a much larger
picture. For starters, VBA is the macro language for several applications (Word,
Excel, PowerPoint, and so forth). If you know how to program one, you know the
basics of programming them all.
Next, VBA is a large subset ofVisual Basic, so if you knowVBA, you almost
know Visual Basic. Similarly, VBScript (used for ASP) is a subset ofVBA, so if you
know VBA, you practically knowVBScript and ASP programming. You'll need to
pick up some details to go from being a Word programmer to an ASP Web developer, but it will be a lot easier than moving from Emacs Lisp to Java or C#. And
wouldn't you prefer to have VB, VBA, VBScript, Word, Excel, Access, PowerPoint,
and Outlook programming all on your resume rather than the fact that you wrote
an abacus in Lisp?

What This Book Covers (and What It Doesn't)
This book explains how to use VBA to manipulate the Microsoft Office XP and
Microsoft Office 2003 applications. It does not explain everything there is to know
about using each of the different Office applications. It explains some of the applications' more interesting features so you can see how to use them in VBA code, but
it doesn't cover every last nook and cranny. Entire books have been written about
using these applications, and you should look to those books for that information.
For example, the Step by Step series published by Microsoft Press is fairly popular
(that series includes Microsoft Word Version 2002 Step by Step, Microsoft Excel
Version 2002 Step by Step, Microsoft PowerPoint Version 2002 Step by Step, Microsoft
Access Version 2002 Step by Step, and Microsoft Outlook Version 2002 Step by Step).
For Word, Excel, and PowerPoint, this shouldn't be a big deal. Once you know
how to display text in italicsusingVBAcode (Selection. Font. Italic = True), you
should be able to figure out how to display text with strikethreugh on your own

(Selection.Font.StrikeThrough = True).
On the other hand, Access and Outlook are much more than document editors in the way Word, Excel, and PowerPoint are. Access and Outlook are powerful
programming environments in their own rights. Using Access, you can build forms
and reports for entering, viewing, editing, and examining data in a database.
Access provides its own kinds of controls, macros, and other methods for manipulating data on these forms and reports.
Similarly, Outlook provides its own tools for manipulating the objects it manages. It lets you build forms for creating specific kinds of e-mail messages
(expense reports, purchase requisitions, recipes, and whatnot), calendar entries
(project meetings, performance reviews), tasks, folders, and so forth.
This book touches lightly on these subjects to provide the context you need to
manipulate these programs usingVBA It doesn't go into nearly the depth you would
6
nafisspour@bluewin.ch

Introduction

need to take full advantage of the Office applications. It explains how to use Outlook
VBA to create a project meeting appointment that happens everyThesday at 4:00,
but to make Outlook do everything it possibly can, you'll need to look elsewhere.

Intended Audience
This is not a beginner's book. If you have never programmed before, you may want
to pick up a more general Visual Basic or VBA programming book before you read
this one. For example, you might take a look at Programming Microsoft Visual
Basic 6.0 by Francesco Balena (Microsoft Press, 1999) or Microsoft Visual Basic 6.0
Professional Step by Step by Michael Halvorson (Microsoft Press, 2002). You don't
need to know Visual Basic in excruciating detail, however. If you know how to
declare variables, write a For loop, use an If statement, and use a control's properties and methods, then you should have no trouble with this book.

CAUTION When you go shopping for an introductory Visual Basic book, be
sure you get a book about Visual Basic 6 (or possibly Visual Basic 5). Don't buy
a VB .NET book, or you'll also get a wealth of confusion at no extra cost. VBA
in Microsoft Office is based on Visual Basic 6. VB .NET, the next generation in
the Visual Basic evolution, is extremely different. Its version will undoubtedly
be coming to VBA soon, but not in Office XP or Office 2003.

If you have experience programming in some other language, such as C++ or
Delphi, you can probably work through this book with only a little difficulty. If you
understand the concepts of declaring variables, writing loops, and using If statements, then you should have no trouble following this book. Some details will be
different from the language you're used to, but the ideas will be familiar.

Chapters Overview
Chapter 2, "Macros," explains fundamental concepts you should understand to
program the Office applications. It explains the general structure of those applications and how they use VBA as a macro language. It shows how to use the macro
recorder to quickly generate simple macros (except in Outlook, which doesn't have
a macro recorder).
After you've written a macro, you need some way to execute it. Chapter 3,
"Customizing Office," explains different ways you can run a macro or make it

7
nafisspour@bluewin.ch

Chapter 1

easily available to others who need to use your code. It tells how to tie macros to
custom toolbar buttons and menu items.
Chapter 4, "Automatic Customization," explains how to make documents that
install and remove their own customizations. Depending on your particular needs,
some Office applications provide ways to do this automatically. However, the
examples in this chapter demonstrate more general techniques you can use to
perform actions when the user opens and closes a document.
Chapter 5, "Office Programming the Easy Way: OLE," explains how you can
use OLE to avoid some possibly onerous programming chores. Briefly described
earlier in this chapter, OLE lets you include the features of one Office application
within another. For example, OLE lets you embed an Excel worksheet inside a
Word document.
The rest of the book deals mostly with the various Office applications' object
models. Chapter 6, "Introduction to Office XP Object Models," discusses features
and objects shared by the different Office applications.
Chapters 7 through 10 and Chapter 12 cover the Word, Excel, PowerPoint,
Access, and Outlook object models. They describe the objects, properties, and
methods you will probably find most helpful in building Office applications.
Chapter 11, ·~ccess and ADO," (ActiveXData Objects) explains how you can
use ADO to manipulate Access databases without using Access itself. ADO doesn't
have the user interface that Access provides, so it is a leaner tool for when you
want to manipulate a database behind the scenes.
Just as you can use ADO to work with an Access database without Access, you
can use MAPI (messaging application programming interface) and CDO (Collaboration Data Objects) to perform many Outlook functions without using Outlook.
Chapter 13, "Outlook, MAPI, and CDO," explains how to use MAPI and CDO to
carry out several useful Outlook tasks such as sending and receiving e-mail, and
working with Outlook folders.
Chapter 14, "Smart Tags," explains how you can build smart tags usingVisual
Basic .NET. Smart tags let you add context-sensitive features to your document
text. For example, whenever the user types the word "VBA" in Word, Excel, or
PowerPoint, your smart tag can provide a menu allowing the user to perform
related tasks such as connecting to the MicrosoftVBA Web site, checking a file
server for VBA code snippets, or sending you e-mail.
Chapter 15, "Office 2003," describes developer enhancements provided by
Microsoft Office 2003. These include objects for working with XML embedded in
Word documents and Excel worksheets, smart tag enhancements, tools for integratingVisual Studio .NET with Office 2003, InfoPath, and smart documents,
which take the context-sensitive features of smart tags one step further.

8
nafisspour@bluewin.ch

Introduction

How to Use This Book
Because Chapters 2 through 5 explain fundamental concepts necessary for programming any Office application, you should read those chapters first. This book
assumes that you are experienced with either Office or Visual Basic programming,
so some of this material may be review for you. For example, if you are a very
experienced Word programmer, you may already understand how the Word object
model works, so you may not need a more general discussion of object models.
Even if you have a lot of experience programming any Office application, you may
want to skim these chapters to look for tidbits you don't already know.
Mter you have read Chapters 2 through 5, you can read the others in any order
when you need them. For example, if you need to build a program that automates
some task in Excel, read Chapter 8, "Excel." If you want to write a Word macro that
extracts text and builds a PowerPoint presentation, read Chapter 7, "Word," and
Chapter 9, "PowerPoint."

Same Play, Different Playbook
Although Office applications have much in common, they also have many differences. Certainly, a word processor (Word) and a spreadsheet application (Excel)
behave differently in many ways. Even beneath the surface, the Office applications
show many dissimilarities.
All Office applications provide an Application object that gives a VBA program
access to the application's functionality. They all have a concept of a document (a
manuscript in Word, a workbook in Excel, a presentation in PowerPoint, a database in Access, and "items" in Outlook). Beyond that, however, the ways you can
use each application in a VBA program varies, sometimes widely.
For example, Word allows you to associate code with a document, with the
global template Normal. dot, or with some other global template that you create.
Excel and PowerPoint, on the other hand, only allow you to store code inside a
document (a workbook in Excel or a presentation in PowerPoint). Outlook, which
was the last Office application to come into the VBA fold, is undoubtedly the
strangest, using Outlook forms and VBScript to build applications while still allowing you to use VBA to program the Outlook environment.
Clearly, Microsoft gave the Office applications' development teams some
goals they were all supposed to meet (provide an Application object and an object
model that gives VBA programmers access to the applications' features), but the
different teams came up with slightly different solutions. It's as if they are all working on the same play, but from different playbooks.

9
nafisspour@bluewin.ch

Chapter 1

Or, perhaps more accurately, it's like asking five artists to paint a picture of a
house. Each will produce something that you can recognize as a house, but they'll
differ in the details. They'll probably all have some sort of a roof, walls, doors, and
windows, but they won't be the same house.
Even if the applications used the same approach (where they store VBA code
and so forth), they would still have large, obvious differences. Word and PowerPoint don't generally need the powerful financial functions and equation solvers
that Excel provides. Excel and Word don't really need to record audio and timings
as you step through a presentation one slide at a time.
You could probably combine the features of all of the Office applications, but
the result would be a huge, bloated monster application that tried to be all things
to all people. That would probably be a marketing disaster and would certainly be
a maintenance nightmare.
The bottom line is that although you can learn a lot about Office programming in general from Chapters 2 through 5, you still must spend some time with
each Office application to learn its foibles and idiosyncrasies. You need to learn
the details of how each application supports VBA and how to use the features and
tools unique to that application.
You'll see this theme repeated throughout the book, particularly in Chapters 2
through 5. The section that explains how to add custom toolbar buttons and menu
items to an Office application points out the most important differences between
the different Office applications.

Required Equipment and Software
This book is about Office XP and Office 2003 programming, so to get the most out
of it, you will need a copy of Office XP or Office 2003. Obviously, you will need a
computer capable of running Microsoft Office, but other than that, you need no
special hardware or software to use this book.
Much of this book focuses on Office XP but most of the material will also work
with Office 2003. Most of the code will also work with earlier versions of Microsoft
Office. Some newer features may not work with older versions of Office, but most
of the functionality in the Office object models has been around for quite a while.
Most of the code snippets throughout this book were tested with Office 2000
and will probably work in newer and even older versions. The exceptions are
Chapters 14 and 15. Chapter 14 explains smart tags, which were introduced in
Office XP, so that code won't work with previous versions of Office. Because that
code demonstrates new features in Office 2003, it will not run in earlier versions
(although it will probably work in future versions). Similarly, Chapter 15 describes
features added in Office 2003, so that code won't work in Office XP or earlier
versions.
10
nafisspour@bluewin.ch

Introduction

To write Visual Basic programs that manipulate Office applications, you need
a copy ofVisual Basic. The examples in this book were tested using Visual Basic 6,
but they should work with few or no changes in Visual Basic 5.

The Book's Web Site
On the book's Web site (www . vb-helper .com/office . htm) you can:
• Download the examples in this book
• Download other Office programming examples
• View updates and corrections
• Read other readers' comments and suggestions
• Contribute your own VBA examples, comments, and suggestions
The book's source code, which is also available in the downloads section on
the Apress Web site at www. apress . com, includes examples for each chapter zipped
into one bigWinZip archive. Download this file and unzip it into its own directory.
Let WinZip extract the files into separate directories so it can create directories for
each chapter.

NOTE You can obtain WinZipatwww . winzip.com.

... ·...

~\

~.r

FILE

Ch04\MakeButton.doc

~
L_

Many of the book's code snippets and figures include a FILE statement similar
to this one indicating the file containing the code or the file that produced the figure.
For instance, the margin note here indicates that you should look in the Ch04
directory for the file MakeButton.doc.

11
nafisspour@bluewin.ch

Chapter 1

Contacting the Author
If you have comments, suggestions, corrections, orVBA code to contribute, please
e-mail them to RodStephens@vb-helper .com.
The main VB Helper Web site (www. vb-helper. com) contains more than a thousand tips, tricks, and examples, mostly for Visual Basic developers.

Summary
Separately, each Microsoft Office application is powerful but incomplete. Each
provides useful tools for performing very specific tasks. VBA programming lets you
break the barriers inherent in each application. It lets you extend the capabilities
of each application and combine them to take full advantage of each application's
strengths.
The following chapters explain how to use VBA to do with code practically
anything the Office applications can do interactively. They show how to write code
that performs tasks not handled directly by the Office products. They show how to
invoke one Office application from code running in another application to take
best advantage of both. Using these techniques, you can build solutions that do
things never dreamed of by the Office product development teams.

12
nafisspour@bluewin.ch

CHAPTER 2

Macros
TRADITIONALLY, A MACRO is a relatively small piece of code that an application can
execute to perform some simple task. For example, you could write a macro to
build a definition list. The code could give each paragraph a hanging indentation
(the lines after the first are indented) and make the words in each paragraph that
appear before the first em dash bold. The result might look like this:
Macros-This chapter explains fundamental concepts you should understand
to program the Office XP applications.
Customizing Office-This chapter explains different ways you can run a
macro or make it easily available to others who need to use your code.
Automatic Customization-This chapter explains how to make documents
that install and remove their own customizations.
Office Programming the Easy Way: OLE-This chapter explains how you can
use OLE to avoid some possibly onerous programming chores.
Originally, macro languages were relatively simple. By opening up the Office
applications' object models to VBA (Visual Basic for Applications), Microsoft has
given VBA programmers an extremely powerful programming environment. In an
Office application, you can do almost anything programmatically that you can do
interactively, and lots more.

NOTE Actually, there are some other kinds of macros. For example, Access has

its own notion of what a macro is, and it's very different from a VBA macro.
By making selections from dropdown menus and entering a few values, an
Access user can build a sequence of commands that Access calls a macro.
Even less experienced Access users can write this type of macro without
any programming experience.

This chapter explains the fundamentals of Office macro programming. It tells
you how to create, record, edit, and execute macros.

13
nafisspour@bluewin.ch

Chapter2

CAUTION Largely because Microsoft has integrated VBA so completely into

the Office applications, VBA has become a language of choice for less skilled
hackers and virus writers. This has given VBA macro programming a lot of
bad press in recent years. The section "Guarding Against Macro Viruses" later
in this chapter discusses some steps you can take to protect yourselffrom VBA
macro viruses.

First, however, this chapter explains when you should not write macros. In
many situations, a simpler solution can save you the time and effort of writing a
macro that will later require debugging and maintenance.

When Not to Write Macros
The Rule of Twice in programming is:

If you need to use a piece of code two or more times, you should put it in a
separate subroutine.
For example, suppose you have a program that manages customer account
information. In one part of the code, you write some code to create a new record
in the account database. Later you discover another piece of code that needs to
do the same thing. Rather than rewriting the code or copying and pasting it, you
should move it into a separate subroutine that you can call from both places.
Placing code in a subroutine lets you maintain the code in one place rather
than two or more. Later, when you find bugs (and you will) or you decide to make
enhancements, you need to modify the code in only one place instead of hunting
down all the places you've used the code, possibly in many applications.
Writing code for Microsoft Office is a bit different from writing end-user
applications-in Visual Basic or C++, for example-for several reasons. Even if you
will want to do something more than once, it may not be worth the effort of writing VBA code to do it for you. The following sections explain some of the reasons
you may not want to use VBA to perform certain tasks.

14
nafisspour@bluewin.ch

Macros

NOTE Putting the code in a separate routine also makes it easier to reuse it

later. I've always found that two ofsomething in programming is suspicious.
Often, something either occurs exactly once or it might occur a lot of times.
For example, I've written a lot of applications where customer records need to
store customer addresses. Sometime during development, the customer says,
"Oh yeah. The customer record also needs a separate Billing Address." At that
point, we've gone from one address to two.
Being the cautious type, I generalize the record to hold any number of
addresses because I know that next week the customers will want to add a
Shipping Address. The following week they'll add a Records Address. Then
they'll decide that different shipments may go to different addresses and a
single order may be split and shipped to different addresses. By the time all is
said and done, they'll probably want the user to be able to define new address
types on the fly.
When you see something that needs to be done twice, be suspicious. Some
primitive cultures only had numbers for one, two, and many. When designing
an application, you often only need one and many.

One- Time Tasks
If you're going to do something only once, it makes no sense to waste a lot of time

writing macros to do it for you. Even if you're going to do something similar once
or twice more, writing code may not be worth the trouble.
For example, suppose that you're composing a holiday newsletter. You bought
nice stationery with evergreens, birds, and snowmen along the borders, and you
want the newsletter's printed text to wobble in and out on both sides to follow the
outline of the pictures. You could write a program that opened a scanned image of
the stationery, scaled it to fit a printed page, determined where the blank areas on
the page were, and then formatted the text the hard way by inserting carriage
returns to force line breaks and using tabs to position each line on the left. While
this might be an entertaining little exercise, it would probably be a huge waste of
time (unless, of course, you're paid by the hour).
It would be much easier to simply insert carriage returns and tabs yourself
manually. It might not be as much fun as writing an elaborate program but, unless
you're planning on sending out similarly formatted newsletters weekly, you would
probably be better off formatting the letter manually. You might even finish early
enough to mail the letter out before the holiday season is over.

15
nafisspour@bluewin.ch

Chapter2

'C\.····..
~/

=
L_

FILE

Ch02\Shaped.doc

TIP A relatively simple method for this kind offormatting is to create a Word
AutoShape. On the Drawing toolbar (select View~ Toolbars ~Drawing),
open the AutoShapes dropdown, select the Lines command, and pick the
Freeform tool. Then draw a shape that covers the left side of the stationery's
picture. Now select Format~AutoShape. On the Layout tab, select the Tight
Wrapping style. This will make the text automatically wrap around the edges
of the shape. Repeat this process for the right side of the paper. Before you
print, set the fill and outline colors for both shapes to white so they don't
appear in the printed result.

Even if you need to produce a similar newsletter next year, it's probably better
to just do it by hand. If you write a program now, chances are you won't have the
foggiest notion of how the code works in a year. If there's a bug, you'll have to spend
time relearning code that you probably remember about as well as the doubleangle formula from your high school trigonometry class.
Generally, if you're doing something only a couple of times, and you can do it
quickly manually, you should probably just do it rather than writing a program to
do it for you.

Easy Tasks
For the same reasons, easy tasks don't make particularly good candidates for
Office programs. Suppose you want to begin each paragraph in your document
with a hard Tab character (a practice I don't recommend). Just hit Tab when you
start a new paragraph. Many touch typists do this anyway and breaking them of
the habit is hard.
You could probably cook up some code to insert Tabs at the beginning of each
paragraph, but it's just not that hard to type your own.

TIP A better solution is to create a style where paragraphs are formatted to
indent the first line. Then you get first-line indentation automatically. As a
bonus, you can later change the style's definition to increase or decrease the
indentation if necessary without needing to add or remove tabs.

16
nafisspour@bluewin.ch

Macros

Or, just type the text without the Tabs and then later use Find and Replace to
insert them. Replace the special code "p (Paragraph) with "p"t (Paragraph+ Tab).
You can replace "p"t"t (Paragraph+ Tab+ Tab) with "p"t (Paragraph+ Tab) in case
you accidentally type a Tab manually. You might also need to do a little cleanup for
places like the last paragraph in the document, because you don't want to add a
new paragraph containing only a Tab (the first replacement will put one there), but
most of the work will be done for you.

Reinventing the Wheel
The Microsoft Office products have been around for a long time, and they do a
lot. Each new version of Microsoft Office adds a lot of bells and whistles, and it's
entirely possible that a particular whistle will do exactly what you want. It would
be a big waste of your time to write a macro to do something that Office can
already do for you.
For example, it would be silly to write an Excel macro to calculate the sum of
the cells in row A between columns 1 and 10 because Excel's SUM command already
does that (SUM(A1:A10)) .
Unfortunately, the Office applications have so many features that it's hard to
keep track of everything they can and cannot do. If you need to find a quadratic
equation to fit some data, Excel can do that. If you want to print addresses on a
sheet of Avery standard 2163 mini shipping labels, Word can do it. If you have a
hankering to save your PowerPoint presentation as a series of plain HTML Web
pages, PowerPoint can do that.
If you want to find a value of X for which x2 + 2 *X -1 =0, you can use Excel's
Tools >Goal Seek command to discover the value 0.414211263. You'll need to do
some fiddling around to find the other solution -2.414183834, however. Yes, this is
a somewhat esoteric calculation, but it shows how a program sometimes does
almost but not exactly what you need.

~\.··· ·.
-Y/ ~·

FILE

Ch02\SolveQuad.doc

""'\\

If you want to find both solutions exactly or without a bunch of guesswork,
you're better off writing your own code, either as an equation in an Excel cell or as
a macro that calculates the results. The quadratic formula says the two solutions

17
nafisspour@bluewin.ch

Chapter2

to A* x2 + B *X+ C =0 (where A, B, and C are constants and you want to find a
value of X that satisfies the equation) are given by:

- B ± -J B 2 - 4 X A X c
2xA
For the equation x2 + 2 * X- 1 = 0 (A= 1, B = 2, C = -1), this gives the values

2±~2 2 -4xlx(-1)

-2±~ =

=

2xl

2

-2±2.fi
2

=

-I±fi

This formula gives two solutions with values of approximately
0.414213562373095 and -2.41421356237309.

TIP Before you starting writing code, you should think about whether anyone
else in the world might have wanted to do the same thing at some point. If so,
check the Office Help files to see if the feature you need is already there. Don't
forget to consider all of the Office applications. For example, ifyou wanted to
solve an equation in a Word document, it might be easier to solve it using
Excel and then copy or link the result into the Word document rather than
writing a general-purpose Word macro to do the job.

Transferring Data
One common waste of time in Office programming is a VBA program that takes
data from one Office application and inserts it into another. The Office applications already have a bunch of commands that import and export data in a variety
of formats, so investing time to do this is pointless. Similarly, Word provides mail
merge commands that can do things like place address information on letters
(perhaps the holiday newsletter described earlier) so you don't want to reinvent
that sort of feature.
For a concrete example, suppose you want to copy some data from an Excel
spreadsheet into a Word table. You could write a program in Word that uses Excel to
open the spreadsheet, exhumes the data, and uses it to build a Word table.

TIP Actually, this is pretty good practice at working with an Excel server and
Word tables. If you're bored, give it a try.

18
nafisspour@bluewin.ch

Macros

If you're only going to do this once or twice, however, there's a much easier
method. Simply open Word, position the cursor where you want the table, invoke
the File ~Open command, and select the Excel spreadsheet. Word will ask you
whether you want to import the entire spreadsheet or just a range of cells. After
you make your selection, the program automatically loads the Excel data and
stuffs it into a table.
Easier still, select the data you want in Excel and press Ctrl-C to copy it to the clipboard. Then open Word, position the cursor, and press Ctrl-Vto paste the data. Word
automatically figures out that this is table-like data so it shoves it into a new table.
The other Office applications also do a pretty good job of figuring out what
you mean when you copy and paste. If you copy Excel data and paste it into PowerPoint, PowerPoint puts the data in a new table. Copy a table in Word and paste
into a PowerPoint Text object, and PowerPoint displays the data as a run-together
text string. If you don't select a Text object, however, PowerPoint puts the data in a
new table.

TIP Before you write code to copy data from one Office application to
another, see if copy and paste is good enough.

The Office applications are also good at opening files of different types. If you
open an Excel spreadsheet in Word, Word creates a table to hold the spreadsheet
data. Once you have the data in Word, you can copy and paste it into a more farniliarformat.
OLE (Object Linking and Embedding) lets you include documents from one
Office application directly in another. For example, you can insert an Excel spreadsheet directly inside a PowerPoint presentation. In PowerPoint, select Insert ~Object.
Select the Create From File option, click Browse, select the Excel document you want
to insert, and click OK.

NOTE Before you click OK, check the Link box ifyou want PowerPoint to link

the original Excel document. Later, if the Excel document changes, the data
shown in the PowerPoint presentation is automatically updated. Ifyou leave
the Link box unchecked, PowerPoint makes its own copy of the data so it is
not automatically updated later.

After you insert the OLE object into the PowerPoint presentation, double-click
on it to open the data in its native application (in this case, Excel). You can then
use Excel to modify the data. Chapter 5 has more to say about using OLE interactively and programmatically.
19
nafisspour@bluewin.ch

Chapter2

Handling Non-Native Data Formats
The Office applications provide a lot of tools for transferring data to and from
other Office applications. They also provide quite a few methods for moving data
in and out of non -Office applications.
If you need to export data for a non-Office application, try the File ~Save As
command. Different Office applications can save data in many formats, including:
• Text
• Tab-delimited text
• Comma-separated value (CSV)
• Rich Text Format (RTF)
• Web pages (HTML)
• GIF, JPEG, TIF, and other graphics formats

• XML
Similarly, the different applications can read files saved in a variety of different
formats. Just try opening the file and see if it works.
With all of these options available, there's a pretty good chance the Office
applications can handle your data import and export needs without any programming. Spend a few minutes experimenting with Office's built-in features before
you launch a major development effort. Don't waste your time if the Office developers have already done what you need.

When to Write Macros
Of course, there are many good reasons to write your own code. The most obvious
of these is that you want to do something frequently, and the Office applications
can't do it for you.
For instance, suppose you want to build a large sign in a comic font where
every other letter is adjusted up or down a small random amount and is drawn in
a randomly selected color as shown in Figure 2-l (you can't see the color in the
book, but trust me, it's there).

20
nafisspour@bluewin.ch

Macros

Figure 2-1. This text was randomly adjusted by VBA code.

You could do this by hand. For each character you would:
1.

Select the character.

2.

Select Format )oo- Font.

3.

Click the Character Spacing tab.

4.

Set the Position combo box to Raised or Lowered.

5.

Enter the amount you want to move the character.

6.

Click the Font tab.

7.

Select a random color.

8.

ClickOK.

If you need a very short sign, this may be practical. For anything longer than a
dozen or so characters, this will be mind-numbingly boring. Word doesn't have a
tool to do this automatically (it would be a pretty flagrant case of feature proliferation if it did), so you'll have to build your own tool.
The RandomUpDown subroutine shown in the following code demonstrates one
method for randomly moving and coloring the characters in the selected text. It
begins by calling Randomize to initialize Visual Basic's random number generator.
It then loops through the Selection object's Characters array. For each character, the code uses a With statement to manipulate the character's Font object. It
uses a Select Case statement to give the Font a random color, and it uses Rnd to set
the Font's Position to a random value between -3 and 3. This positions the character somewhere between three points above and below the line.

~~·"··.

~9
L_

FILE

Ch02\UpDown.doc

21
nafisspour@bluewin.ch

Chapter2

' Raise or lower the selected characters
' by a small random amount.
Sub RandomUpDown()
Dim i As Integer
Randomize
For i = 1 To Selection.Characters.Count
With Selection.Characters . Item(i) . Font
Select Case Int(Rnd * 5)
Case o:
.Color = vbRed
Case 1 :
.Color = vbBlack
Case 2:
.Color = RGB(o, 192, o) ' Green
Case 3:
.Color = RGB(O, o, 255) 'vbBlue
Case 4:
.Color = vbMagenta
End Select
.Position = Int(Rnd * 7 - 3)
End With
Next i
End Sub

NOTE The Rnd.function returns a value between 0 (inclusive) and

1 (exclusive). That means Int (Rnd * 5) returns a value between 0 and 4,
and Int (Rnd * 7 - 3) returns a value between -3 and 3.

This code follows roughly the same steps you would take to format the characters manually. For each character, it selects a random color and position.
The objects used by the code (Selection, Selection. Characters,
Selection. Characters. Item, and Selection. Characters. Item(i). Font) are part of
the Word object model. Chapter 6 has more to say about the Office applications'
object models in general. Chapters 7 through 10 and 12 describe individual Office
applications' object models in more detail.

22
nafisspour@bluewin.ch

Macros

Complex Tasks
If a task is particularly complex, you might want to write VBA code to handle it
instead of doing it manually even if you plan to do it only once. It's usually easier
to debug a VBA subroutine than it is to debug a single statement entered in an
Excel cell or a calculated Word field.
For example, suppose you want to calculate a complicated expression for each
value in a series. In Excel, you could define the expression for the first value and
then copy and paste the definition into each of the other calculated value cells.
That's quick and easy, so it's probably a better solution than writing something
unnecessarily elaborate.

TIP To copy and paste a calculation from one cell to many, select the first cell
and press Ctrl-C to copy its definition. Then select all of the cells where you
want to place the formula and press Ctrl- V. Excel automatically adjusts the
expression's parameters as necessary. For example, ifyou copy the formula
=2*A1from cellA2 into cells B2:H2, then Excel sets the cells' definitions to
=2*81, =2*(1, =2*01,. • • =2*H1.

But what if you need to change the formula? You'll need to type it into the first
calculated cell, then copy and paste it into the other cells again. You can do that if
you will need to change the formula only once, but what are the odds of that happening? There's no such thing as just one change.
Here's a way to make things easier. Define a function that performs the calculation. For example, the Nth Fibonacci number is defined by:

Fibonacci(o) = o
Fibonacci(1) = 1
Fibonacci(N) = Fibonacci{N - 1)

+

Fibonacci{N - 2)

The first few values are 0, 1, 1, 2, 3, 5, 8, 13, 21, 34. The following function
calculates Fibonacci numbers.

' Return the Nth Fibonacci number.
Function Fibonacci(ByVal N As Integer) As Double
Dim fib_i_minus_1 As Double
Dim fib -i minus- 2 As Double
Dim fib i As Double
Dim i As Integer

23
nafisspour@bluewin.ch

Chapter2

If N <= 1 Then

Fibonacci = N
Else
fib_i_minus_2 = o
fib_i_minus_l = 1
For i = 2 To N
fib_i = fib_i_minus_l + fib_i_minus_2
fib_i_minus_2 = fib_i_minus_1
fib -i minus- 1 = fib - i
Next i
Fibonacci = fib i
End If
End Function
Now you can use this function in the calculated cells. For example, cell B2
might contain the formula =Fibonacci(B1). You can fill in many calculated cells by
copying and pasting this formula just as you would copy and paste a formula that
didn't invoke one of your functions.
Now if you need to change the way the function works, you simply modify the
VBA code, return to the spreadsheet, and press F9 to make the spreadsheet recalculate its values. You don't need to explicitly change the calculated cells, because
their definitions have not changed. They all still call the Fibonacci function, but
that function's definition has changed.
UsingVBA code in this manner also lets you define extremely complicated
functions relatively easily. The function can use If statements, For loops, While
loops, and call other functions that you have written. Doing something similar in
a single Excel cell or calculated Word field would be difficult if not impossible.
Building complex functions, even those that just use plain old arithmetic, is
sometimes easier in VBA code. You can use the Visual Basic editor to write the
function instead of trying to type it into a tiny spreadsheet cell. You can also use
the editor to step through the function as it performs its calculations to debug the
code.

Throwaway Macros
Throwaway macros deserve some special attention. By their very nature, throwaway macros are used only once or twice and then thrown away. That may make
you think you should just perform their task yourself and skip writing any code.
In some cases it's still easier to use VBA code to perform a one-time task. For
instance, suppose you want to number the cells in a large Word table so cell (I,D
displays the text" (1, D" as shown in Figure 2-2.

24
nafisspour@bluewin.ch

Macros

~ ~ake Tdble do' M•cro!.o(t Word

EJa tdt

' tJ

»ow

Insert FQI'IMt !ools

-

TAble

Nonnol + 26 pt • Tines New Remon • 26 •

l!!)ldow

'~

>
v

:'j-

t:teiP

I ~~ ilr 1::

!::

.P • 8

1/1

Figure 2-2. VBA code numbered the cells in this Word table.

Subroutine MakeTableLabelCells shown in the followingVBA code generates
tables like this one. The routine begins by looping through the table's rows. For
each row, it uses the Selection. Type Text command to type the first cell's text for
that row. Then for each column after the first, the routine types a Tab character
and the next cell's value. The code ends each row with a new paragraph.
When it has added all of the table's text to the document, the subroutine
moves up the same number of table lines it just created. It extends its selection
as it moves, so in the end the new text is selected. The routine then uses the
Selection. ConvertToTable command to turn the new text into a table. Finally,
it uses the AutoFormat command to format the table using the style
wdTableFormatGridl (a simple grid).

~\... ·..

/" ~

FILE

Ch02\MakeTable.doc

L_

Sub MakeTablelabelCells()
Const NUM_ROWS = 4
Const NUM_COLS = 5
Dim R As Integer
Dim C As Integer

25
nafisspour@bluewin.ch

Chapter2

' Make the table.
For R = 1 To NUM_ROWS
Selection.TypeText Text:="(" &Format$(R) &", 1)"
For C = 2 To NUM COLS
Selection.TypeText Text:=vbTab &_
"(" &Format$(R) &", " &Format$(C) &")"
Next C
Selection.TypeParagraph
Next R
' Select the text and convert it into a table.
Selection.MoveUp Unit:=wdline, Count:=NUM_ROWS, Extend:=wdExtend
Selection.ConvertToTable Separator:=wdSeparateByTabs, _
NumColumns:=NUM_COLS, NumRows:=6, _
Format:=wdTableFormatNone, ApplyBorders:=True, ApplyShading:= _
True, ApplyFont:=True, ApplyColor:=True, ApplyHeadingRows:=True, _
ApplylastRow:=False, ApplyFirstColumn:=True, ApplylastColumn:=False, _
AutoFit:=True, AutoFitBehavior:=wdAutoFitFixed
Selection.Tables(1).AutoFormat Format:=wdTableFormatGrid1, ApplyBorders:= _
True, ApplyShading:=True, ApplyFont:=True, ApplyColor:=True, _
ApplyHeadingRows:=True, ApplylastRow:=False, ApplyFirstColumn:=True, _
ApplylastColumn:=False, AutoFit:=True
End Sub
You could easily create the table in Figure 2-2 manually. If the table were
much larger, however, it would be tedious beyond belief. Making a table with
10 columns and 1,000 rows takes practically no time using subroutine
MakeTableLabelCells. Building that table by hand could take quite a while.

Coding for Other Users
One very common reason for writing VBA code is to automate a task for someone
else. Although you may be able to handle the complexities of graphing cost versus
quantity produced to find an optimal production level, your boss may not. In
cases like that, you can build a form that simplifies data entry and generates
graphical results.
You can also integrate several Office applications to give your boss a comprehensive presentation package. Your code can automatically generate a Word document reporting the results and a PowerPoint slide show for your boss to show to

26
nafisspour@bluewin.ch

Macros
upper management. All this simplifies your boss's job, reduces his chances to mess
things up, and makes him (and you) look good. (If you're a manager, this discussion applies to your boss, not to you. If you're a CEO and you don't have a boss,
well, this obviously only applies to other companies and not to yours.)

TIP Sometimes, you can find useful VBA macros on the Web.
11y Microsoft's discussion groups or Office development community

athttp ://msdn.microsoft.com/community/office .asp.

TIP You can also write startup macros that install buttons for the truly clueless to push to launch your tools. Chapter 4 has more to say about this.

Writing Macros
Having decided that you want to write a macro, you can proceed a couple ways.
First, you can write a macro from scratch. You may need to take this approach
when writing a particularly complicated macro. However, often you can take a
shortcut by recording a macro similar to the one you want to write and then modifying it.
The following sections explain how to manage macros in Office applications.
They tell how to list and execute macros, record macros, set macro security levels,
and how to open the Visual Basic IDE (Integrated Development Environment) to
edit and debug macros.
In an Office application, if you select Tools ~Macro , you'll see four relevant
items: Macros, Record New Macro, Security, and Visual Basic Editor. These choices
are described in the following sections.

Managing Macros
The Tools ~Macro ~Macros command opens a Macros dialog that lets you create, view, edit, and execute macros. Figure 2-3 shows the dialog presented by
Word. The dialogs are slightly different for other Office applications. These differences are described a little later.

nafisspour@bluewin.ch

27

Chapter2

(!~aero

nomo:

TestLoodXmiToble
TestS..vexmiArt~le
Tests..vexmiToblo

:i_tep lrto

Org,anizer. "

MAcros In: INormol.dot (global template)

Cancel

I

Desat>tlon:
Lood the lnclcoted Xfol. fie cortoining an art~le into the currently octlve dorunent.

Figure 2-3. Words Macros dialog lists available macros.

Select a macro and click Run to execute that macro. Click Step Into to start
running the macro and go to its first line of code in the Visual Basic IDE. Click Edit
to jump to the macro's code in the IDE. Click Delete to delete the selected macro.
While you have a macro selected, you can change its description text by typing in the Description box at the bottom.
Enter a new name in the "Macro name" text box and click Create to make a
new macro. This opens the IDE with a new subroutine containing a comment

indicating who made the macro and when. For instance, suppose the document's
author name is set to Rod (File )o- Properties )o- Author field), and you create a new
macro named GraphData on December 9, 2002. Then Word generates the following
code. The macro's description is initially set to the barely better than useless value,
"Macro created 12/9/2002 by Rod."

Sub GraphData()
' GraphData Macro
' Macro created 12/9/2002 by Rod

End Sub
In Word, you can use the "Macros in" combo box to select a group of macros.
Your choices are:

• All active templates and documents-This lists all macros in all open documents and in Normal.dot. That's everything available to you while the current document is open.
28
nafisspour@bluewin.ch

Macros

• Normal.dot (global template)-This lists macros stored in Normal.dot.
These macros are always available no matter what documents you have
open.
• Word commands-This lists commands supplied by Word itself. These
are only marginally useful here. For example, you could select the All Caps
macro to capitalize the selected text, but it would be just as easy to use
Format ~Font and check the "All caps" box.
• Documentl (document)-This lists macros stored in the currently active
document, in this case Document!.
Unfortunately, the various Office applications have a few differences in the
way they handle macro code. Figure 2-4 shows the Macros dialog displayed by
Excel.

Macro

.11~

·;:

l!l,acroname:

~

ILoodSales~•
Colculot

•

I

R~n

Concel
~ep

Into

~dlt

Create

..:.1

Mpos In:

jProductlon.xls

:::1

l)_elete

~···

~ ------------------

Load soles data from the soles database.

Figure 2-4. Excel's Macros dialog lists macros available in Excel.

Most of the details are the same in the Word and Excel versions of the Macros
dialog, but there are two significant differences. First, Excel stores all of its macros
with a specific workbook so there is no place to store global macros that corresponds to Word's Normal.dot template. This affects the choices in the "Macros in"
combo box. In Excel, you have the following choices:
• All Open Workbooks-As you can probably guess, this displays all macros
in all workbooks currently open.
• This Workbook-This displays macros in the active workbook.

29
nafisspour@bluewin.ch

Chapter2

• Bookl.xls, ...-All loaded workbooks are listed after the This Workbook
choice so you can list the macros in any single loaded workbook. Figure 2-4
lists the two macros in the workbook Production.xls.
The second difference between Word's and Excel's Macros dialog is the way in
which the dialogs handle macro descriptions. You can see in Figure 2-4 that the
LoadSalesData macro's description is in a label, not in a text box. That means you
cannot edit it there. Instead you must click Options and enter the description in
the text box on the dialog shown in Figure 2-5.

Macro Options

.. ~

Macro MlT'Ie:
loadSalesDot•

Shortcut !!,oy:

Ctrl+l
!;lese~:

lload saes data from the sales database.
OK

Cancel

I

Figure 2-5. The Macro Options dialog lets you enter options for Excel macros.
Although there are differences between how the Office applications handle
the details, the general ideas are the same. After you figure out one of these
dialogs, you should have little trouble with the others.

Recording Macros
One of the most remarkable features of Office applications is their ability to record
your actions and store them in a macro so you can play them back later. For example, you could record a macro while you select a word and make it bold and move
to the beginning of the next line. By executing the recorded macro repeatedly, you
could quite easily make the first word in a series of lines bold.
Macro recording not only lets you automatically save code to perform simple
chores, but it also provides a useful peek into the inner workings of the Office
applications. Suppose you want to build a Word macro that formats selected text
so its characters are spaced farther apart than normal. You can record a macro
while you select Format> Font, click the Character Spacing tab, set Spacing to
Expanded, and enter 30pt as the amount to adjust the characters. Now you can
look at the recorded macro, shown in the following code, to see how it works.

30
nafisspour@bluewin.ch

Macros

FILE Ch02\SpreadLetters.doc

Sub Spreadletters()
' Spreadletters Macro
' Spread the letters in the selected text
With Selection.Font
.Name = "Times New Roman"
.Size = 12
.Bold = False
.Italic = False
.Underline = wdUnderlineNone
.UnderlineColor = wdColorAutomatic
.StrikeThrough = False
.DoubleStrikeThrough = False
.Outline = False
.Emboss = False
.Shadow = False
.Hidden = False
.SmallCaps = False
.AllCaps = False
.Color

=

wdColorAutomatic

.Engrave = False
.Superscript = False
.Subscript = False
.Spacing = 30
.Scaling = 100
•Position = o
.Kerning = o
.Animation = wdAnimationNone
End With
End Sub
Just by looking at this code, you can see that Word exposes a Selection object
to your VBA code. The Selection object has a Font property that you can use to
modify the text's font. Looking through the properties specified in the recorded
macro, you can easily learn how to modify the text's font name, size, bold, italic,
and other properties.

31
nafisspour@bluewin.ch

Chapter2

If you have some experience with Visual Basic or VBA programming (and this
book assumes you do), then you can probably guess that you can omit any of
these assignments that you do not want to change. For example, if you want to
spread out the selected letters without changing the font's other properties, you
can reduce the macro to the following code.

~ . .. . .

'/

;.·

FILE

Ch02\Spread.Letters.doc

' Spread the letters in the selected text
Sub ConciseSpreadletters()
Selection.Font.Spacing = 30
End Sub
If you later wanted to change the code to alter other font properties, such as
the font's color or boldness, you wouldn't have too much trouble figuring out how.
Although macro recording is similar in all Office applications that allow it
(Outlook does not), the details are slightly different. The following sections explain
how to record macros in Word and Excel.

NOTE This is a common theme in the Office applications. Although they follow the same general approach and provide similar macro capabilities, the
details are often slightly different. Same play, different playbooks.

Recording Word Macros
When you select Tools ~Macro ~Record New Macro, Word displays the dialog
shown in Figure 2-6. Give the macro a descriptive name, select the location where
Word should store the macro, and enter a description. Then click OK to start
recording.

32
nafisspour@bluewin.ch

Macros
Record Macto

~ ;:.,

Macro name:
JMacro1
Asars

~oroCustomize command. You can use them now, but you may
find it easier to record the macro first and assign it to a toolbar button or menu
item later. Chapter 3 has a lot more to say about this.

Recording Excel Macros
Just as the various Office applications' Macros dialogs are slightly different, their
Record Macro and recording dialogs are also different. Figure 2-8 shows Excel's
Record Macro dialog.

33
nafisspour@bluewin.ch

Chapter2

o

I

I

f!laao name:
Shottcut !lev:

ctrl+l

I

st;c:.:or
.;,.:.
e--"macr
"-o'-"j'l-:- - - . . . ,
Ttvs Workbook

I

~rlltk>n:

Mecro recorded 12/11/2002 by Rod

OK

Cancel

I

Figure 2-8. Excel's Record Macro dialog is slightly different from Word's
(shown in Figure 2-6).

The different Office applications store macros in different locations, so the
locations you can select in the "Store macro in" box differ. Excel's Record Macro
dialog shown in Figure 2-8 also lets you assign a shortcut key directly, but Word's
dialog requires additional steps. The Office applications' macro recording dialogs
also vary by application. For example, PowerPoint has no Pause button.
In Excel's macro recording dialog, shown in Figure 2-9, the Pause button is
replaced by a Relative Reference button (on the right). This button is unusual
enough that it deserves a bit more attention. If you click this button and then
record a macro, the macro is relative to the initially active cell.

Figure 2-9. Excel's macro recording dialog has a Relative Reference button instead of
a Pause button.

To see how the Relative Reference button works, consider the Excel worksheet
shown in Figure 2-10. Note that cell B2 is initially the active cell. Suppose you
record a normal macro with the Relative Reference button not pressed. You click
on cell F2, type "=SUM(", click and drag to select cells B2:E2, type")", and finally
press the • button to stop recording. When you finish, cell F2 contains the formula
=SUM(B2:E2).

·c.....·.
~/'

FILE

Ch02\Relative.xls

~

34
nafisspour@bluewin.ch

Macros

t!ti

E!1e tdt :t10W Insert

D~Piil

FQrmat

Ioois

t!at•

'!'!lndow

~

~ll.l©~Aria1

Figure 2-10. This simple worksheet displays values by quarter and region.

Suppose you now select cell B3 and run the macro. The macro performs
exactly the same steps as before. It moves the active cell to F2 and assigns it the
formula =SUM(B2: E2). Not very useful.
Now suppose you start over with the active cell in B2 again as shown in
Figure 2-10. This time you start recording and press the Relative Reference button.
You perform the same steps as before, so in the end cell F2 contains the formula
=SUM(B2:E2).

This time when you select cell B3 and run the macro, something very different
happens. The macro moves the active cell four spaces to the right relative to its
current position and lands in cell F3. It then inserts the text "=SUM(", inserts the
relative range B3:E3, and closes the formula to get =5UM(B3: E3). This is much more
useful than repeatedly inserting exactly the same formula into cell F2.

TIP In this simple example, you can get the same effect with less work by filling cell F2 with the formula =SUM( 82: E2) and then copying and pasting it into
cellF3.

CAUTION Note that the Relative Reference button remains pressed between
macro recordings. Ifyou use it for one macro, it will still be pressed when you
record the next macro.

Aside from the Relative Reference button, the macro recording dialogs used by
the different Office applications are pretty similar. You should be able to figure out
the differences without too much difficulty.

35
nafisspour@bluewin.ch

Chapter2

Guarding Against Macro Viruses
Microsoft has tightly integrated its Office applications by giving them a common
macro language (VBA) and by exposing their object models for easy programming.
That makes building powerful, comprehensive applications easy. Unfortunately,
the fact that Outlook sends and receives e-mail makes writing e-mail viruses easy.
The Microsoft Office applications form a powerful workplace tool, but they also
form a gateway for every hacker wannabe.
Although Office does not include a hacker-proof system for stoppingVBA
viruses, it does include three security levels you can use to protect yourself if you
know what you are doing. When you select Tools )-Macros )-Security, Word displays the dialog shown in Figure 2-11. This dialog lets you set the macro security
level to High, Medium, or Low. The following sections explain what these levels
mean and describe some other steps you can take to protect yourself from VBA
viruses.

Secur1ty

, •

[]!£ght be oiOI!IOfl'. tnors wil receive o sec•dy
w01ning when they open • file thot contono • macto pr~ Mh • sel..igned
oignoiUIO.

A ooll·oigned ce~if~eote should be used only 101 pe~oonal use. If you need an
auhenticated ce.tlicate. you rnig,t wont to corlocta corlification aoAholiy. ouch ..

thooe btedot

httj>.//olliceupdote.miorosollcom/ollice/redireclllromOIIice9/certJ..m.

YOUinome:

OK

C¥ICel

Figure 2-12. Program Selfcert.exe lets you make your own digital signatures.
Mter you create the digital signature certificate, open the VBA code project you
want to sign in the Visual Basic development environment. Select Tools>- Digital
Signature to open the dialog shown in Figure 2-13.

The V8A project Is CUTently sq,ed as - - . . . . . - - - - . . , . . --

Certflcote name:

[No certficote]

~·· ------------------------Certllcatename:

[No certficote)

OK

Cancel

I

Figure 2-13. The Digital Signature dialog lets you pick a digital certificate for a VBA
project.

38
nafisspour@bluewin.ch

Macros

When you click the Choose button, the program displays the dialog shown in
Figure 2-14.

· . ""

Select Certtficate

Select tho certificate you- to use.

Issued to

lrltended P...

~RMCC

RMCC

Code~

OK

N011e

C..-.:el

I

:t~ew Cettiflcote

I

Figure 2-14. This dialog lets you select a digital certificate for a VBA project.

~; ·· ·..

FILE

Ch02\SignedVBA.doc

FILE

Ch02\SignedVBA.xls

~
L
_

~~ .· ··.
~,

·.

~
L
_

Select the certificate you want to use to sign the code and click OK. The code's
Digital Signature dialog now looks like Figure 2-15. Click the Remove button to
remove the code's signature.
D1g1tal Signature

· · •'"

Tho VBA project is currently <9'led as _ _
CertlficMe name:

Rod stephens
Qetal ...

~·· -----------------------------Certlflcote

name:

Rod stephens

B.omovo

OK

Cancel

Figure 2-15. The VBA code is attached to a digital signature certificate.
39
nafisspour@bluewin.ch

Chapter2

Suppose you try to open a document containingVBA code that is signed with
an authenticated digital signature certificate (the next section has more to say
about this).lfyou have security set at high, then Word displays the security dialog
shown in Figure 2-16.

, ''1~

Secur1ty Warmng

C: \Offlce5moc"""""'\Src\Ch02\l)anger .doc

contains macros by
Rod Stephen<

Det,als ...

Macros ""'Y conton viruses. !tIs always sole to 

l[!l:ended purpose:

I

I

ill

I

Personal Other People !nl:erlnedote Cer~flcotlcn Author~ los Trusted Root Certflt«iOr
lssuedB

Issued To

ffi! RMCC
~ Rod

r

Stephens

Import...

I

RMCC

1/1/2008



ROO Sl:eph~rrS

1ll/.200B

d lvn':'" ;;

~port...

I

Remove

e,dv.nced ...

I

Cerlft:etelrtended pu-poses

CodeSiplg

~lose

Figure 2-19. You can use Internet Explorer to manage certificates.

Next select the Trusted Root Certification Authorities tab, click the Import button, and import the file you just made. After you finish importing the certificate
onto the Trusted Root Certification Authorities tab, your system will consider the
certificate authenticated.
Now back to the Visual Basic IDE. If you view the certificate again (Tools~
Digital Signature, click Choose, select the certificate, click View Certificate), you'll
see the view shown in Figure 2-20.

42
nafisspour@bluewin.ch

Macros

Cert1ficate

General

:-!."'

IDetoiis l Cerliication Path l

This certificate is intended to:

• Ensll'"es software came from softw!lre pubhsher

• Protects sdtwa~e from a~eration alter publcatlon

Issued to: Rod Stephens
Issued by: Rod Stephens
Valid from 1/1/2002 to 1/1/2008
~ You have a private key that corresponds to tlis certficate.

Figure 2-20. This certificate is authenticated.

To remove the authentication from a self-made certificate, reopen the dialog
shown in Figure 2-19 (start Internet Explorer, select Tools~ Internet Options, go to
the Content tab, and click the Certificates button) . Go to the Trusted Root Certification Authorities tab, select the certificate, and click Remove.

Signing a Document
You can use digital signature certificates not only to sign VBA code, but also to sign
a Word document itself. This ensures that the document has not been modified
since it was signed.
If someone modifies the document in any way and tries to save the changes,
Word presents the dialog shown in Figure 2-21. If the user continues to save, Word
strips off all of the document's signatures.

M•cto~oO: Word

"'·f~

SomQ.,. remo'-Options. Go to the Security tab and click
the Digital Signatures button. Click the Add button, select the signature certificate
you want to add, and click OK. Figure 2-22 shows the Digital Signature dialog listing the signatures attached to a document.

Signalweol
The diollll slgnetu'e generated by Office moy not constlt01...., n ~ .

~--------------------~~
Ylew Certfbte ... I w----~: m
:Jl e.emove

17 A!;,t..ch certificates wtil newly added s!Qnotures

t!elp

I

OK

Cancel

I

Figure 2-22. The Digital Signature dialog lists the signatures attached to a document.

Medium Security
When security is set at high, Office quietly disables macros unless they are digitally
signed. When security is set at medium, Office warns you when you open a document containing macros that might be unsafe. If the macros are signed by a
trusted source, they are quietly enabled. Otherwise, you must click the Enable
Macros button to allow the macros to run.
Medium is undoubtedly the most useful setting forVBA developers. When you
open documents containing macros that you wrote, you can click the Enable
Macros button. When you open the recipe for scones that you downloaded from
44
nafisspour@bluewin.ch

Macros

the Internet, you can click Disable Macros. You must use a little more intelligence
about which macros you enable or disable than you would if security is set at high,
but the medium setting lets you ignore the tortuous steps needed to sign code digitally.
If you download a document that may actually have a valid need to run
macros, start by clicking Disable Macros. Next, open the document's VBA code and
take a look. Search for Shell and Shell Execute statements, API function calls, calls
to Outlook or other Office applications, anything that uses Web URLs, and any
other code that looks the least bit suspicious. If you're absolutely sure the code is
benign, close the document, reopen it, and click Enable Macros this time (you
cannot run the macros if you initially clicked Disabled Macros when you first
opened the document).

CAUTION Do not click Enable Macros before you have examined the code! You

may think, "''ll enable macros, but I won't run any of them until I look at the
code." That won't work, because some Office documents have Open events that
execute macros immediately before you can do anything else. As soon as you
open the document, this code executes, and a virus could already be installed
before you have a chance to look at the code.
In fact, you should probably regard the presence ofDocument_Open (in Word)
and Workbook_ Open (in Excel) in the code as a bit suspicious. If these event
handlers are present, take a very close look at them before you open the document with macros enabled.

If you want to be extra safe, you can use WordPad to open potentially hazardous files, such as that 1.2MB joke list your brother-in-law e-mailed to you.
WordPad doesn't support macros, so it cannot run them even if you become so
used to clicking the Enable Macros button that you do it by accident.

Low Security
When security is set at low, Office silently enables all macros even if they will trash
your hard disk and e-mail hundreds of naughty pictures to your boss, spouse, and
church group.
Because macros are integrated so deeply into Microsoft Office applications,
particularly Outlook, VBA has become a panacea for hackers. Setting your security
level at low is just plain suicidal. Any idiot can write a VBA virus, stick it in a document named Playmates_Tell_All.doc, and have thousands of people downloading

45
nafisspour@bluewin.ch

Chapter2

it and infecting themselves within minutes. You may as well post your credit card
numbers on a public bulletin board and get it over with.
If you are developer, it makes much more sense to set security at medium.
When you open a document that contains macros, the application warns you, and
you can take a look at the code yourself. If your users are inexperienced and vulnerable to macro viruses, tell them to set the security level at high. Or better still,
do it for them.
About the only situation in which I can imagine setting the security level at
low is on a computer completely isolated from any network that might harbor a
virus and is also used by a neophyte who can't handle the complexities of clicking
the Enable Macro button. Even in this case, it's only a matter oftime before the
user brings in a game or other "useful" tool he downloaded from the Internet on a
floppy disk, and all is lost.

Application Differences
Naturally, the different Office applications handle security policy differently. For
example, if a macro is in Normal. dot, Word quietly assumes it is safe to run. That's
not a bad guess, because you probably put the macro in there yourself, but it does
seem a bit privileged.
If a macro is in a Word document, the security settings take full effect. If security is set at high, Word refuses to run unauthenticated macros. If security is set at
medium, Word warns you that the document contains macros when you open it
and asks if you want to enable or disable them. If security is set at low, you get
what you deserve.
Because Excel stores all macros in a particular workbook, it has no location for
storing trusted macros that corresponds to Word's Normal. dot. That means Excel
always behaves as Word does when a macro is stored in a document. If security is set
at high, Excel refuses to run unauthenticated macros. If security is set at medium,
Excel warns you when you open the document and lets you decide whether it
should run macros. If security is set at low, you're just asking for a virus that sends
threatening e-mails to the Internal Revenue Service.
Like Excel, PowerPoint has no location corresponding to Normal. dot, so it
behaves much as Excel does. PowerPoint doesn't have an Open event that executes
code when you open a presentation, so you don't need to worry about that kind of
virus. Just for safety's sake, you should still review any VBA code with macros disabled before you enable them.

46
nafisspour@bluewin.ch

Macros

NOTE The application checks security when a document is first opened. Ifyou

change the security setting, you must close and reopen the document to see
any effect.

Also keep in mind that the security check occurs only when the document is
opened. Ifsecurity is set at medium and you tell the application to allow
macros to run, it allows all macros in the document to run at any time without any further warning. The program won't tell you it is about to run a
macro that reformats your hard drive later. It's an aU-or-nothing decision
when you first open the document.

More Information on Security
VBA security can be a confusing issue. In case you don't feel you have enough

information to be thoroughly confused yet, there's plenty more available on the
Web. Here are a few links you can explore:
• http: I /office. microsoft. com/downloads/2002/offxpsec. aspx-The Microsoft
publication Office XP Document: Macro Security White Paper.
•

http://office.microsoft.com/assistance/9798/virusres.aspx-Microsoft~

Web page Anti-Virus Resources for Microsoft Office. Some of this information
doesn't apply to all versions of every Office application, but there's a lot of
useful stuff here. Be sure to look at the related links section on the right.
• http://www.sophos.com/virusinfo/whitepapers/office2000.html-Sophos
(antivirus company) article Microsoft Office 2000 and Digital Macro
Signatures.

The Visual Basic Editor
The final item in the Tools ~Macro submenu is Visual Basic Editor. All Microsoft
Office applications use the same macro programming language (VBA) and the
same IDE. Figure 2-23 shows the Visual Basic IDE in Microsoft Word. The code
shows subroutine MakeArticle, a function that generates an XML Article object.
MakeArticle is contained in module NewMacros, which is stored in the Normal
project. That project is saved with the Normal. dot template so that its modules
are available to all Word documents.

47
nafisspour@bluewin.ch

Chapter2

E.1o

~dt

'tlow

~

li!l ~ • Iii! \ ~

Fgrmot

ft ,. .-,

~

C"'•

. Norn,.:d

Normal
D MioO>Oit ward Ob!eOit WOfd Ob)octs
Cl References

e,dd-!ns l!titdow ~
~ ~ ~ i:j' '\! ~ ll)

Ioois

&'"'

Qobug

II

•

f'.I('W"'1ol(tOs([ode)

3

ln 131, Cell
~.._

lMakeArtlcle

1

Make an J..L""Cicle w.1t.b the l.nthcated Title.

1

Return the neu Article eletnl!'nt..

Pr1vate Function Ha.k:e.Article (ByVal dcm document
Dim ert!.c:::le_e l emene As IXHLOOfttlemene
1

lEekf! the Article

As DOIIDocument,

el~ne .

Set article element • dom docuroe:nt.cre~teElement.("Art.ic::le") _J
parent . appeildChild are ic te_elemt!:nt
artie le_element. ::set.A.ttribute "Title", title

• Start. a new line 1nsid.e the At"tlcle element .
art.ic le_e lement. apJ)endChild dom_document .createTextNode (vbCr

IMQRO- Macro menu command
to manage the macro. You can select it and edit it in the Visual Basic IDE.
Set your Office security to an appropriate level to allow you to run your
macros while still protecting yourself from viruses. You may find the high security
setting restrictive for your own development use, but it may be appropriate for
VBA neophytes and untrained Office users.
The medium security level is usually more appropriate for developers because
it lets you enable your macros while disabling those in e-mail messages you
receive from disgruntled former coworkers.
Finally, the low security setting is appropriate for computer thrillseekers,
those who like experimenting with scrambled hard drives, and those who want an
excuse to buy a new computer.

53
nafisspour@bluewin.ch

CHAPTER 3

Customizing Office
THERE's LI'ITLE POINT in writing or recording a macro if you're never going to use it.
One way to invoke a macro is to use the Tools • Macro • Macros command to open
the Macros dialog described in Chapter 2 and shown in Figure 3-1. Select the macro
you want to execute and click Run.

M a eros

•JJ:!I

~.···~~

Macro""""''

JrestloadXmiArticle

&to>
6

TestLO

1• '

>;ommonds

I

I

Qptlons

To odd • corrrnond to • toobor: select • cot"90'Y ond drag the
cOel.-.cros.l

Fonts
AutoText
styles
&Jil·ln MervJs
NewMerAJ

Normai.NewMacros.RenameFnes

Normei.NewMecros.RestoreTooii.=J

~:

Macro recorded 12/11/2002 by Rod

3

~ ~eve n: INormel.dot

Keyboerd ... II

0ose

Figure 3-4. To add custom menu items, select the Macros entry in the list on the left.

In the list on the right, find the macro you want to assign. Then click and drag
the macro onto Word's top-level menu bar. Hover the mouse over the menu where
you want to place the macro and wait until the menu opens. Drag the macro into
position in the menu and drop it.

Customizing Menu Items
By default, a new custom menu item shows its completely qualified name. For

instance, Figure 3-5 shows a new menu item for the LoadSalesData macro in the
NewMacros module contained in the Normal project stored in Normal.dot. You
can modify the menu item's appearance to change the text it displays, make it
display a button, or change its overall style.

not

!ools TAble

]l!lndow

tie~

Normoi.Ne¥ol'lecros.Load5eles0ate
~end

Grammar ...

Language
]l!ordCO\.Oll •••

Figure 3-5. Initially, a custom menu item displays a fully qualified path to the
macro it executes.

59
nafisspour@bluewin.ch

Chapter3

If you closed the Customize dialog shown in Figure 3-4, reopen it using
Tools >-Customize. Click on the menu that contains the custom menu item to
open it. Then right-click on the custom menu item to display the popup menu
shown in Figure 3-6. The popup menu holds the commands Reset, Delete, Name,
and so forth.

""

I Ci
41

. . ..
tdt i.'iOW II\SOit

Ia

~Iii

•' " l Nonnol

Cl>lll"' @)

I ... '

:;peihj and Grammar ..•

f:(eset

F

,

-----~:

ctrl+~

Norrnoi.Ne¥ofolaer•

CO!>'>' Man lmaQe

A\(toSunmarlze ...

£oslo Man 1"-

!rack~

.....

~Man1"-

Ctti+Shlft+E

~~andG-OQthe

r-

,..._. and MerQO I!Oeunerl•· ..

~"' Man lmaQe ...

erotect Doco.rnent ...

Chon9e Quttan ' " - • "

OQine Colabcratloo

Del &,I. Stylo

Took on tho wet! ...

lmaQe tnd Text

Terroplotos and Add-Ills ...

6eQon. Go"'-"

&-meorrect Optlons •••

As.VO !!YPef(l CoH4 CII8T

G.J-· .. ' . ,.

:>"

'-·~~"'~ •
• G) _
jJ:RJ
"·

~

Nonnoi.NeMolocros.Loac!SolosDola

a~~

• Tfmos-ROIN

Norrnol

:.J.bl29

;rL'f,!, ·;-_-

!odsl TJI>Io ytroclow

~

In:

\Nonnol. to select that item. For
example, in most applications the File menu has F underlined. If you press Alt-E
the menu opens. You can then use Alt - again to invoke one of the items in
the menu. For example, Alt-S saves the current file in Word.
You can use a similar technique to create your own keyboard shortcuts. To
create a shortcut for a menu item, place an ampersand(&) before one of the letters in its display text. Word displays that letter underlined without the ampersand, and that letter becomes the item's Alt- shortcut key.
For instance, suppose you create a new menu named &Macros (Tools •
Customize, select New Menu on the left, drag the New Menu item from the right
list onto the menu bar). Because the menu's name starts with an ampersand, Word
displays its name as Macros.
Now you can add a menu item named &Load Sales Data to the Macros menu.
Word would display this item as .Load Sales Data. If you hold down the Alt key and
press M followed by L, Word opens the Macros menu and then invokes the Load
Sales Data item.
Similarly, you can assign an Alt- shortcut to a toolbar button. Use an
ampersand in the button's caption to indicate the letter that should trigger the
button. Note that the shortcut will not work unless the button's style is set to
display text.
If more than one visible item has the same shortcut key, then pressing
Alt- does not invoke it. Instead, it toggles the focus among the items
that share the shortcut. When the one you want is selected, press Enter.
This can make selecting the right item inside a menu tricky, because if you
release the Alt key, Word stops looking for accelerators. To select an item in a menu
with an ambiguous shortcut key, be sure to hold the Alt key down while you press
Enter and any other letters until you have navigated to your final selection.

nafisspour@bluewin.ch

63

Chapter3

NOTE Because all these fingertip gymnastics can be pretty tricky, most users

won't bother. Some will use simple key combinations but will just use the
mouse if they need to hold down Alt, press the key repeatedly, and hit Enter.
You can make things easier by trying hard to ensure that all underlined letters
are unique on any given menu.

Deciding on a shortcut key that is not in use can be tricky because it may
depend on the kind of window open at the time. Left to its own devices, Word uses
the same menus and toolbars all the time. However, you can hide or show different toolbars and make custom menus, items, toolbars, and buttons. In other
Office applications, such as Outlook (which has many different kinds of windows
that each has its own menu selections), this can be a big problem.
Creating your own Macros menu and placing all your tools there can make
things a little easier. Note that you also can combine all of these techniques. You
can make a command available through a menu item, tool bar button, keyboard
combination, and Alt - shortcut. Then users can use the method they find
most natural.

Replacing Standard Word Functions
In addition to creating your own custom toolbar buttons and menu items, you can
hijack Word's standard functions and replace them with your own VBA subroutines. Overriding standard commands indiscriminately will cause confusion that
results in your being paged daily during your vacation. Sometimes, however, it
may be worthwhile to add additional functionality to an existing function. For
example, you could modify the File >-Save command so your code can verify the
document's format before saving the user's changes.
Note also that software upgrades may replace any customizations you make
with their default actions. Then you would need to reinstall your customizations.
If you decide to go ahead anyway, be sure to keep all of the overrides in one place
with instructions so you can reapply them later if necessary.
Before you can override Word's standard commands, you must learn what
those commands are. One way to do that is to press Ctrl-Alt and the plus sign on
the numeric keypad. Find the toolbar button or menu item you want to replace,
and click it. That makes Word display the Customize Keyboard dialog shown in
Figure 3-8. This figure shows that Word performs the standard New action using
the FileNew command.

64
nafisspour@bluewin.ch

Customizing Office

~: ..

Cu"Jto nu ze Keyb-oard

Ali Comm-!!nds

Spedy keyboord sequence - - - - - - - - Qrr;:.:;ert:::,:;.;;keys='- - --, Pross oew st-.Jrtcut key:
ctri+N
:;)

l

II

.:..1

~cMnoeCustomize
and clicking the Keyboard button to display the dialog shown in Figure 3-9. Select
the category of the command you want on the left. In Figure 3-9, the File category
is selected. If you don't know what category you want, select All Commands. After
you select a category, scroll through the list on the right to see what commands
Word has available.

Spedyoconrnand - - - - - - - - - -

;;'P!""ieiii
gorlosii '• • •:J(Qmmonds
.. 2~=.:''=----.
FileCioseOrExit
• I

J

Edit

View

Insert
Format
Tools

~

FieConli'mConversions
Fle£xl
Fiefir'd

...J

_ 1 FieNewContext
F!eOpen

.!J

Table

Spedyke~d~e ---------~~

I

Cr=t,l~rerl::::,:;.;;keys=:-----r Press [leW shortcU: key:
Ctri+N

~

:!J

.::.1

_____________
INormol.dot

.,........

Creates • new doament or template

t;~gn

I

Re;npye

I

R¥ AI...

II

Close

Figure 3-9. Select Tools> Customize and click Keyboard to browse through Words
standard functions.

65
nafisspour@bluewin.ch

Chapter3
Once you have found the command you want, you can override it two ways:
with a named module or with a named subroutine.

Using Named Modules
The first way you can override Word's standard functions is by creating a standard
VBA module named after the command you want to replace. Give this module a
subroutine named Main and put the code you want to execute in it.
The following code, placed in a module named FileOpen, overrides the File
menu's Open command. It prompts the user for a project name, sets the File Open
dialog's initial directory appropriately for that project, and then displays the File
Open dialog.

TIP A more user-friendly application would probably display a list of projects
in a combo box on a customized form. Then the user could pick from the list
instead of having to type the project name correctly. You could also supply a
Cancel button in case the user decides not to open a file after all.

It may be even better to make this a new menu item or toolbar button and
leave Words standard open command alone.

'C\.····..
~.r

FILE

Ch03\Builtln.doc

$

Sub Main()
Dim project_name As String
' See which project the user wants to work on.
project_name = LCase$(InputBox("Project", "Project", ""))
' Set an appropriate file open directory.
= "amess" Then
Application .ChangeFileOpenDirectory _
"C:\Projects\AMess\Current Version\Documents"

If project_name

66
nafisspour@bluewin.ch

Customizing Office

Elseif project_name = "foolserrand" Then
Application.ChangeFileOpenDirectory _
"C:\Projects\FoolsErrand\Current Version\Documents "
Elseif project_name = "ratfink" Then
Application .ChangeFileOpenDirectory _
"C :\Projects\Testing"
Else
o Use the current directory.
End If
Display the file open dialog .
Application .Dialogs . Item(wdDialogFileOpen).Show
End Sub
0

When you create a new code module, Word initially gives it a catchy name
similar to Module l. To rename the module in the IDE, click on it and select View~
Properties Window to display the window shown in Figure 3-10. Type the module's
new name in the Name property.

F'ropertre: · t·.-1odule 1

lr.1

Figure 3-10. Select View~ Properties Window to rename a module.
As is the case for most operations using Word, you can do the same thing with
VBAcode.

Application.OrganizerRename _
Source :=ActiveDocument, _
Name :="Modulel", _
NewName:="FileOpen", _
Object :=wdOrganizerObjectProjectitems

67
nafisspour@bluewin.ch

Chapter3

Using Named Subroutines
The second way you can override a standard Word command is to create a VBA
subroutine with the same name as the command. You can put the routine in any
standard code module.
The following subroutine replaces the FileSave command. This code displays
a message indicating where you would put validation code if you wanted to check
the document's formatting, ensure that necessary data fields were entered, and so
forth. It then calls the ActiveDocument object's Save method to save the document
normally.

~ .... .
f ;.·

FILE

Ch03\Builtln.doc

Sub FileSave()
' Validate the document's format.
MsgBox "Validate the document's format here"
' Save the document.
ActiveDocument . Save
End Sub

Using New Named Macros
Another way to override a standard Word command is really just a modification on
the previous method that uses a named subroutine. This method has two advantages. First, it lets you find and override the standard function in one step. Second,
it shows you how the standard Word command works so you can mimic some of
its behavior if you like.
Start by selecting Tools >-Macro >-Macros to open the Macros dialog. In the
"Macros in" list, select Word commands. This displays a list of all standard commands available in Word. Scroll through the list and select the command you want
to override.

68
nafisspour@bluewin.ch

Customizing Office

Now, in the "Macros in'' list, select the location where you want to save the new
macro code. Normally, you would select your current document or Normal. dot if
you want to override the command in every Word document.
Finally, click the Create button. Word creates the new macro and gives it the
name of the command you want to override. It places the code in the NewMacros
module within the project you selected in the "Macros in'' list. Word also inserts
the code it uses to execute the standard command. If you don't change the
description entered when you selected the standard command from Word's list,
you get the same description, too.
The following code shows an unretouched macro created to replace the FileNew command. From this code you can see how to let the user open a new file by
using Dialogs ( wdDialogFileNew) . Show.
Sub FileNew()
1
1

FileNew Macro
Creates a new document or template
Dialogs(wdDialogFileNew).Show

End Sub

Removing Word Customizations
To remove a keyboard shortcut in Word, select Tools >-Customize and click the
Keyboard button to show the Customize Keyboard dialog in Figure 3-3. Select
Macros in the left list and the macro you want to remove in the right list. The
"Current keys" list shows the shortcuts you have assigned to this macro. Select
the one you want to remove and click the Remove button.
To remove a menu item or toolbar button in Word, select Tools >-Customize.
Right-click the item you want to remove, and select Delete.
Alternatively, you can drag the item out of its menu or toolbar and drop it
somewhere it is not allowed. For example, you can drop it on the Word document,
title bar, or the Customize dialog. Figure 3-11 shows the LoadSalesData menu item
being dragged over the Customize dialog. The cursor with an arrow, button, and
box with an X in it indicates that the item cannot be placed there.

nafisspour@bluewin.ch

69

Chapter3

...Jbl.l!l
T;'Pfo~~~fa-~4·

,.,_

41-

--

,. .....,.,

• )(

Toadda~toatoc:lhdr:~.at.ll.fJ

CortroiToolxlx
""""
................
,_......,

Figure 3-14. Excel's Customize dialog is similar to Word's.

71
nafisspour@bluewin.ch

Chapter3
To create a menu item, drag the Custom Menu Item object from the list onto
the main menu that you want to contain the new item. Hover there until the menu
opens. Then drag the new item into position and drop it.

NOTE It doesn't matter much whether you use a Custom Menu Item or Custom
Button on menus or toolbars; they both do about the same thing. The main
difference is their initial appearance. A Custom Menu Item initially displays
the name "Custom Menu Item," and the Custom Button initially displays a
smiley face button.

Customizing Menu Items
When you right -click on the new menu item in customize mode, you see the
popup menu shown in Figure 3-15.

B.osot
~lete
~mne:

--

&Custom Button

l;opy Button Imaoo

eosto Button lmaQo
ROiel: Button lmOQO

!;:dll: Button lmaQO ...

--

ChanQo autton lmaQo •

0~

roxt Only (Always)
Text~ ('n Menus)

lmOQe tnd Text
Se91n•~oup

Assqo l:jypefW;
Assq,(!!acro •. ,

•
~

Figure 3-15. Excel uses this menu to customize menu items and toolbar buttons.
This menu is similar to the one used by Word except for the addition of the
Assign Macro command at the bottom. When you select this command , Excel displays the Assign Macro dialog shown in Figure 3-16. Select the macro you want to
assign to the menu item and click OK.

72
nafisspour@bluewin.ch

Customizing Office

""'.. ~,

j.. .,..,-

::1

v.._

~ -Add~ the
82:E2 ra.ttve toccl82-

Figure 3-16. Use Excel's Assign Macro command to assign code to a toolbar button.

Toolbar Buttons
To create a new toolbar button in Excel, display the Customize dialog as usual by
selecting Tools )1- Customize. Select Macros in the list on the left. Then click and
drag the Custom Button item onto a toolbar and position it where you want it.
Right -click on the new button to change its style, modify the text it displays,
copy, paste, and edit its image, and so forth. Use the Assign Macro command to
attach the button to a macro.

Removing Excel Customizations
To remove a keyboard shortcut in Excel, open the Macro dialog shown in Figure 3-12
again (Tools )1- Macro )1- Macros). Select the macro attached to the shortcut you
want to remove and click Options to display the Macro Options dialog shown in
Figure 3-13. Select the text in the "Shortcut key" text box and delete it.
You remove a custom menu item or toolbar button the same way you remove
these items in Word. Select Tools )1- Customize. Right-click the item and select
Delete to remove it. Alternatively, you can click and drag the item out of its menu
or toolbar and drop it somewhere it is not allowed. A cursor with an arrow, button,
and box with an X similar to the one shown in Figure 3-lllets you know where the
item cannot be dropped.

Customizing PowerPoint
You make custom menu items and toolbar buttons in PowerPoint in the same way
you make them in Word. Use Tools )1- Customize to open the Customize dialog.

73
nafisspour@bluewin.ch

Chapter3

Select Macros on the left and drag the macro you want onto a menu or toolbar.
Right -click the newly created item to modify its properties.
Although PowerPoint doesn't have a central location for storing code similar
to Word's Normal. dot template, it does let you use code from one presentation
while you are designing another. In fact, it will let you use menu items and toolbar
buttons that you have attached to any code in any presentation even if you currently have no presentation open.
For example, suppose you have a MakeJobTable macro in the presentation
Jobs.ppt. You invoke Tools ,..Customize, select macros on the left, and drag
MakeJobTable onto a toolbar. Now you close Jobs.ppt so no presentation is loaded.
The button you created is still in the toolbar. In fact, if you click the button, it will
work despite the fact that the presentation containing the code the button executes isn't loaded. When you click the button, PowerPoint automatically opens
Jobs.ppt, digs out the code it needs, and executes it.
The catch is that PowerPoint cannot find the code if you delete or move the
file Jobs.ppt. If you delete the file and click the button, PowerPoint displays amessage similar to this:
PowerPoint can't run this macro because it can't open C:\OfficeSmackdown\
Src\Ch03\ContainsControls.ppt.
You remove custom menu items and toolbar buttons from PowerPoint just as
you do in Word. Open the Customize dialog (Tools ,..Customize), right-click the
item you want to remove, and select Delete. Alternatively, you can drag the item
and drop it somewhere is isn't allowed.

Keyboard Shortcuts
Unfortunately, PowerPoint doesn't allow you to create keyboard shortcuts. You can
create Alt- shortcuts by placing an ampersand in the caption displayed by
a menu item or toolbar button. For more information, see the section "Menu Item
and Toolbar Button Shortcuts" earlier in this chapter.

Customizing Access
Access is not really an end-user tool in the same way Word, Excel, and PowerPoint
are. Access is really a programming tool for DBAs (database administrators). It
helps you build tables, queries, forms, and reports that you can use to manage the
database.

74
nafisspour@bluewin.ch

Customizing Office

To better understand the difference, think about what the users of these products do. AWord or PowerPoint user composes a more-or-less static document. Once
the document is finished, the user can print or display it, but probably won't make
changes on a daily basis. An Excel workbook can be somewhat more dynamic,
because you may want to make changes to the data over time and let Excel recalculate related values. Often, however, a user creates an Excel workbook to display some
data in a report or presentation and it really isn't intended to change daily.
On the other hand, a DBA can use Access on a daily basis to monitor the
health of the database and to generate reports that summarize the data. For example, consider a sales database that contains information about customers, orders,
inventory, and sales staff. The DBA may run weekly productivity reports to see
how much each sales engineer has sold, daily inventory reports to see what supplies are running low, daily order reports to make sure orders are moving through
the system, and quarterly sales forecasts to predict future demand. Far from being
a static document, the database and its associated forms, reports, and other database tools form a constantly evolving system.
The different focuses of these applications leads to some differences in how
you customize them.

Access Macros
Access introduces yet another twist on writing macros and attaching them to
menu items and toolbar buttons. To satisfy the more programmatic needs of its
users, Access provides more options for writing and executing code. The basic idea
is that Access users need powerful programming features but are not necessarily
skilled enough to handle the complexities of a programming environment such as
VBA by themselves.
To allow users to perform moderately complex tasks, Access includes its own
concept of a macro that differs from a simple chunk ofVBA code. This is really on
the edge of this book's scope because it deals with Access features rather than
Office programming. (For comparison, consider instructions for manually indenting paragraphs in Word versus instructions on how to make a VBA program indent
the paragraphs.) It's important to understand the difference between the two
kinds of macros, however, so this section says a little more about the Access-style
macros.
To manage this kind of macro, use Access to select the Macros object category
as shown in Figure 3-17. Click the New button at the top to make anew Accessstyle macro. Select a macro and click Run or double-click the macro to execute it.
Select a macro and click Design to open the macro in the Access macro editor.

75
nafisspour@bluewin.ch

Chapter3

iji Sales: Database (Access 2000 file £ormat)

1m!

Tables

@

~ies

l% ~lary!
l% GrartRaise
l% loao the GhoonthlsO

Source Exif Data:
File Type                       : PDF
File Type Extension             : pdf
MIME Type                       : application/pdf
PDF Version                     : 1.4
Linearized                      : No
Has XFA                         : No
XMP Toolkit                     : Adobe XMP Core 5.2-c001 63.139439, 2010/09/27-13:37:26
Modify Date                     : 2018:05:10 23:41:26+02:00
Create Date                     : 2013:05:25 17:57:58+08:00
Metadata Date                   : 2018:05:10 23:41:26+02:00
Creator Tool                    : Adobe Acrobat 9.0
Format                          : application/pdf
Creator                         : 
Title                           : 
Document ID                     : uuid:b695bab6-cd9d-4d1a-be14-5f37350aebdf
Instance ID                     : uuid:396e77f4-94c1-4187-9f61-72fd4b5f2cf2
Version ID                      : 1
Producer                        : Acrobat Distiller 10.1.2 (Windows); modified using iText® 5.4.0 ©2000-2012 1T3XT BVBA (AGPL-version)
Trapped                         : False
GTS PDFX Version                : PDF/X-3:2002
Part                            : 1
Conformance                     : B
Language                        : en
Page Layout                     : SinglePage
Page Mode                       : UseThumbs
Page Count                      : 725
Owner ID                        : 74478992
EXIF Metadata provided by EXIF.tools

Navigation menu