Excel 2016 Power Programming with VBA
38%
off

Excel 2016 Power Programming with VBA

4.33 (12 ratings by Goodreads)
By (author)  , By (author)  , By (author) 

Free delivery worldwide

Available. Dispatched from the UK in 3 business days
When will my order arrive?

Description

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

Product details

  • Paperback | 768 pages
  • 187 x 233 x 36mm | 1,000g
  • New York, United States
  • English
  • 1119067723
  • 9781119067726
  • 43,822

Back cover copy

Become a power user with VBA

Take your Excel skills to the next level by harnessing the power of the VBA language. This comprehensive resource will help you automate data processes and develop full featured Excel programs. Fully updated for the newest release of Excel, this one-of-a-kind resource presents everything you need to develop both large and small Excel applications.

This book was designed with you in mind, giving you straight facts without hype or unnecessary explanations. This book offers tips, tricks and practical examples complete with all the code and sample templates you need to start your journey from humble data analyst to Excel programming pro.

Let Mr. Spreadsheet show you how to: Go beyond recording simple macros Dynamically manipulate workbooks and worksheets Automate pivot table and chart features Manage external data and wrangle external files Send emails directly from Excel Create your own Excel Ribbon interface Develop and distribute your own Excel Add-ins

COMPANION WEBSITE Visit the companion website at www.wiley.com/go/excel2016powerprogramming to find files used as examples in the book
show more

Table of contents

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
show more

About Richard Kusleika

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
show more

Rating details

12 ratings
4.33 out of 5 stars
5 58% (7)
4 17% (2)
3 25% (3)
2 0% (0)
1 0% (0)
Book ratings by Goodreads
Goodreads is the world's largest site for readers with over 50 million reviews. We're featuring millions of their reader ratings on our book pages to help you find your new favourite book. Close X