Introduction 1
Part I: Some Essential Background
Chapter 1: Excel in a Nutshell 11
About Excel 11
Thinking in Terms of Objects 11
Workbooks 12
Excel's User Interface 16
Data Entry 25
Formulas, Functions, and Names 25
Selecting Objects 27
Formatting 28
Protection Options 29
Charts 32
Shapes and SmartArt 33
Database Access 33
Internet Features 35
Analysis Tools 36
Add-Ins 36
Macros and Programming 37
File Format 37
Excel's Help System 38
Chapter 2: Formula Tricks and Techniques 39
About Formulas 39
Calculating Formulas 40
Cell and Range References 41
Using Names 45
Formula Errors 51
Excel Auditing Tools 52
Array Formulas 61
Counting and Summing Techniques 64
Lookup Formulas 66
Working with Dates and Times 69
Creating Megaformulas 71
Chapter 3: Understanding Excel Files 75
Starting Excel 75
File Types 76
Excel File Compatibility 80
Protected View 81
Using AutoRecover 82
Working with Template Files 83
Inside an Excel File 86
The OfficeUI File 91
The XLB File 92
Add-In Files 93
Excel Settings in the Registry 93
Chapter 4: Essentials of Spreadsheet Application Development 97
What Is a Spreadsheet Application? 97
Steps for Application Development 98
Determining User Needs 99
Planning an Application That Meets User Needs 100
Determining the Most Appropriate User Interface 102
Concerning Yourself with the End User 106
Other Development Issues 113
Part II: Understanding Visual Basic for Applications
Chapter 5: Introducing Visual Basic for Applications 119
Getting Some BASIC Background 119
Delving into VBA 120
Covering the Basics of VBA 121
Introducing Visual Basic Editor 124
Working with Project Explorer 127
Working with Code Windows 129
Customizing the VBE Environment 137
The Macro Recorder 143
About Objects and Collections 151
Properties and Methods 153
The Comment Object: A Case Study 156
Some Useful Application Properties 164
Working with Range Objects 166
Things to Know about Objects 173
Chapter 6: VBA Programming Fundamentals 177
VBA Language Elements: An Overview 177
Comments 179
Variables, Data Types, and Constants 181
Assignment Statements 195
Arrays 197
Object Variables 199
User-Defined Data Types 201
Built-in Functions 202
Manipulating Objects and Collections 205
Controlling Code Execution 208
Chapter 7: Working with VBA Sub Procedures 227
About Procedures 227
Executing Sub Procedures 230
Passing Arguments to Procedures 241
Error-Handling Techniques 245
A Realistic Example That Uses Sub Procedures 250
Chapter 8: Creating Function Procedures 269
Sub Procedures versus Function Procedures 269
Why Create Custom Functions? 270
An Introductory Function Example 271
Function Procedures 275
Function Arguments 281
Function Examples 282
Emulating Excel's SUM function 298
Extended Date Functions 302
Debugging Functions 304
Dealing with the Insert Function Dialog Box 305
Using Add-ins to Store Custom Functions 311
Using the Windows API 311
Chapter 9: VBA Programming Examples and Techniques 317
Learning by Example 317
Working with Ranges 318
Working with Workbooks and Sheets 348
VBA Techniques 353
Some Useful Functions for Use in Your Code 361
Some Useful Worksheet Functions 367
Windows API Calls 385
Part III: Working with UserForms
Chapter 10: Custom Dialog Box Alternatives 395
Before You Create That UserForm 395
Using an Input Box 395
The VBA MsgBox Function 401
The Excel GetOpenFilename Method 406
The Excel GetSaveAsFilename Method 410
Prompting for a Directory 410
Displaying Excel's Built-In Dialog Boxes 411
Displaying a Data Form 413
Chapter 11: Introducing UserForms 417
How Excel Handles Custom Dialog Boxes 417
Inserting a New UserForm 418
Adding Controls to a UserForm 419
Toolbox Controls 419
Adjusting UserForm Controls 424
Adjusting a Control's Properties 425
Displaying a UserForm 429
Closing a UserForm 432
Creating a UserForm: An Example 433
Understanding UserForm Events 440
Referencing UserForm Controls 446
Customizing the Toolbox 448
Creating UserForm Templates 450
A UserForm Checklist 451
Chapter 12: UserForm Examples 453
Creating a UserForm "Menu" 453
Selecting Ranges from a UserForm 456
Creating a Splash Screen 458
Disabling a UserForm's Close Button 460
Changing a UserForm's Size 460
Zooming and Scrolling a Sheet from a UserForm 462
ListBox Techniques 464
Using the MultiPage Control in a UserForm 483
Using an External Control 484
Animating a Label 486
Chapter 13: Advanced UserForm Techniques 491
A Modeless Dialog Box 492
Displaying a Progress Indicator 496
Creating Wizards 505
Emulating the MsgBox Function 511
A UserForm with Movable Controls 515
A UserForm with No Title Bar 517
Simulating a Toolbar with a UserForm 519
Emulating a Task Pane with a UserForm 521
A Resizable UserForm 522
Handling Multiple UserForm Controls with One Event Handler 527
Selecting a Color in a UserForm 530
Displaying a Chart in a UserForm 532
Making a UserForm Semitransparent 533
An Enhanced Data Form 535
A Puzzle on a UserForm 538
Video Poker on a UserForm 539
Part IV: Advanced Programming Techniques
Chapter 14: Developing Excel Utilities with VBA 543
About Excel Utilities 543
Using VBA to Develop Utilities 544
What Makes a Good Utility? 545
Text Tools: The Anatomy of a Utility 545
More about Excel Utilities 563
Chapter 15: Working with Pivot Tables 565
An Introductory Pivot Table Example 565
Creating a More Complex Pivot Table 571
Creating Multiple Pivot Tables 576
Creating a Reverse Pivot Table 579
Chapter 16: Working with Charts 583
Getting the Inside Scoop on Charts 583
Creating an Embedded Chart 586
Creating a Chart on a Chart Sheet 588
Modifying Charts 589
Using VBA to Activate a Chart 591
Moving a Chart 592
Using VBA to Deactivate a Chart 592
Determining Whether a Chart Is Activated 593
Deleting from the ChartObjects or Charts Collection 594
Looping through All Charts 596
Sizing and Aligning ChartObjects 598
Creating Lots of Charts 600
Exporting a Chart 603
Changing the Data Used in a Chart 605
Using VBA to Display Arbitrary Data Labels on a Chart 611
Displaying a Chart in a UserForm 615
Understanding Chart Events 617
Discovering VBA Charting Tricks 625
Animating Charts 632
Creating an Interactive Chart without VBA 637
Working with Sparkline Charts 641
Chapter 17: Understanding Excel's Events 645
What You Should Know about Events 645
Getting Acquainted with Workbook-Level Events 653
Examining Worksheet Events 661
Checking Out Chart Events 670
Monitoring with Application Events 672
Using UserForm Events 676
Accessing Events Not Associated with an Object 678
Chapter 18: Interacting with Other Applications 685
Starting an Application from Excel 685
Activating an Application with Excel 690
Running Control Panel Dialog Boxes 691
Using Automation in Excel 693
Sending Personalized E-Mail via Outlook 704
Sending E-Mail Attachments from Excel 707
Chapter 19: Creating and Using Add-Ins 711
What Is an Add-In? 711
Understanding Excel's Add-In Manager 714
Creating an Add-in 716
An Add-In Example 717
Comparing XLAM and XLSM Files 723
Manipulating Add-Ins with VBA 729
Optimizing the Performance of Add-ins 735
Special Problems with Add-Ins 736
Part V: Developing Applications
Chapter 20: Working with the Ribbon 743
Ribbon Basics 743
Using VBA with the Ribbon 747
Customizing the Ribbon 753
Creating an Old-Style Toolbar 775
Chapter 21: Working with Shortcut Menus 779
CommandBar Overview 779
Using VBA to Customize Shortcut Menus 786
Shortcut Menus and Events 796
Chapter 22: Providing Help for Your Applications 801
Help for Your Excel Applications 801
Help Systems That Use Excel Components 804
Displaying Help in a Web Browser 812
Using the HTML Help System 814
Chapter 23: Developing User-Oriented Applications 821
What Is a User-Oriented Application? 821
the Loan Amortization Wizard 822
Application Development Concepts 832
Part VI: Other Topics
Chapter 24: Compatibility Issues 837
What Is Compatibility? 837
Types of Compatibility Problems 838
Avoid Using New Features 839
But Will It Work on a Mac? 841
Dealing with 64-bit Excel 842
Creating an International Application 843
Chapter 25: Manipulating Files with VBA 851
Performing Common File Operations 851
Displaying Extended File Information 861
Working with Text Files 863
Text File Manipulation Examples 867
Zipping and Unzipping Files 879
Working with ADO 882
Chapter 26: Manipulating Visual Basic Components 885
Introducing IDE 885
The IDE Object Model 888
Displaying All Components in a VBA Project 891
Listing All VBA Procedures in a Workbook 892
Replacing a Module with an Updated Version 894
Using VBA to Write VBA Code 896
Adding Controls to a UserForm at Design Time 898
Creating UserForms Programmatically 902
Chapter 27: Understanding Class Modules 911
What Is a Class Module? 911
Example: Creating a NumLock Class 912
More about Class Modules 918
Example: A CSV File Class 921
Chapter 28: Working with Colors 927
Specifying Colors 927
Understanding Grayscale 933
Experimenting with Colors 935
Understanding Document Themes 937
Working with Shape Objects 943
Modifying Chart Colors 948
Chapter 29: Frequently Asked Questions about Excel Programming 953
Getting the Scoop on FAQs 953
General Excel Questions 954
Visual Basic Editor 958
Procedures 960
Functions 964
Objects, Properties, Methods, and Events 967
Security-Related Issues 977
UserForms 978
Add-Ins 982
User Interface 984
Part VII: Appendixes
Appendix A: VBA Statements and Functions Reference 989
Invoking Excel functions in VBA instructions 992
Appendix B: VBA Error Codes 999
Appendix C: This Book's Website 1003
Index 1019
![Microsoft Office 365 Non-Tech Savvy Bible : [10 in 1]: From Beginner to Pro with This Fully Illustrated Guide- Word, Excel, PowerPoint, Access, Outlook, Skype, Teams, OneDrive, OneNote, and Publisher - Alan Norwood](https://www.booktopia.com.au/covers/200/9781915825483/6033/microsoft-office-365-non-tech-savvy-bible.jpg)
