دانلود کتاب Mastering VBA for Microsoft Office 2013

فهرست مطالب :

Cover......Page 1
Title Page......Page 3
Copyright......Page 4
Contents......Page 13
Part 1 Recording Macros and Getting Started with VBA......Page 39
What Is VBA and What Can You Do with It?......Page 41
The Difference between Visual Basic and Visual Basic for Applications......Page 42
Understanding Macro Basics......Page 43
Displaying the Developer Tab on the Ribbon......Page 44
Planning the Macro......Page 45
Starting the Macro Recorder......Page 46
Naming the Macro......Page 49
Choosing How to Run a New Macro......Page 53
Running a Macro......Page 59
Recording a Sample Word Macro......Page 60
Record the Macro......Page 63
Deleting a Macro......Page 65
The Bottom Line......Page 67
Opening the Visual Basic Editor......Page 69
Opening the Visual Basic Editor Directly......Page 70
Navigating to a Macro......Page 71
The Project Explorer......Page 72
The Object Browser......Page 76
The Code Window......Page 77
The Properties Window......Page 81
The Immediate Window......Page 83
Setting Properties for a Project......Page 84
Customizing the Visual Basic Editor......Page 86
Choosing Editor and View Preferences......Page 87
Customizing the Toolbar and Menu Bar......Page 94
Customizing the Toolbox......Page 97
The Bottom Line......Page 101
Chapter 3 Editing Recorded Macros......Page 103
Testing a Macro in the Visual Basic Editor......Page 104
Stepping through a Macro......Page 105
Setting Breakpoints......Page 106
Commenting Out Lines......Page 107
Stepping Out of a Macro......Page 108
Editing the Word Macro......Page 109
Creating a Transpose_Word_Left Macro......Page 110
Unhiding the Personal Macro Workbook......Page 112
Opening the Macro for Editing......Page 113
Editing the Macro......Page 114
Editing a PowerPoint Macro......Page 117
Save Your Work......Page 122
The Bottom Line......Page 123
Setting Up the Visual Basic Editor for Creating the Procedures......Page 125
Creating a Procedure for Word......Page 127
Creating a Procedure for Excel......Page 132
Creating a Procedure for PowerPoint......Page 137
Creating a Procedure for Access......Page 143
The Bottom Line......Page 144
Part 2 Learning How to Work with VBA......Page 145
Getting Ready......Page 147
Procedures......Page 148
Subprocedures......Page 149
Statements......Page 150
Keywords......Page 153
Variables......Page 154
Constants......Page 155
Arguments......Page 156
Specifying Argument Names vs. Omitting Argument Names......Page 157
Collections......Page 158
Events......Page 159
The Bottom Line......Page 161
Chapter 6 Working with Variables, Constants, and Enumerations......Page 163
Choosing Names for Variables......Page 164
Declaring a Variable......Page 165
Choosing the Scope and Lifetime of a Variable......Page 168
Specifying the Data Type for a Variable......Page 175
Declaring Your Own Constants......Page 181
Working with Enumerations......Page 182
The Bottom Line......Page 183
What Is an Array?......Page 185
Declaring an Array......Page 187
Storing Values in an Array......Page 189
Declaring a Dynamic Array......Page 190
Returning Information from an Array......Page 191
Sorting an Array......Page 192
Performing a Linear Search through an Array......Page 196
Performing a Binary Search through an Array......Page 201
The Bottom Line......Page 206
The Benefits of OOP......Page 209
Properties......Page 211
Methods......Page 212
Working with Collections......Page 214
Finding the Objects You Need......Page 215
Using the Macro Recorder to Add Code for the Objects You Need......Page 216
Using the Object Browser......Page 218
Using the Auto List Members Feature......Page 227
Using Object Variables to Represent Objects......Page 229
Team Programming and OOP......Page 232
The Bottom Line......Page 233
Part 3 Making Decisions and Using Loops and Functions......Page 235
What Is a Function?......Page 237
Using Functions......Page 238
Passing Arguments to a Function......Page 240
Using Functions to Convert Data......Page 241
Using the Asc Function to Return a Character Code......Page 243
Using the Val Function to Extract a Number from the Start of a String......Page 244
Using the Str Function to Convert a Number into a String......Page 245
Using the Format Function to Format an Expression......Page 246
Using the Chr Function and Constants to Enter Special Characters in a String......Page 250
Using Functions to Manipulate Strings......Page 252
Using the Left, Right, and Mid Functions to Return Part of a String......Page 253
Using InStr and InStrRev to Find a String within Another String......Page 257
Using LTrim, RTrim, and Trim to Remove Spaces from a String......Page 259
Using Len to Check the Length of a String......Page 260
Using StrConv, LCase, and UCase to Change the Case of a String......Page 262
Using the StrComp Function to Compare Apples to Apples......Page 263
Using VBA’s Mathematical Functions......Page 264
Using VBA’s Date and Time Functions......Page 265
Using the DatePart Function to Parse Dates......Page 266
Using the DateDiff Function to Figure Out a Time Interval......Page 267
Using the Dir Function to Check Whether a File Exists......Page 268
The Bottom Line......Page 270
Chapter 10 Creating Your Own Functions......Page 273
Components of a Function......Page 274
Starting a Function Manually......Page 276
Passing Arguments to a Function......Page 277
Specifying an Optional Argument......Page 278
Examples of Functions for Any VBA-Enabled Office Application......Page 279
Returning Text Data from a Function......Page 281
Creating a Function for Word......Page 284
Creating a Function for Excel......Page 286
Creating a Function for PowerPoint......Page 287
Creating a Function for Access......Page 289
The Bottom Line......Page 290
How Do You Compare Things in VBA?......Page 293
Testing Multiple Conditions by Using Logical Operators......Page 295
If Blocks......Page 297
If… Then......Page 298
If… Then… Else Statements......Page 300
If… Then… ElseIf… Else Statements......Page 302
Creating Loops with If and GoTo......Page 306
Nesting If Blocks......Page 307
Example......Page 310
When Order Matters......Page 312
The Bottom Line......Page 313
When Should You Use a Loop?......Page 315
Understanding the Basics of Loops......Page 316
For… Next Loops......Page 317
For Each… Next Loops......Page 325
Using Do… Loops for Variable Numbers of Repetitions......Page 326
Do While… Loop Loops......Page 327
Do… Loop While Loops......Page 331
Do Until… Loop Loops......Page 332
Do… Loop Until Loops......Page 335
Using an Exit Do Statement......Page 336
Is the Exit Do Statement Bad Practice?......Page 337
While… Wend Loops......Page 338
Nesting Loops......Page 339
Avoiding Infinite Loops......Page 341
The Bottom Line......Page 342
Part 4 Using Message Boxes, Input Boxes, and Dialog Boxes......Page 343
Chapter 13 Getting User Input with Message Boxes and Input Boxes......Page 345
Opening a Procedure to Work On......Page 346
Displaying Status Bar Messages in Word and Excel......Page 347
The Pros and Cons of Message Boxes......Page 349
Message Box Syntax......Page 350
Displaying a Simple Message Box......Page 351
Displaying a Multiline Message Box......Page 352
Choosing Buttons for a Message Box......Page 353
Choosing an Icon for a Message Box......Page 354
Setting a Default Button for a Message Box......Page 355
Controlling the Modality of a Message Box......Page 356
Specifying a Title for a Message Box......Page 357
Adding a Help Button to a Message Box......Page 358
Specifying a Help File for a Message Box......Page 359
Retrieving a Value from a Message Box......Page 360
Input Boxes......Page 361
Input Box Syntax......Page 362
Retrieving Input from an Input Box......Page 363
The Bottom Line......Page 364
When Should You Use a Custom Dialog Box?......Page 367
Creating a Custom Dialog Box......Page 368
Inserting a User Form......Page 370
Renaming a User Form......Page 373
Adding Controls to the User Form......Page 375
Renaming Controls......Page 380
Moving a Control......Page 381
Changing the Caption on a Control......Page 383
Key Properties of the Toolbox Controls......Page 385
Working with Groups of Controls......Page 401
Placing Controls......Page 404
Adjusting the Tab Order of a Form......Page 405
Linking a Form to a Procedure......Page 406
Loading and Unloading a Form......Page 407
Setting a Default Command Button......Page 408
Returning a Value from an Option Button......Page 409
Returning a Value from a List Box......Page 410
Word Example: The Move-Paragraph Procedure......Page 412
General Example: Opening a File from a List Box......Page 423
Creating the Code for the User Form......Page 426
Displaying a Built-in Dialog Box......Page 429
Which Button Did the User Choose in a Dialog Box?......Page 433
The Bottom Line......Page 434
Chapter 15 Creating Complex Forms......Page 437
Revealing a Hidden Part of a Form......Page 438
Tracking a Procedure in a Form......Page 443
Using Multipage Dialog Boxes and Tab-Strip Controls......Page 446
Creating a Modeless Dialog Box......Page 456
Specifying a Form’s Location on Screen......Page 457
Using Events to Control Forms......Page 458
Events Unique to the UserForm Object......Page 461
Events That Apply to Both UserForms and Container Controls......Page 466
Events That Apply to Many or Most Controls......Page 471
Events That Apply Only to a Few Controls......Page 484
The Bottom Line......Page 485
Part 5 Creating Effective Code......Page 487
What Is Modular Code?......Page 489
How to Approach Creating Modular Code......Page 490
Calling a Procedure......Page 491
Making Logical Improvements to Your Code......Page 493
Making Visual Improvements to Your Code......Page 500
What Can You Do with Class Modules?......Page 506
Planning Your Class......Page 507
Setting the Instancing Property......Page 508
Adding Properties to the Class......Page 509
Adding Methods to a Class......Page 514
Using Your Class......Page 515
The Bottom Line......Page 516
Principles of Debugging......Page 519
Compile Errors......Page 521
Runtime Errors......Page 524
VBA’s Debugging Tools......Page 525
Break Mode......Page 526
The Step Over and Step Out Commands......Page 528
The Locals Window......Page 529
The Watch Window......Page 530
The Immediate Window......Page 533
The Call Stack Dialog Box......Page 535
When Should You Write an Error Handler?......Page 536
Trapping an Error......Page 537
Resuming after an Error......Page 539
Getting a Description of an Error......Page 542
Handling User Interrupts in Word, Excel, and Project......Page 543
Disabling User Input While Part of a Procedure Is Running......Page 544
Documenting Your Code......Page 545
The Bottom Line......Page 546
What Is a Well-Behaved Procedure?......Page 549
Retaining or Restoring the User Environment......Page 550
Leaving the User in the Best Position to Continue Working......Page 551
Keeping the User Informed during the Procedure......Page 552
Displaying Information at the Beginning of a Procedure......Page 555
Creating a Log File......Page 556
Undoing Changes the Procedure Has Made......Page 560
Removing Scratch Files and Folders......Page 561
The Bottom Line......Page 562
Understanding How VBA Implements Security......Page 563
What Is a Digital Certificate?......Page 567
Getting a Digital Certificate......Page 568
Understanding the Security Threats Posed by VBA......Page 577
Specifying a Suitable Security Setting......Page 578
Additional Trust Center Settings......Page 579
Locking Your Code......Page 582
The Bottom Line......Page 584
Part 6 Programming the Office Applications......Page 585
Examining the Word Object Model......Page 587
Creating a Document......Page 590
Creating a Template......Page 591
Saving a Document......Page 592
Opening a Document......Page 597
Closing a Document......Page 600
Printing a Document......Page 601
Working with the ActiveDocument Object......Page 603
Checking the Type of Selection......Page 604
Checking the Story Type of the Selection......Page 605
Getting Other Information about the Current Selection......Page 607
Inserting Text at, after, or before the Selection......Page 610
Extending a Selection......Page 611
Collapsing a Selection......Page 612
Defining a Named Range......Page 613
Using the Duplicate Property to Store or Copy Formatting......Page 614
Setting a Default File Path......Page 615
Turning Off Track Changes......Page 616
Accessing OneNote......Page 617
The Bottom Line......Page 618
Using Find and Replace via VBA......Page 621
Understanding the Syntax for the Execute Method......Page 623
Putting Find and Replace to Work......Page 626
Understanding How VBA Implements Headers and Footers......Page 627
Linking to the Header or Footer in the Previous Section......Page 628
Adding Page Numbers to Your Headers and Footers......Page 629
Adding a Section to a Document......Page 633
Changing the Page Setup......Page 634
Splitting a Window......Page 635
Scrolling a Window......Page 636
Making Sure an Item Is Displayed in the Window......Page 637
Zooming the View to Display Multiple Pages......Page 638
Creating a Table......Page 639
Converting Text to a Table......Page 640
Finding Out Where a Selection Is within a Table......Page 642
Adding a Column to a Table......Page 644
Setting the Width of a Column......Page 645
Deleting a Row from a Table......Page 646
Inserting a Cell......Page 647
Deleting Cells......Page 648
Selecting a Range of Cells......Page 649
Converting a Table or Rows to Text......Page 650
The Bottom Line......Page 651
Getting an Overview of the Excel Object Model......Page 653
Understanding Excel’s Creatable Objects......Page 654
Creating a Workbook......Page 655
Saving a Workbook......Page 656
Accessing Cloud Storage......Page 659
Opening a Workbook......Page 660
Closing a Workbook......Page 662
Protecting a Workbook......Page 663
Inserting a Worksheet......Page 664
Deleting a Worksheet......Page 665
Printing a Worksheet......Page 666
Protecting a Worksheet......Page 668
Working with the Active Cell......Page 669
Creating a Named Range......Page 671
Working with the Special Cells......Page 673
Entering a Formula in a Cell......Page 674
Setting Options in the Application Object......Page 675
Accessing OneNote......Page 676
The Bottom Line......Page 677
Creating a Chart......Page 679
Working with Series in the Chart......Page 681
Working with a Chart Axis......Page 684
Working with Windows......Page 685
Arranging and Resizing Windows......Page 686
Searching with the Find Method......Page 688
Replacing with the Replace Method......Page 690
Adding Shapes......Page 691
The Bottom Line......Page 692
Getting an Overview of the PowerPoint Object Model......Page 693
Understanding PowerPoint’s Creatable Objects......Page 694
Creating a New Presentation Based on the Default Template......Page 695
Creating a New Presentation Based on a Template......Page 696
Saving a Presentation......Page 697
Exporting a Presentation or Some Slides to Graphics......Page 700
Applying a Template to a Presentation, to a Slide, or to a Range of Slides......Page 701
Working with Windows and Views......Page 702
Closing a Window......Page 703
Changing the View......Page 704
Working with Slides......Page 705
Inserting Slides from an Existing Presentation......Page 706
Finding a Slide by Its ID Number......Page 707
Moving a Slide......Page 708
Formatting a Slide......Page 709
Setting a Transition for a Slide, a Range of Slides, or a Master......Page 710
Working with the Title Master......Page 712
The Bottom Line......Page 713
Adding Shapes to Slides......Page 715
Repositioning and Resizing a Shape......Page 721
Working with Text in a Shape......Page 722
Animating a Shape or a Range of Shapes......Page 726
Displaying or Hiding a Header or Footer Object......Page 728
Setting the Format for Date and Time Headers and Footers......Page 729
Controlling the Show Type......Page 730
Creating a Custom Show......Page 731
Starting a Slide Show......Page 732
Pausing the Show and Using White and Black Screens......Page 733
The Bottom Line......Page 734
Getting an Overview of the Outlook Object Model......Page 735
Understanding Outlook’s Most Common Creatable Objects......Page 736
Working with the NameSpace Object......Page 737
Working with Inspectors and Explorers......Page 738
Understanding Inspectors and Explorers......Page 741
Creating Items......Page 742
Using the Delete Method......Page 744
Using the SaveAs Method......Page 746
Working with Messages......Page 747
Working with the Contents of a Message......Page 748
Adding an Attachment to a Message......Page 749
Working with the Contents of a Calendar Item......Page 750
Creating a Task......Page 751
Working with the Contents of a Task Item......Page 752
Searching for Items......Page 753
The Bottom Line......Page 755
Chapter 27 Working with Events in Outlook......Page 757
Working with Application-Level Events......Page 758
Using the Startup Event......Page 759
Using the Quit Event......Page 760
Using the NewMail and NewMailEx Events......Page 761
Using the AdvancedSearchComplete and the AdvancedSearchStopped Events......Page 762
Using the MAPILogonComplete Event......Page 763
Working with Item-Level Events......Page 764
Declaring an Object Variable and Initializing an Event......Page 765
Understanding the Events That Apply to All Message Items......Page 766
Understanding the Events That Apply to Explorers, Inspectors, and Views......Page 768
Understanding the Events That Apply to Folders......Page 771
Understanding the Events That Apply to Reminders......Page 772
Understanding the Events That Apply to Synchronization......Page 773
Understanding Quick Steps......Page 774
The Bottom Line......Page 775
Getting Started with VBA in Access......Page 777
Creating an Access-Style Macro to Run a Function......Page 779
Translating an Access-Style Macro into a VBA Macro......Page 781
Using an AutoExec Macro to Initialize an Access Session......Page 782
Understanding the Option Compare Database Statement......Page 783
Getting an Overview of the Access Object Model......Page 784
Understanding Creatable Objects in Access......Page 785
Closing the Current Database and Opening a Different Database......Page 786
Opening Multiple Databases at Once......Page 788
Closing a Database......Page 790
Working with the Screen Object......Page 791
Using the DoCmd Object to Run Access Commands......Page 793
Using the PrintOut Method to Print an Object......Page 797
Using the RunMacro Method to Run an Access-Style Macro......Page 798
The Bottom Line......Page 799
Understanding How to Proceed......Page 801
Establishing a Connection to the Database......Page 802
Opening a Recordset Using ADO......Page 803
Choosing How to Access the Data in an ADO Recordset......Page 805
Using the MoveFirst, MoveNext, MovePrevious, and MoveLast Methods......Page 813
Using the Move Method to Move by Multiple Records......Page 814
Searching for a Record in an ADO Recordset......Page 815
Searching for a Record in a DAO Recordset......Page 816
Returning the Fields in a Record......Page 817
Inserting and Deleting Records......Page 818
Saving a Recordset to the Cloud......Page 819
The Bottom Line......Page 820
Understanding the Tools Used to Communicate between Applications......Page 823
Using Automation to Transfer Information......Page 824
Understanding Early and Late Binding......Page 825
Returning an Object with the GetObject Function......Page 826
Examples of Using Automation with the Office Applications......Page 827
Using the Shell Function to Run an Application......Page 836
Using Data Objects to Store and Retrieve Information......Page 837
Storing Information in a Data Object......Page 838
Assigning Information to the Clipboard......Page 839
Finding Out Whether a Data Object Contains a Given Format......Page 840
Using DDEInitiate to Start a DDE Connection......Page 841
Using DDERequest to Return Text from Another Application......Page 842
Using DDEExecute to Have One Application Execute a Command in Another......Page 843
Communicating via SendKeys......Page 844
The Bottom Line......Page 849
Chapter 31 Programming the Office 2013 Ribbon......Page 851
Hiding the Editing Group on the Word Ribbon......Page 852
A Word of Warning......Page 857
XML Terminology......Page 858
Working with Excel and PowerPoint......Page 859
Selecting the Scope of Your Ribbon Customization......Page 860
Cautions about Customizing......Page 861
Two Ways to Find the Correct idMso......Page 863
Adding Callbacks......Page 864
Using Built-In Icons and ScreenTips......Page 866
Creating Your Own Icons......Page 867
Adding Menus......Page 868
Adding a DropDown List Control......Page 870
Using a DialogBoxLauncher......Page 872
Toggling with a Toggle Button Control......Page 873
Modifying the Ribbon in Access......Page 874
Testing Your New Ribbon......Page 877
Adding a Callback in Access......Page 878
Employ Error Message Tools......Page 879
Cure Common User Interface Programming Problems......Page 880
Where to Go from Here......Page 882
The Bottom Line......Page 883
Chapter 1: Recording and Running Macros in the Office Applications......Page 885
Chapter 2: Getting Started with the Visual Basic Editor......Page 886
Chapter 3: Editing Recorded Macros......Page 887
Chapter 4: Creating Code from Scratch in the Visual Basic Editor......Page 888
Chapter 5: Understanding the Essentials of VBA Syntax......Page 891
Chapter 6: Working with Variables, Constants, and Enumerations......Page 892
Chapter 7: Using Array Variables......Page 894
Chapter 8: Finding the Objects, Methods, and Properties You Need......Page 895
Chapter 9: Using Built-in Functions......Page 896
Chapter 10: Creating Your Own Functions......Page 897
Chapter 11: Making Decisions in Your Code......Page 899
Chapter 12: Using Loops to Repeat Actions......Page 900
Chapter 13: Getting User Input with Message Boxes and Input Boxes......Page 901
Chapter 14: Creating Simple Custom Dialog Boxes......Page 902
Chapter 15: Creating Complex Forms......Page 906
Chapter 16: Building Modular Code and Using Classes......Page 907
Chapter 17: Debugging Your Code and Handling Errors......Page 909
Chapter 18: Building Well-Behaved Code......Page 910
Chapter 19: Securing Your Code with VBA’s Security Features......Page 912
Chapter 20: Understanding the Word Object Model and Key Objects......Page 913
Chapter 21: Working with Widely Used Objects in Word......Page 915
Chapter 22: Understanding the Excel Object Model and Key Objects......Page 916
Chapter 24: Understanding the PowerPoint Object Model and Key Objects......Page 917
Chapter 26: Understanding the Outlook Object Model and Key Objects......Page 919
Chapter 27: Working with Events in Outlook......Page 920
Chapter 28: Understanding the Access Object Model and Key Objects......Page 921
Chapter 29: Manipulating the Data in an Access Database via VBA......Page 922
Chapter 30: Accessing One Application from Another Application......Page 923
Chapter 31: Programming the Office 2013 Ribbon......Page 924
Index......Page 927