Table Of ContentOffice VBA:
Macros You Can Use Today
Office VBA: Macros You Can Use Today
Copyright: © 2006 Holy Macro! Books. All Rights Reserved
Authors: Juan Pablo González, Cindy Meister, Suat Ozgur, Bill Dilworth, and Nico
Altink.
Publisher: Bill Jelen
Project Manager: Anne Troy
Art Director: Scott Pierson
Technical Editor and PrePress: Linda DeLonais
Cover Design: Shannon Mattiza, 6'4 Productions
Published by: Holy Macro! Books, 13386 Judy, Uniontown OH 44685
Distributed by: Independent Publishers Group
ISBN: 978-1-932802-53-5
LCCN: 2005921880
First Printing: December 2005. Printed in USA
All brand names and product names used in this book are trade names, service marks,
trademarks or registered trademarks of their respective owners.
Table of Contents
Office VBA: Macros You Can Use Today
page i
Table of Contents
Foreword ........................................................................................ a
About the Authors and Contributors ................................................. c
Juan Pablo González ..........................................................................................................................c
Cindy Meister......................................................................................................................................d
Suat Ozgur ..........................................................................................................................................d
Bill Dilworth.........................................................................................................................................e
Nico Altink...........................................................................................................................................e
Contributors .........................................................................................................................................f
Introduction .................................................................................... 1
What is VBA?.......................................................................................................................................1
Save Time .....................................................................................................................................1
Take Advantage of UserForms.....................................................................................................1
Develop User-defined Functions .................................................................................................2
Enjoy Security ...............................................................................................................................2
How to Use This Book ...................................................................... 3
Using the Procedures .........................................................................................................................3
Understanding Code Comments .................................................................................................3
Inserting Paragraph Returns in Code..........................................................................................4
Using Menu Commands...............................................................................................................4
Identifying Procedure Components.............................................................................................4
Using Sample Files.......................................................................................................................5
Backup Your Files!........................................................................................................................5
Version Compatibility ...................................................................................................................5
Introducing the Visual Basic Editor .................................................. 7
Accessing Visual Basic Editor (VBE) ..................................................................................................8
Touring the VBE Toolbar.....................................................................................................................9
Setting VBE Options......................................................................................................................... 11
Using Project Explorer ..................................................................................................................... 12
Understanding VBA Project ............................................................................................................. 13
Working with Modules..................................................................................................................... 14
Using the Main Code Window ......................................................................................................... 16
Protecting Your Projects.................................................................................................................. 18
Excel Procedures .......................................................................... 19
Printing All Files ............................................................................................................................... 19
Saving a Workbook as Today's Date............................................................................................... 21
Highlighting Duplicates Within a Range ......................................................................................... 23
Table of Contents
page ii
Office VBA: Macros You Can Use Today
Sorting Worksheets ......................................................................................................................... 25
Generating a Unique List................................................................................................................. 27
Using AutoFilter on a Protected Sheet ..................................................................................... 30
Deleting Rows Based on Criteria .................................................................................................... 31
Checking Whether or Not a File Exists............................................................................................ 35
Removing Hyperlinks....................................................................................................................... 36
Applying SUM / COUNT by Color ..................................................................................................... 37
Using More Than Three Conditional Formats................................................................................. 40
Providing a Calendar to Choose Dates for Input............................................................................ 42
Restricting Text Box Entry to Numbers........................................................................................... 45
Running a Macro When a Cell Changes......................................................................................... 47
Forcing the Use of a Custom Print Procedure................................................................................ 49
Restricting the User to a Portion of the Worksheet ....................................................................... 50
Copying a Workbook with Macros Removed.................................................................................. 52
Inserting Empty Rows in a Range ................................................................................................... 54
Creating a Custom Toolbar.............................................................................................................. 56
Creating a Table of Contents of a Workbook ................................................................................. 60
Changing the Case of Text .............................................................................................................. 62
Creating a Photo Album................................................................................................................... 64
Deleting the Empty Rows in a Range.............................................................................................. 67
Creating a List of Files That Reside in a Directory ......................................................................... 69
Forcing the User to Enable Macros................................................................................................. 73
Finding and Replacing a String in All Open Workbooks................................................................. 75
Converting Data to a Tabular Format ............................................................................................. 76
AutoNumbering Invoices and Other Workbooks............................................................................ 79
Comparing Columns Using Various Criteria ................................................................................... 80
Deleting the Contents of Unlocked Cells........................................................................................ 83
Hiding All Standard Toolbars Except Your Own.............................................................................. 85
Creating a PPT Presentation from a Pivot Chart ............................................................................ 88
Saving a Backup Copy of a Workbook............................................................................................ 93
Importing Your Contacts from Outlook ........................................................................................... 96
E-mailing from Excel with Outlook .................................................................................................. 99
Printing a UserForm.......................................................................................................................102
Importing and Formatting a Text File............................................................................................105
Extracting Numbers from a Text String.........................................................................................109
Finding and Deleting Erroneously Named Ranges ......................................................................111
Logging Actions When a Cell Changes..........................................................................................113
Synchronizing Page Fields of Pivot Tables ...................................................................................116
Table of Contents
Office VBA: Macros You Can Use Today
page iii
Word Procedures......................................................................... 119
Applying Your Favorite Bullet/Number Format ............................................................................119
Finding and Replacing in Multiple Documents ............................................................................122
Highlighting a Selection.................................................................................................................126
Highlighting a Selection in Word 2002/XP...................................................................................127
Removing All Highlighting..............................................................................................................129
Inserting AutoText with No Formatting .........................................................................................130
Updating All Fields.........................................................................................................................131
Setting Hyperlinks on Index Entries..............................................................................................132
Displaying a Number in Millions as Text.......................................................................................138
Copying Nested Field Codes as Text.............................................................................................141
Converting AutoNumbered Text into Normal Text........................................................................144
Reverse Numbering.......................................................................................................................144
Tables: Changing the Tab Direction..............................................................................................146
Tables: Suppressing New Rows When Tabbing ...........................................................................148
Tables: Formatting Numbers in a Selection.................................................................................149
Tables: Copying Formulas .............................................................................................................151
Using Calendar Wizard ..................................................................................................................157
Formatting Your Calendar.......................................................................................................163
Inserting a Picture with Caption....................................................................................................164
Making Changes......................................................................................................................168
Associating a Picture with a Page.................................................................................................170
Forms: Suppressing New Paragraphs in Form Fields..................................................................175
Forms: Formatting Text Input in Form Fields ...............................................................................178
Changing Other Types of Formatting......................................................................................182
Forms: Inserting a New Table Row ...............................................................................................183
Forms: Deleting a Table Row ........................................................................................................189
Forms: Placing a Picture in a Protected Form..............................................................................193
Mail Merge: Using a Relative Path for Data Source.....................................................................195
Mail Merge: Displaying the Mail Merge Interface ........................................................................198
Mail Merge: Creating a User-Friendly List of Fields......................................................................200
Mail Merge: Making Placecards Using WordArt...........................................................................203
Mail Merge: Creating a One-to-Many List .....................................................................................205
Mail Merge: Merging with a Chart.................................................................................................215
Transferring a Selection to a New Document ..............................................................................225
Splitting a Document into Multiple Files ......................................................................................230
Creating a Folder Tree Menu ........................................................................................................236
Changing Custom Dictionaries On-the-Fly....................................................................................245
Formatting Spelling Errors for Printing .........................................................................................249
Table of Contents
page iv
Office VBA: Macros You Can Use Today
Entering Data Easily Using a Custom Dialog Box.........................................................................254
Creating a Bookmark from a Selection ........................................................................................260
Making Bookmarks Visible............................................................................................................264
Forcing the User to Enable Macros...............................................................................................265
Generating the Document Using VBA.....................................................................................265
Using Forms Protection...........................................................................................................267
Macros in Files Opened by Code ............................................................................................267
Outlook Procedures ..................................................................... 269
Creating Control Buttons...............................................................................................................269
Saving E-mail Attachments in a Specified Folder ........................................................................271
Creating a Contacts Database......................................................................................................274
Sending a Web Page as the Body of an E-mail Message ............................................................279
Sending a Message Individually to Multiple Recipients ..............................................................282
Sending Daily Attachments to Certain Recipients .......................................................................287
Creating Reminders Automatically ...............................................................................................290
Creating Task Items Automatically in Outlook .............................................................................294
Special: Outlook Security ..............................................................................................................300
Auto Replying to Selected E-mail Messages..........................................................................301
Remote Control with Outlook E-mail Message.......................................................................302
PowerPoint Procedures ............................................................... 305
Inserting a Predefined Number of Slides .....................................................................................305
Manipulating AutoShapes.............................................................................................................306
Grabbing All Text............................................................................................................................308
Moving Shapes and Graphics During Presentation.....................................................................310
Making a Random Jump to Another Slide....................................................................................313
Random Madness .........................................................................................................................315
Sending Word Outline to Notes Section of PowerPoint ...............................................................316
Wrapping Text to the Next Slide....................................................................................................319
Saving the Show Point...................................................................................................................321
Personalizing a Presentation ........................................................................................................322
Creating a New Presentation..................................................................................................324
Access Procedures ...................................................................... 327
Splitting Names .............................................................................................................................327
Designing Consistent Forms .........................................................................................................331
Triggering a New Form Based on a Subform Selection...............................................................334
Selecting and Filtering with Cascading Combo Boxes.................................................................337
E-mailing a Selection.....................................................................................................................342
Making a Rolodex-type Selection Listbox.....................................................................................346
Table of Contents
Office VBA: Macros You Can Use Today
page v
Validating Data ..............................................................................................................................350
Moving Rows Between Listboxes..................................................................................................353
Moving Rows in Listboxes.............................................................................................................355
Creating a Dynamic Crosstab Report ...........................................................................................357
Generating Periodic Reports.........................................................................................................359
Creating Controlled Numbers .......................................................................................................361
Making a Wizard with Tabbed Control..........................................................................................363
Combined Procedures ................................................................. 367
Transferring Charts From Excel to PowerPoint.............................................................................367
Saving Word Form Data to an Excel Spreadsheet .......................................................................368
Filling a Word Combo Box with Data from Excel ..........................................................................371
Transferring Data from E-mail Attachments to Excel...................................................................378
Creating Word Labels from an Excel Recipient List.....................................................................382
Creating Custom Mail Merge Using Data in Excel Worksheet.....................................................389
Using Calendar Control for Office Applications ............................................................................391
Appendix A ................................................................................. 403
Opening and Using the Visual Basic Editor ..................................................................................403
Locating the Code Object........................................................................................................404
Inserting a Module ..................................................................................................................406
Inserting a UserForm...............................................................................................................407
Opening Worksheet Objects (Excel) .......................................................................................410
Opening ThisWorkbook Object (Excel)....................................................................................412
Opening ThisDocument Objects (Word) .................................................................................412
Opening ThisOutlookSession Objects (Outlook) ....................................................................413
Opening Slide Objects (Powerpoint).......................................................................................414
Access Objects ........................................................................................................................415
Appendix B ................................................................................. 417
Running a macro ...........................................................................................................................417
Running a Macro Automatically..............................................................................................417
Running a Macro Manually.....................................................................................................417
Running a Macro from a Toolbar Button................................................................................418
Running a Macro Using Shortcut Keys...................................................................................419
Index .......................................................................................... 421
Table of Contents
page vi
Office VBA: Macros You Can Use Today
Foreword
Office VBA: Macros You Can Use Today
page a
Foreword
A simple macro language appeared in version 3 of VisiCalc. When Lotus 1-2-3
introduced the keystroke macro recorder, accountants everywhere began
developing arcane little macros to automate the daily task of importing and
formatting sales data in their spreadsheets. When Excel 5 shipped with a new
macro language called VBA in 1993, the world changed. Using VBA, it became
possible for every one of the 400 million users of Microsoft Office to develop great
looking and powerful applications.
The message board at MrExcel.com hosts over 30,000 questions per year and over a
third of these questions are posed by people who have questions about VBA in
Excel. Clearly – there are very many people using VBA around the world. However
– the typical VBA coder is someone is not necessarily a programmer. You will have
someone who is very good at a certain Office application – perhaps I would go so far
as to call him or her a guru with a particular application. Soon, our coder has
mastered every aspect of the application and starts to explore the macro recorder
and then get in to writing VBA macros to automate the use of that application.
However – it is rare to find someone who is a guru in both Excel and PowerPoint.
Either you work somewhere where you process lots of data or somewhere where
you design a lot of presentations and your expertise in one app or another allows
you to climb the learning curve for that VBA app.
At MrExcel Consulting, I prefer to write applications for Excel, but occasionally a
client needs Excel to interface with PowerPoint or Word and things generally come
to a halt. We know Excel VBA inside and out. We know the gotchas and the
peculiarities that don't quite work. But, when we need to tread in the PowerPoint
VBA object model, we are rookies.
The idea for this book was to gather together VBA experts from each of the
Microsoft Office applications and to have them all design really cool applications for
their individual application. The goal is for an expert in Excel VBA to be able to
pick up the book and learn from example how things are done in PowerPoint or
Word or Access or Outlook. There are many books on the bookstore shelves that
address VBA for one particular Office app or another – my goal with this book is
that you can comfortably write useful macros in all of your Office apps.
Bill Jelen
co-author of VBA & Macros for Microsoft Excel
Foreword
page b
Office VBA: Macros You Can Use Today
This page intentionally left blank.
About the Authors and Contributors
Office VBA: Macros You Can Use Today
page c
About the Authors and Contributors
We have asked some of the greatest, most respected developers from all over the
world to provide you with the most commonly asked for VBA routines, and to de-
mystify those routines for you.
Juan Pablo González
Excel Development Author
Juan Pablo was born in Bogotá, Colombia. He started programming in Basic and
then ventured for a while with Q–Basic and Pascal. But it wasn't until the
spreadsheet battle between Lotus, Quattro Pro, and Excel that he started to build
small applications to solve simple problems.
While he attended Pontificia Universidad Javeriana for his major in Industrial
Engineering, Juan Pablo developed several applications—all of which were based
in Microsoft Excel—to deal with issues that are found on a daily basis in different
areas of any organization, such as customer service, marketing, and accounting.
In 2001, working as an analyst for an Insurance company, he began browsing the
message board at www.MrExcel.com, where he realized the potential of Microsoft
Excel—something very few people seem to grasp. After winning with an entry he
submitted to MrExcel's "Challenge of the Month", he began working as a part-time
consultant, creating Excel-based applications for clients around the globe.
He works now as a full-time developer and software architect for Dealerware, LLC,
a software company based in Illinois. Their main product is F & I Menu Wizard
(www.FIMenuWizard.com), a very impressive Excel-based application used by
automobile dealerships. JP (as his friends call him) lives in Carbondale, Illinois,
and continues to offer free peer-to-peer support at the www.MrExcel.com message
board as well as the Microsoft public newsgroups. These contributions have earned
him Microsoft’s Excel Most Valuable Professional (MVP) Award. JP can be reached
at
