Name: Excel 2016 Power Programming with VBA By Michael Alexander
SKU: 9781119067726
Availability: OutOfStock

Excel 2016 Power Programming with VBA Summary

Excel 2016 Power Programming with VBA by Michael Alexander

Maximize your Excel experience with VBA 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 Excel 2016 Power Programming with VBA is a fundamental resource for intermediate to advanced users who want to polish their skills regarding spreadsheet applications using VBA.

About Michael Alexander

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. 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. 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

Introduction 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

Additional information

Sku

NGR9781119067726

ISBN 13

9781119067726

ISBN 10

1119067723

Title

Excel 2016 Power Programming with VBA by Michael Alexander

Author

Michael Alexander

Series

Mr. Spreadsheet's Bookshelf

Condition

New

Binding type

Paperback

Publisher

John Wiley & Sons Inc

Year published

2016-03-18

Number of pages

768

Prizes

N/A

Cover note

Book picture is for illustrative purposes only, actual binding, cover or edition may vary.

Note

This is a new book - be the first to read this copy. With untouched pages and a perfect binding, your brand new copy is ready to be opened for the first time

Excel 2016 Power Programming with VBA Michael Alexander

Excel 2016 Power Programming with VBA by Michael Alexander

Summary

Excel 2016 Power Programming with VBA Summary

Excel 2016 Power Programming with VBA by Michael Alexander

About Michael Alexander

Table of Contents

Additional information

Customer Reviews - Excel 2016 Power Programming with VBA