VBA OFFICE.pdf

发布时间：2022-06-15 发布人：admin 分类：说明书资料大小：7.98M 资料格式：pdf 举报版权申诉

390fa1dd-3570-4204-929e-fbe3fb8d3766.pdf-第1页.png

第1页 / 共451页

390fa1dd-3570-4204-929e-fbe3fb8d3766.pdf-第2页.png

第2页 / 共451页

390fa1dd-3570-4204-929e-fbe3fb8d3766.pdf-第3页.png

第3页 / 共451页

390fa1dd-3570-4204-929e-fbe3fb8d3766.pdf-第4页.png

第4页 / 共451页

390fa1dd-3570-4204-929e-fbe3fb8d3766.pdf-第5页.png

第5页 / 共451页

390fa1dd-3570-4204-929e-fbe3fb8d3766.pdf-第6页.png

第6页 / 共451页

390fa1dd-3570-4204-929e-fbe3fb8d3766.pdf-第7页.png

第7页 / 共451页

390fa1dd-3570-4204-929e-fbe3fb8d3766.pdf-第8页.png

第8页 / 共451页

Foreword

Cover

Table of Contents

About the Authors and Contributors

Juan Pablo González

Cindy Meister

Suat Ozgur

Bill Dilworth

Nico Altink

Contributors

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

Using AutoFilter on a Protected Sheet

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

Formatting Your Calendar

Changing the Page Settings

Changing the Font

Changing the Borders

Inserting a Picture with Caption

Making Changes

Linking in a Picture

Inserting a Picture Without a Frame

Leaving off the Caption

Controlling the Picture Size

Setting Exact Height and Width

Adding Borders

Positioning the Frame

Wrapping Text Around the Frame

Changing the Path for Graphics Files

Associating a Picture with a Page

Forms: Suppressing New Paragraphs in Form Fields

Forms: Formatting Text Input in Form Fields

Changing Other Types of Formatting

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

Generating the Document Using VBA

Using Forms Protection

Macros in Files Opened by Code

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 List Box

Validating Data

Moving Rows Between List Boxes

Moving Rows in List Boxes

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

Back Cover

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 Table of Contents F o r e w o r d . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . a A b o u t t h e A u t h o r s a n d C o n t r i b ut o r s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . c Juan Pablo González .......................................................................................................................... c Cindy Meister ......................................................................................................................................d Suat Ozgur ..........................................................................................................................................d Bill Dilworth.........................................................................................................................................e Nico Altink ...........................................................................................................................................e Contributors .........................................................................................................................................f I n t r o d u c t i on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 What is VBA?.......................................................................................................................................1 Save Time .....................................................................................................................................1 Take Advantage of UserForms.....................................................................................................1 Develop User-defined Functions .................................................................................................2 Enjoy Security ...............................................................................................................................2 H o w t o U s e T h i s B o o k . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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 I n t r o d u c i ng t h e V i s u al B a s i c E d i t o r . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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 E x c e l P r o c e d u r e s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 9 Printing All Files ............................................................................................................................... 19 Saving a Workbook as Today's Date............................................................................................... 21 Highlighting Duplicates Within a Range ......................................................................................... 23 Office VBA: Macros You Can Use Today page i

Table of Contents 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 page ii Office VBA: Macros You Can Use Today

Table of Contents W o r d P r o c e d u r e s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1 9 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 Office VBA: Macros You Can Use Today page iii

Table of Contents 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 O u t l o o k P ro c e d u re s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 6 9 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 P o w e r P o i n t P r o c e d u re s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 0 5 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 A c c e s s P r oc e d u re s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 2 7 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 page iv Office VBA: Macros You Can Use Today

分享到：

赞收藏

资料库

VBA OFFICE.pdf

相关推荐

后端

热门标签

最新资料