Office VBA Macros You Can Use Today
January 2006
Learn best-practice coding examples for each core Office product.
Add to Cart: $9.99 »
by Suat M. Ozgur, and more...
category: VBA, Programming
covers: Excel, Access, Word, PowerPoint, Outlook
Product Details
- 420 Pages
- Publisher: Holy Macro! Books
- ISBN: 978-1-932802-06-1
- PDF ISBN: 978-1-932802-53-5
Useful coding samples for Excel, Word, PowerPoint, Access, and Outlook!
If you are a MrExcel.com reader, you are probably an Excel guru. You might be comfortable writing code for Excel in VBA. You realize how cool VBA is for Excel and you wonder, "is it possible to automate the creation of PowerPoint slides?" I will admit that once I step outside of the realm of Excel VBA, I am completely lost trying to automate Word or PowerPoint or Outlook. I am sure that it can be done, but I am not the guy to do it.
The idea behind this book is to gather together five VBA experts - one from each core Office component. These co-authors contributed their favorite VBA macros that would be useful to a wide range of Office users.
A Who's-Who of VBA
- Word: The book contains 45 Word macros by Cindy Meister, a Microsoft MVP for Word. She hails from Switzerland and has been an MVP since 1996.
- PowerPoint: 11 Macros for PowerPoint by Bill Dilworth. Bill is a Microsoft MVP in PowerPoint from Pennsylvania.
- Excel: 41 macros written by Juan Pablo Gonzalez. Juan Pablo is a MrExcel MVP and a Microsoft MVP. He is a native of Colombia
- Outlook: 11 macros for Outlook provided by Suat M. Ozgur. Suat is a MrExcel MVP and a professional VBA coder from Turkey.
- Access: 13 macros for Access by Nico Altink. Nico is a top Access expert having answered over 6,900 questions. He is a native of the Netherlands.
- Bonus: 7 additional macros that work across applications provided by Cindy Meister and Suat M. Ozgur.
- Overview: Anne Troy provides an introduction to VBA, using the VB Editor, and how to use the code samples from the book, with contributions from Ken Puls, Jacob Hilderbrand, T J Brandt, Rich Shields, John Skewes, Carlos Fernando Paleo da Rocha, and Tony Jollans
-
Introduction
- What is VBA?
- Save Time
- Take Advantage of UserForms
- Develop User-defined Functions
- Enjoy Security
-
How to Use This Book
- Using the Procedures
- Understanding Code Comments
- Inserting Paragraph Returns in Code
- Using Menu Commands
- Identifying Procedure Components
- Using Sample Files
- Backup Your Files!
- Version Compatibility
-
Introducing the Visual Basic Editor
- Accessing Visual Basic Editor (VBE)
- Touring the VBE Toolbar
- Setting VBE Options
- Using Project Explorer
- Understanding VBA Project
- Working with Modules
- Using the Main Code Window
- Protecting Your Projects
-
Excel Procedures
- Printing All Files
- Saving a Workbook as Today's Date
- Highlighting Duplicates Within a Range
- Sorting Worksheets
- Generating a Unique List
- Deleting Rows Based on Criteria
- Checking Whether or Not a File Exists
- Removing Hyperlinks
- Applying SUM / COUNT by Color
- Using More Than Three Conditional Formats
- Providing a Calendar to Choose Dates for Input
- Restricting Text Box Entry to Numbers
- Running a Macro When a Cell Changes
- Forcing the Use of a Custom Print Procedure
- Restricting the User to a Portion of the Worksheet
- Copying a Workbook with Macros Removed
- Inserting Empty Rows in a Range
- Creating a Custom Toolbar
- Creating a Table of Contents of a Workbook
- Changing the Case of Text
- Creating a Photo Album
- Deleting the Empty Rows in a Range
- Creating a List of Files That Reside in a Directory
- Forcing the User to Enable Macros
- Finding and Replacing a String in All Open Workbooks
- Converting Data to a Tabular Format
- AutoNumbering Invoices and Other Workbooks
- Comparing Columns Using Various Criteria
- Deleting the Contents of Unlocked Cells
- Hiding All Standard Toolbars Except Your Own
- Creating a PPT Presentation from a Pivot Chart
- Saving a Backup Copy of a Workbook
- Importing Your Contacts from Outlook
- E-mailing from Excel with Outlook
- Printing a UserForm
- Importing and Formatting a Text File
- Extracting Numbers from a Text String
- Finding and Deleting Erroneously Named Ranges
- Logging Actions When a Cell Changes
- Synchronizing Page Fields of Pivot Tables
-
Word Procedures
- Applying Your Favorite Bullet/Number Format
- Finding and Replacing in Multiple Documents
- Highlighting a Selection
- Highlighting a Selection in Word 2002/XP
- Removing All Highlighting
- Inserting AutoText with No Formatting
- Updating All Fields
- Setting Hyperlinks on Index Entries
- Displaying a Number in Millions as Text
- Copying Nested Field Codes as Text
- Converting AutoNumbered Text into Normal Text
- Reverse Numbering
- Tables: Changing the Tab Direction
- Tables: Suppressing New Rows When Tabbing
- Tables: Formatting Numbers in a Selection
- Tables: Copying Formulas
- Using Calendar Wizard
- Inserting a Picture with Caption
- Associating a Picture with a Page
- Forms: Suppressing New Paragraphs in Form Fields
- Forms: Formatting Text Input in Form Fields
- Forms: Inserting a New Table Row
- Forms: Deleting a Table Row
- Forms: Placing a Picture in a Protected Form
- Mail Merge: Using a Relative Path for Data Source
- Mail Merge: Displaying the Mail Merge Interface
- Mail Merge: Creating a User-Friendly List of Fields
- Mail Merge: Making Placecards Using WordArt
- Mail Merge: Creating a One-to-Many List
- Mail Merge: Merging with a Chart
- Transferring a Selection to a New Document
- Splitting a Document into Multiple Files
- Creating a Folder Tree Menu
- Changing Custom Dictionaries On-the-Fly
- Formatting Spelling Errors for Printing
- Entering Data Easily Using a Custom Dialog Box
- Creating a Bookmark from a Selection
- Making Bookmarks Visible
- Forcing the User to Enable Macros
-
Outlook Procedures
- Creating Control Buttons
- Saving E-mail Attachments in a Specified Folder
- Creating a Contacts Database
- Sending a Web Page as the Body of an E-mail Message
- Sending a Message Individually to Multiple Recipients
- Sending Daily Attachments to Certain Recipients
- Creating Reminders Automatically
- Creating Task Items Automatically in Outlook
- Special: Outlook Security
- Auto Replying to Selected E-mail Messages
- Remote Control with Outlook E-mail Message
-
PowerPoint Procedures
- Inserting a Predefined Number of Slides
- Manipulating AutoShapes
- Grabbing All Text
- Moving Shapes and Graphics During Presentation
- Making a Random Jump to Another Slide
- Random Madness
- Sending Word Outline to Notes Section of PowerPoint
- Wrapping Text to the Next Slide
- Saving the Show Point
- Personalizing a Presentation
- Creating a New Presentation
-
Access Procedures
- Splitting Names
- Designing Consistent Forms
- Triggering a New Form Based on a Subform Selection
- Selecting and Filtering with Cascading Combo Boxes
- E-mailing a Selection
- Making a Rolodex-type Selection Listbox
- Validating Data
- Moving Rows Between Listboxes
- Moving Rows in Listboxes
- Creating a Dynamic Crosstab Report
- Generating Periodic Reports
- Creating Controlled Numbers
- Making a Wizard with Tabbed Control
-
Combined Procedures
- Transferring Charts From Excel to PowerPoint
- Saving Word Form Data to an Excel Spreadsheet
- Filling a Word Combo Box with Data from Excel
- Transferring Data from E-mail Attachments to Excel
- Creating Word Labels from an Excel Recipient List
- Creating Custom Mail Merge Using Data in Excel Worksheet
- Using Calendar Control for Office Applications
-
Appendix A
- Opening and Using the Visual Basic Editor
- Locating the Code Object
- Inserting a Module
- Inserting a UserForm
- Opening Worksheet Objects (Excel)
- Opening ThisWorkbook Object (Excel)
- Opening ThisDocument Objects (Word)
- Opening ThisOutlookSession Objects (Outlook)
- Opening Slide Objects (Powerpoint)
- Access Objects
-
Appendix B
- Running a macro
- Running a Macro Automatically
- Running a Macro Manually
- Running a Macro from a Toolbar Button
- Running a Macro Using Shortcut Keys
- Index
Downloadable Code Files!
All of the code listings are available for download at the site mentioned on page 5 of the book. Just copy and paste into your VBA project.
