Office VBA Macros You Can Use Today: Over 100 Amazing Ways to Automate Word, Excel, PowerPoint, Outlook, and Access PDF
Preview Office VBA Macros You Can Use Today: Over 100 Amazing Ways to Automate Word, Excel, PowerPoint, Outlook, and Access
Office 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
The list of books you might like
