Excel 2016 Power Programming with VBA
John Wiley & Sons (Verlag)
978-1-119-06772-6 (ISBN)
Excel 2016 Power Programming with VBA is fully updated to cover all the latest tools and tricks of Excel 2016.
Excel 2016 Power Programming with VBA is fully updated to cover all the latest tools and tricks of Excel 2016. Encompassing an analysis of Excel application development and a complete introduction to Visual Basic for Applications (VBA), this comprehensive book presents all of the techniques you need to develop both large and small Excel applications.
Over 800 pages of tips, tricks, and best practices shed light on key topics, such as the Excel interface, file formats, enhanced interactivity with other Office applications, and improved collaboration features. In addition to the procedures, tips, and ideas that will expand your capabilities, this resource provides you with access to over 100 online example Excel workbooks and the Power Utility Pak, found on the Mr. Spreadsheet website.
Understanding how to leverage VBA to improve your Excel programming skills can enhance the quality of deliverables that you produce—and can help you take your career to the next level.
- Explore fully updated content that offers comprehensive coverage through over 900 pages of tips, tricks, and techniques
- Leverage templates and worksheets that put your new knowledge in action, and reinforce the skills introduced in the text
- Access online resources, including the Power Utility Pak, that supplement the content
- Improve your capabilities regarding Excel programming with VBA, unlocking more of your potential in the office
Michael Alexander is a Microsoft Certified Application Developer and author of several books on Microsoft Access and Excel. He runs a free tutorial site at datapigtechnologies.com.
John Walkenbach is arguably the foremost authority on Excel. He has written more than 30 books and maintains the popular Spreadsheet Page at http://spreadsheetpage.com
Dick Kusleika has been helping users get the most out of Microsoft Office products for more than fifteen years through online forums, blogging, books, and conferences.
ntroduction xxvii
Part I: Introduction to Excel VBA
Chapter 1: Essentials of Spreadsheet Application Development 3
What Is a Spreadsheet Application? 3
Steps for Application Development 4
Determining User Needs 5
Planning an Application That Meets User Needs 6
Determining the Most Appropriate User Interface 8
Customizing the Ribbon 8
Customizing shortcut menus 8
Creating shortcut keys 9
Creating custom dialog boxes 9
Using ActiveX controls on a worksheet 10
Executing the development effort 11
Concerning Yourself with the End User 12
Testing the application 12
Making the application bulletproof 13
Making the application aesthetically appealing and intuitive 15
Creating a user Help system 16
Documenting the development effort 16
Distributing the application to the user 16
Updating the application when necessary 17
Other Development Issues 17
The user’s installed version of Excel 17
Language issues 17
System speed 18
Video modes 18
Chapter 2: Introducing Visual Basic for Applications 19
Getting a Head Start with the Macro Recorder 19
Creating your first macro 20
Comparing absolute and relative macro recording 23
Other macro recording concepts 27
Working with the Visual Basic Editor 32
Understanding VBE components 32
Working with the Project window 33
Working with a Code window 36
Customizing the VBA environment 38
The Editor Format tab 40
The General tab 41
The Docking tab 41
VBA Fundamentals 42
Understanding objects 43
Understanding collections 43
Understanding properties 44
Deep Dive: Working with Range Objects 47
Finding the properties of the Range object 48
The Range property 48
The Cells property 49
The Offset property 52
Essential Concepts to Remember 53
Don’t Panic—You Are Not Alone 55
Read the rest of the book 55
Let Excel help write your macro 55
Use the Help system 55
Use the Object Browser 56
Pilfer code from the Internet 57
Leverage user forums 57
Visit expert blogs 58
Mine YouTube for video training 58
Learn from the Microsoft Office Dev Center 59
Dissect the other Excel files in your organization 59
Ask your local Excel genius 59
Chapter 3: VBA Programming Fundamentals 61
VBA Language Elements: An Overview 61
Comments 63
Variables, Data Types, and Constants 64
Defining data types 65
Declaring variables 67
Scoping variables 69
Working with constants 72
Working with strings 73
Working with dates 74
Assignment Statements 75
Arrays 77
Declaring arrays 77
Declaring multidimensional arrays 78
Declaring dynamic arrays 79
Object Variables 79
User-Defined Data Types 80
Built-In Functions 81
Manipulating Objects and Collections 84
With-End With constructs 84
For Each-Next constructs 85
Controlling Code Execution 87
GoTo statements 88
If-Then constructs 88
Select Case constructs 93
Looping blocks of instructions 96
Chapter 4: Working with VBA Sub Procedures 105
About Procedures 105
Declaring a Sub procedure 106
Scoping a procedure 107
Executing Sub Procedures 108
Executing a procedure with the Run Sub/UserForm command 109
Executing a procedure from the Macro dialog box 109
Executing a procedure with a Ctrl+shortcut key combination 110
Executing a procedure from the Ribbon 111
Executing a procedure from a customized shortcut menu 111
Executing a procedure from another procedure 112
Executing a procedure by clicking an object 116
Executing a procedure when an event occurs 118
Executing a procedure from the Immediate window 118
Passing Arguments to Procedures 119
Error-Handling Techniques 123
Trapping errors 123
Error-handling examples 124
A Realistic Example That Uses Sub Procedures 127
The goal 128
Project requirements 128
What you know 128
The approach 129
Some preliminary recording 129
Initial setup 131
Code writing 132
Writing the Sort procedure 133
More testing 137
Fixing the problems 138
Utility availability 141
Evaluating the project 142
Chapter 5: Creating Function Procedures 143
Sub Procedures versus Function Procedures 143
Why Create Custom Functions? 144
An Introductory Function Example 144
Using the function in a worksheet 145
Using the function in a VBA procedure 146
Analyzing the custom function 146
Function Procedures 148
A function’s scope 150
Executing function procedures 150
Function Arguments 153
Function Examples 153
Functions with no argument 153
A function with one argument 156
A function with two arguments 159
A function with an array argument 159
A function with optional arguments 160
A function that returns a VBA array 162
A function that returns an error value 165
A function with an indefinite number of arguments 166
Emulating Excel’s SUM Function 168
Extended Date Functions 171
Debugging Functions 173
Dealing with the Insert Function Dialog Box 174
Using the MacroOptions method 174
Specifying a function category 176
Adding a function description manually 177
Using Add-Ins to Store Custom Functions 178
Using the Windows API 179
Windows API examples 179
Determining the Windows directory 180
Detecting the Shift key 181
Learning more about API functions 182
Chapter 6: Understanding Excel’s Events 183
What You Should Know about Events 183
Understanding event sequences 184
Where to put event-handler procedures 184
Disabling events 186
Entering event-handler code 187
Event-handler procedures that use arguments 188
Getting Acquainted with Workbook-Level Events 190
The Open event 191
The Activate event 192
The SheetActivate event 192
The NewSheet event 193
The BeforeSave event 193
The Deactivate event 193
The BeforePrint event 194
The BeforeClose event 195
Examining Worksheet Events 197
The Change event 198
Monitoring a specific range for changes 199
The SelectionChange event 203
The BeforeDoubleClick event 204
The BeforeRightClick event 205
Monitoring with Application Events 206
Enabling Application-level events 207
Determining when a workbook is opened 208
Monitoring Application-level events 209
Accessing Events Not Associated with an Object 210
The OnTime event 210
The OnKey event 212
Chapter 7: VBA Programming Examples and Techniques 217
Learning by Example 217
Working with Ranges 218
Copying a range 218
Moving a range 219
Copying a variably sized range 220
Selecting or otherwise identifying various types of ranges 221
Resizing a range 223
Prompting for a cell value 224
Entering a value in the next empty cell 225
Pausing a macro to get a user-selected range 226
Counting selected cells 228
Determining the type of selected range 229
Looping through a selected range efficiently 231
Deleting all empty rows 233
Duplicating rows a variable number of times 234
Determining whether a range is contained in another range 236
Determining a cell’s data type 237
Reading and writing ranges 238
A better way to write to a range 240
Transferring one-dimensional arrays 242
Transferring a range to a variant array 242
Selecting cells by value 243
Copying a noncontiguous range 244
Working with Workbooks and Sheets 246
Saving all workbooks 246
Saving and closing all workbooks 247
Hiding all but the selection 247
Creating a hyperlink table of contents 249
Synchronizing worksheets 250
VBA Techniques 251
Toggling a Boolean property 251
Displaying the date and time 251
Displaying friendly time 253
Getting a list of fonts 254
Sorting an array 256
Processing a series of files 257
Some Useful Functions for Use in Your Code 259
The FileExists function 259
The FileNameOnly function 259
The PathExists function 260
The RangeNameExists function 260
The SheetExists function 261
The WorkbookIsOpen function 261
Retrieving a value from a closed workbook 262
Some Useful Worksheet Functions 264
Returning cell formatting information 264
A talking worksheet 266
Displaying the date when a file was saved or printed 266
Understanding object parents 267
Counting cells between two values 268
Determining the last nonempty cell in a column or row 269
Does a string match a pattern? 270
Extracting the nth element from a string 272
Spelling out a number 272
A multifunctional function 273
The SHEETOFFSET function 274
Returning the maximum value across all worksheets 275
Returning an array of nonduplicated random integers 276
Randomizing a range 278
Sorting a range 279
Windows API Calls 280
Understanding API Declarations 280
Determining file associations 281
Determining default printer information 282
Determining video display information 283
Reading from and writing to the Registry 284
Part II: Advanced VBA Techniques
Chapter 8: Working with Pivot Tables 291
An Introductory Pivot Table Example 291
Creating a pivot table 292
Examining the recorded code for the pivot table 294
Cleaning up the recorded pivot table code 294
Creating a More Complex Pivot Table 297
The code that created the pivot table 298
How the more complex pivot table works 299
Creating Multiple Pivot Tables 301
Creating a Reverse Pivot Table304
Chapter 9: Working with Charts 307
Getting the Inside Scoop on Charts 307
Chart locations 307
The macro recorder and charts 308
The Chart object model 308
Creating an Embedded Chart 310
Creating a Chart on a Chart Sheet 311
Modifying Charts 312
Using VBA to Activate a Chart 313
Moving a Chart 314
Using VBA to Deactivate a Chart 315
Determining Whether a Chart Is Activated 316
Deleting from the ChartObjects or Charts Collection 316
Looping through All Charts 318
Sizing and Aligning ChartObjects 320
Creating Lots of Charts 321
Exporting a Chart 324
Exporting all graphics 325
Changing the Data Used in a Chart 326
Changing chart data based on the active cell 327
Using VBA to determine the ranges used in a chart 329
Using VBA to Display Arbitrary Data Labels on a Chart 331
Displaying a Chart in a UserForm 335
Understanding Chart Events 337
An example of using Chart events 338
Enabling events for an embedded chart 340
Example: Using Chart events with an embedded chart 342
Discovering VBA Charting Tricks 344
Printing embedded charts on a full page 344
Creating unlinked charts 344
Displaying text with the MouseOver event 346
Scrolling a chart 349
Working with Sparkline Charts 351
Chapter 10: Interacting with Other Applications 355
Understanding Microsoft Office Automation 355
Understanding the concept of binding 356
A simple automation example 358
Automating Access from Excel 359
Running an Access Query from Excel 359
Running an Access Macro from Excel 360
Automating Word from Excel 361
Sending Excel data to a Word document 361
Simulating mail merge with a Word document 362
Automating PowerPoint from Excel 364
Sending Excel data to a PowerPoint presentation 365
Sending all Excel charts to a PowerPoint presentation 366
Convert a workbook into a PowerPoint presentation 367
Automating Outlook from Excel 369
Mailing the Active Workbook as an Attachment 369
Mailing a Specific Range as an Attachment 370
Mailing a Single Sheet as an Attachment 371
Mailing All Email Addresses in Your Contact List 372
Starting Other Applications from Excel 373
Using the VBA Shell function 373
Using the Windows ShellExecute API function 376
Using AppActivate 377
Running Control Panel dialog boxes 378
Chapter 11: Working with External Data and Files 379
Working with External Data Connections 379
Manually creating a connection 379
Manually editing data connections 383
Using VBA to create dynamic connections 384
Iterating through all connections in a workbook 386
Using ADO and VBA to Pull External Data 387
The connection string 388
Declaring a Recordset 389
Referencing the ADO object library 390
Putting it all together in code 391
Using ADO with the active workbook 392
Working with Text Files 394
Opening a text file 395
Reading a text file 396
Writing a text file 396
Getting a file number 396
Determining or setting the file position 397
Statements for reading and writing 397
Text File Manipulation Examples 398
Importing data in a text file 398
Exporting a range to a text file 398
Importing a text file to a range 399
Logging Excel usage 400
Filtering a text file 401
Performing Common File Operations 402
Using VBA file-related statements 402
Using the FileSystemObject object 407
Zipping and Unzipping Files 410
Zipping files 410
Unzipping a file 411
Part III: Working with UserForms
Chapter 12: Leveraging Custom Dialog Boxes 415
Before You Create That UserForm 415
Using an Input Box 415
The VBA InputBox function 416
The Application.InputBox method 418
The VBA MsgBox Function 421
The Excel GetOpenFilename Method 426
The Excel GetSaveAsFilename Method 429
Prompting for a Directory 430
Displaying Excel’s Built-In Dialog Boxes 430
Displaying a Data Form 433
Making the data form accessible 434
Displaying a data form by using VBA 434
Chapter 13: Introducing UserForms 435
How Excel Handles Custom Dialog Boxes 435
Inserting a New UserForm 436
Adding Controls to a UserForm 437
Toolbox Controls 437
CheckBox 438
ComboBox 438
CommandButton 438
Frame 439
Image 439
Label 439
ListBox 439
MultiPage 439
OptionButton 439
RefEdit 440
ScrollBar 440
SpinButton 440
TabStrip 440
TextBox 440
ToggleButton 440
Adjusting UserForm Controls 442
Adjusting a Control’s Properties 443
Using the Properties window 443
Common properties 445
Accommodating keyboard users 447
Displaying a UserForm 449
Adjusting the display position 449
Displaying a modeless UserForm 450
Displaying a UserForm based on a variable 450
Loading a UserForm 450
About event-handler procedures 451
Closing a UserForm 451
Creating a UserForm: An Example 453
Creating the UserForm 453
Writing code to display the dialog box 455
Testing the dialog box 456
Adding event-handler procedures 457
The finished dialog box 459
Understanding UserForm Events 459
Learning about events 459
UserForm events 460
SpinButton events 461
Pairing a SpinButton with a TextBox 462
Referencing UserForm Controls 465
Customizing the Toolbox 466
Adding new pages to the Toolbox 467
Customizing or combining controls 467
Adding new ActiveX controls 468
Creating UserForm Templates 469
A UserForm Checklist 469
Chapter 14: UserForm Examples 471
Creating a UserForm “Menu”471
Using CommandButtons in a UserForm 471
Using a ListBox in a UserForm 472
Selecting Ranges from a UserForm 474
Creating a Splash Screen 476
Disabling a UserForm's Close Button 478
Changing a UserForm's Size 479
Zooming and Scrolling a Sheet from a UserForm 480
ListBox Techniques 482
Adding items to a ListBox control 483
Determining the selected item in a ListBox 487
Determining multiple selections in a ListBox 488
Multiple lists in a single ListBox 489
ListBox item transfer 490
Moving items in a ListBox 492
Working with multicolumn ListBox controls 494
Using a ListBox to select worksheet rows 496
Using a ListBox to activate a sheet 498
Filtering a ListBox from a TextBox 501
Using the MultiPage Control in a UserForm 503
Using an External Control 504
Animating a Label 506
Chapter 15: Advanced UserForm Techniques 511
A Modeless Dialog Box 512
Displaying a Progress Indicator 516
Creating a stand-alone progress indicator 517
Showing a progress indicator that’s integrated into a UserForm 520
Creating a non-graphical progress indicator 524
Creating Wizards 527
Setting up the MultiPage control for the wizard 528
Adding the buttons to the wizard’s UserForm 528
Programming the wizard’s buttons 528
Programming dependencies in a wizard 530
Performing the task with the wizard 532
Emulating the MsgBox Function 533
MsgBox emulation: MyMsgBox code 533
How the MyMsgBox function works 535
Using the MyMsgBox function 537
A UserForm with Movable Controls 537
A UserForm with No Title Bar 538
Simulating a Toolbar with a UserForm 540
Emulating a Task Pane with a UserForm 542
A Resizable UserForm 543
Handling Multiple UserForm Controls with One Event Handler 547
Selecting a Color in a UserForm 550
Displaying a Chart in a UserForm 552
Saving a chart as a GIF file 553
Changing the Image control’s Picture property 554
Making a UserForm Semitransparent 554
A Puzzle on a UserForm 556
Video Poker on a UserForm 557
Part IV: Developing Excel Applications
Chapter 16: Creating and Using Add-Ins 561
What Is an Add-In? 561
Comparing an add-in with a standard workbook 561
Why create add-ins? 562
Understanding Excel’s Add-In Manager 564
Creating an Add-in 566
An Add-In Example 567
Adding descriptive information for the example add-in 568
Creating an add-in 568
Installing an add-in 570
Testing the add-in 571
Distributing an add-in 571
Modifying an add-in 571
Comparing XLAM and XLSM Files 572
XLAM file VBA collection membership 573
Visibility of XLSM and XLAM files 573
Worksheets and chart sheets in XLSM and XLAM files 574
Accessing VBA procedures in an add-in 575
Manipulating Add-Ins with VBA 578
Adding an item to the AddIns collection 579
Removing an item from the AddIns collection 580
AddIn object properties 580
Accessing an add-in as a workbook 583
AddIn object events 584
Optimizing the Performance of Add-Ins 584
Special Problems with Add-Ins 585
Ensuring that an add-in is installed 585
Referencing other files from an add-in 587
Detecting the proper Excel version for your add-in 588
Chapter 17: Working with the Ribbon 589
Ribbon Basics 589
Customizing the Ribbon 591
Adding a button to the Ribbon 591
Adding a button to the Quick Access Toolbar 594
Understanding the limitations of Ribbon customization 595
Creating a Custom Ribbon 596
Adding a button to an existing tab 596
Adding a check box to an existing tab 602
Ribbon controls demo 605
A dynamicMenu control example 613
More on Ribbon customization 616
Using VBA with the Ribbon 617
Accessing a Ribbon control 617
Working with the Ribbon 619
Activating a tab 621
Creating an Old-Style Toolbar 621
Limitations of old-style toolbars in Excel 2007 and later 622
Code to create a toolbar 622
Chapter 18: Working with Shortcut Menus 625
CommandBar Overview 625
CommandBar types 626
Listing shortcut menus 626
Referring to CommandBars 627
Referring to controls in a CommandBar 628
Properties of CommandBar controls 630
Displaying all shortcut menu items 630
Using VBA to Customize Shortcut Menus 632
Shortcut menu and the single-document interface 633
Resetting a shortcut menu634
Disabling a shortcut menu636
Disabling shortcut menu items 636
Adding a new item to the Cell shortcut menu 637
Adding a submenu to a shortcut menu 639
Limiting a shortcut menu to a single workbook642
Shortcut Menus and Events 642
Adding and deleting menus automatically 642
Disabling or hiding shortcut menu items 643
Creating a context-sensitive shortcut menu 643
Chapter 19: Providing Help for Your Applications 647
Help for Your Excel Applications 647
Help Systems That Use Excel Components 649
Using cell comments for help 649
Using a text box for help650
Using a worksheet to display help text 652
Displaying help in a UserForm 653
Displaying Help in a Web Browser 657
Using HTML files 657
Using an MHTML file 658
Using the HTML Help System 659
Using the Help method to display HTML Help 662
Associating a help file with your application 663
Associating a help topic with a VBA function 663
Chapter 20: Leveraging Class Modules 665
What Is a Class Module? 665
Built-in class modules666
Custom class modules 666
Creating a NumLock Class 667
Inserting a class module 668
Adding VBA code to the class module 668
Using the CNumLock class 670
Coding Properties, Methods, and Events 671
Programming properties of objects 671
Programming methods for objects 673
Class module events 673
Exposing a QueryTable Event 674
Creating a Class to Hold Classes 677
Creating the CSalesRep and CSalesReps classes 677
Creating the CInvoice and CInvoices classes 679
Filling the parent classes with objects 680
Calculating the commissons 682
Chapter 21: Understanding Compatibility Issues 685
What Is Compatibility? 685
Types of Compatibility Problems 686
Avoid Using New Features 687
But Will It Work on a Mac? 689
Dealing with 64-Bit Excel 690
Creating an International Application 691
Multilanguage applications 693
VBA language considerations 694
Using local properties 694
Identifying system settings 695
Date and time settings 697
Part V: Appendix
Appendix A: VBA Statements and Function Reference 701
Index 709
| Erscheint lt. Verlag | 18.3.2016 |
|---|---|
| Reihe/Serie | Mr. Spreadsheet's Bookshelf |
| Verlagsort | New York |
| Sprache | englisch |
| Maße | 188 x 233 mm |
| Gewicht | 988 g |
| Einbandart | kartoniert |
| Themenwelt | Informatik ► Office Programme ► Excel |
| Schlagworte | Excel VBA • VBA • VBA Programmierung mit Excel |
| ISBN-10 | 1-119-06772-3 / 1119067723 |
| ISBN-13 | 978-1-119-06772-6 / 9781119067726 |
| Zustand | Neuware |
| Informationen gemäß Produktsicherheitsverordnung (GPSR) | |
| Haben Sie eine Frage zum Produkt? |
aus dem Bereich
