Details

Excel 2016 Power Programming with VBA


Excel 2016 Power Programming with VBA


Mr. Spreadsheet's Bookshelf 1. Aufl.

von: Michael Alexander, Richard Kusleika

36,99 €

Verlag: Wiley
Format: EPUB
Veröffentl.: 29.01.2016
ISBN/EAN: 9781119067627
Sprache: englisch
Anzahl Seiten: 768

DRM-geschütztes eBook, Sie benötigen z.B. Adobe Digital Editions und eine Adobe ID zum Lesen.

Beschreibungen

<b>Maximize your Excel experience with VBA</b> <p><i>Excel 2016 Power Programming with VBA</i> 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. <p>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. <ul> <li>Explore fully updated content that offers comprehensive coverage through over 900 pages of tips, tricks, and techniques</li> <li>Leverage templates and worksheets that put your new knowledge in action, and reinforce the skills introduced in the text</li> <li>Access online resources, including the Power Utility Pak, that supplement the content</li> <li>Improve your capabilities regarding Excel programming with VBA, unlocking more of your potential in the office</li> </ul> <p><i>Excel 2016 Power Programming with VBA</i> is a fundamental resource for intermediate to advanced users who want to polish their skills regarding spreadsheet applications using VBA.
<p>Introduction xxvii</p> <p><b>Part I: Introduction to Excel VBA</b></p> <p><b>Chapter 1: Essentials of Spreadsheet Application Development 3</b></p> <p>What Is a Spreadsheet Application? 3</p> <p>Steps for Application Development 4</p> <p>Determining User Needs 5</p> <p>Planning an Application That Meets User Needs 6</p> <p>Determining the Most Appropriate User Interface 8</p> <p>Concerning Yourself with the End User 12</p> <p>Other Development Issues 17</p> <p><b>Chapter 2: Introducing Visual Basic for Applications 19</b></p> <p>Getting a Head Start with the Macro Recorder 19</p> <p>Working with the Visual Basic Editor 32</p> <p>VBA Fundamentals 42</p> <p>Deep Dive: Working with Range Objects 47</p> <p>Essential Concepts to Remember 53</p> <p>Don’t Panic—You Are Not Alone55</p> <p><b>Chapter 3: VBA Programming Fundamentals 61</b></p> <p>VBA Language Elements: An Overview 61</p> <p>Comments 63</p> <p>Variables, Data Types, and Constants 64</p> <p>Assignment Statements 75</p> <p>Arrays 77</p> <p>Object Variables79</p> <p>User-Defined Data Types 80</p> <p>Built-In Functions 81</p> <p>Manipulating Objects and Collections 84</p> <p>Controlling Code Execution 87</p> <p><b>Chapter 4: Working with VBA Sub Procedures 105</b></p> <p>About Procedures 105</p> <p>Executing Sub Procedures 108</p> <p>Passing Arguments to Procedures 119</p> <p>Error-Handling Techniques 123</p> <p>A Realistic Example That Uses Sub Procedures 127</p> <p><b>Chapter 5: Creating Function Procedures 143</b></p> <p>Sub Procedures versus Function Procedures 143</p> <p>Why Create Custom Functions? 144</p> <p>An Introductory Function Example 144</p> <p>Function Procedures 148</p> <p>Function Arguments 153</p> <p>Function Examples 153</p> <p>Emulating Excel’s SUM Function 168</p> <p>Extended Date Functions 171</p> <p>Debugging Functions 173</p> <p>Dealing with the Insert Function Dialog Box 174</p> <p>Using Add-Ins to Store Custom Functions 178</p> <p>Using the Windows API 179</p> <p><b>Chapter 6: Understanding Excel’s Events 183</b></p> <p>What You Should Know about Events 183</p> <p>Getting Acquainted with Workbook-Level Events 190</p> <p>Examining Worksheet Events 197</p> <p>Monitoring with Application Events 206</p> <p>Accessing Events Not Associated with an Object 210</p> <p><b>Chapter 7: VBA Programming Examples and Techniques 217</b></p> <p>Learning by Example 217</p> <p>Working with Ranges 218</p> <p>Working with Workbooks and Sheets 246</p> <p>VBA Techniques 251</p> <p>Some Useful Functions for Use in Your Code 259</p> <p>Some Useful Worksheet Functions 264</p> <p>Windows API Calls 280</p> <p><b>Part II: Advanced VBA Techniques</b></p> <p><b>Chapter 8: Working with Pivot Tables 291</b></p> <p>An Introductory Pivot Table Example 291</p> <p>Creating a More Complex Pivot Table 297</p> <p>Creating Multiple Pivot Tables 301</p> <p>Creating a Reverse Pivot Table 304</p> <p><b>Chapter 9: Working with Charts 307</b></p> <p>Getting the Inside Scoop on Charts 307</p> <p>Creating an Embedded Chart 310</p> <p>Creating a Chart on a Chart Sheet 311</p> <p>Modifying Charts 312</p> <p>Using VBA to Activate a Chart 313</p> <p>Moving a Chart 314</p> <p>Using VBA to Deactivate a Chart 315</p> <p>Determining Whether a Chart Is Activated 316</p> <p>Deleting from the ChartObjects or Charts Collection 316</p> <p>Looping through All Charts 318</p> <p>Sizing and Aligning ChartObjects 320</p> <p>Creating Lots of Charts 321</p> <p>Exporting a Chart 324</p> <p>Changing the Data Used in a Chart 326</p> <p>Using VBA to Display Arbitrary Data Labels on a Chart 331</p> <p>Displaying a Chart in a UserForm 335</p> <p>Understanding Chart Events 337</p> <p>Discovering VBA Charting Tricks 344</p> <p>Working with Sparkline Charts 351</p> <p><b>Chapter 10: Interacting with Other Applications 355</b></p> <p>Understanding Microsoft Office Automation 355</p> <p>Automating Access from Excel 359</p> <p>Automating Word from Excel 361</p> <p>Automating PowerPoint from Excel 364</p> <p>Automating Outlook from Excel 369</p> <p>Starting Other Applications from Excel 373</p> <p><b>Chapter 11: Working with External Data and Files 379</b></p> <p>Working with External Data Connections 379</p> <p>Using ADO and VBA to Pull External Data 387</p> <p>Working with Text Files 394</p> <p>Text File Manipulation Examples 398</p> <p>Performing Common File Operations 402</p> <p>Zipping and Unzipping Files 410</p> <p><b>Part III: Working with UserForms</b></p> <p><b>Chapter 12: Leveraging Custom Dialog Boxes 415</b></p> <p>Before You Create That UserForm 415</p> <p>Using an Input Box415</p> <p>The VBA MsgBox Function 421</p> <p>The Excel GetOpenFilename Method 426</p> <p>The Excel GetSaveAsFilename Method 429</p> <p>Prompting for a Directory  430</p> <p>Displaying Excel’s Built-In Dialog Boxes 430</p> <p>Displaying a Data Form 433</p> <p><b>Chapter 13: Introducing UserForms 435</b></p> <p>How Excel Handles Custom Dialog Boxes 435</p> <p>Inserting a New UserForm 436</p> <p>Adding Controls to a UserForm 437</p> <p>Toolbox Controls  437</p> <p>Adjusting UserForm Controls 442</p> <p>Adjusting a Control’s Properties 443</p> <p>Displaying a UserForm 449</p> <p>Closing a UserForm 451</p> <p>Creating a UserForm: An Example 453</p> <p>Understanding UserForm Events 459</p> <p>Referencing UserForm Controls 465</p> <p>Customizing the Toolbox 466</p> <p>Creating UserForm Templates 469</p> <p>A UserForm Checklist 469</p> <p><b>Chapter 14: UserForm Examples 471</b></p> <p>Creating a UserForm “Menu” 471</p> <p>Selecting Ranges from a UserForm  474</p> <p>Creating a Splash Screen 476</p> <p>Disabling a UserForm's Close Button 478</p> <p>Changing a UserForm's Size 479</p> <p>Zooming and Scrolling a Sheet from a UserForm 480</p> <p>ListBox Techniques 482</p> <p>Using the MultiPage Control in a UserForm 503</p> <p>Using an External Control 504</p> <p>Animating a Label 506</p> <p><b>Chapter 15: Advanced UserForm Techniques 511</b></p> <p>A Modeless Dialog Box 512</p> <p>Displaying a Progress Indicator 516</p> <p>Creating Wizards 527</p> <p>Emulating the MsgBox Function 533</p> <p>A UserForm with Movable Controls 537</p> <p>A UserForm with No Title Bar 538</p> <p>Simulating a Toolbar with a UserForm 540</p> <p>Emulating a Task Pane with a UserForm 542</p> <p>A Resizable UserForm 543</p> <p>Handling Multiple UserForm Controls with One Event Handler 547</p> <p>Selecting a Color in a UserForm 550</p> <p>Displaying a Chart in a UserForm 552</p> <p>Making a UserForm Semitransparent 554</p> <p>A Puzzle on a UserForm 556</p> <p>Video Poker on a UserForm 557</p> <p><b>Part IV: Developing Excel Applications</b></p> <p><b>Chapter 16: Creating and Using Add-Ins 561</b></p> <p>What Is an Add-In? 561</p> <p>Understanding Excel’s Add-In Manager 564</p> <p>Creating an Add-in 566</p> <p>An Add-In Example 567</p> <p>Comparing XLAM and XLSM Files 572</p> <p>Manipulating Add-Ins with VBA 578</p> <p>Optimizing the Performance of Add-Ins 584</p> <p>Special Problems with Add-Ins 585</p> <p><b>Chapter 17: Working with the Ribbon 589</b></p> <p>Ribbon Basics 589</p> <p>Customizing the Ribbon 591</p> <p>Creating a Custom Ribbon 596</p> <p>Using VBA with the Ribbon 617</p> <p>Creating an Old-Style Toolbar 621</p> <p><b>Chapter 18: Working with Shortcut Menus 625</b></p> <p>CommandBar Overview 625</p> <p>Using VBA to Customize Shortcut Menus 632</p> <p>Shortcut Menus and Events 642</p> <p><b>Chapter 19: Providing Help for Your Applications 647</b></p> <p>Help for Your Excel Applications 647</p> <p>Help Systems That Use Excel Components 649</p> <p>Displaying Help in a Web Browser 657</p> <p>Using the HTML Help System 659</p> <p><b>Chapter 20: Leveraging Class Modules 665</b></p> <p>What Is a Class Module? 665</p> <p>Creating a NumLock Class 667</p> <p>Coding Properties, Methods, and Events 671</p> <p>Exposing a QueryTable Event 674</p> <p>Creating a Class to Hold Classes 677</p> <p><b>Chapter 21: Understanding Compatibility Issues 685</b></p> <p>What Is Compatibility? 685</p> <p>Types of Compatibility Problems 686</p> <p>Avoid Using New Features 687</p> <p>But Will It Work on a Mac? 689</p> <p>Dealing with 64-Bit Excel 690</p> <p>Creating an International Application 691</p> <p><b>Part V: Appendix</b></p> <p>Appendix A: VBA Statements and Function Reference 701</p> <p>Index 709</p>
<p><b>Michael Alexander</b> 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. <p><b>Dick Kusleika</b> has been helping users get the most out of Microsoft Office products for more than fifteen years through online forums, blogging, books, and conferences. <p><b>John Walkenbach</b> is arguably the foremost authority on Excel. He has written more than 30 books and maintains the popular Spreadsheet Page at http://spreadsheetpage.com
<p><b><i>Become a power user with VBA</i></b> <p>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. <p>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. <p><b><i>Let Mr. Spreadsheet show you how to:</i></b> <ul> <li><b>Go beyond recording simple macros</b></li> <li><b>Dynamically manipulate workbooks and worksheets</b></li> <li><b>Automate pivot table and chart features</b></li> <li><b>Manage external data and wrangle external files</b></li> <li><b>Send emails directly from Excel</b></li> <li><b>Create your own Excel Ribbon interface</b></li> <li><b>Develop and distribute your own Excel Add-ins</b></li> </ul> <p><b>COMPANION WEBSITE</b><br/> Visit the companion website at www.wiley.com/go/excel2016powerprogramming to find files used as examples in the book

Diese Produkte könnten Sie auch interessieren:

AutoCAD 2013 For Dummies
AutoCAD 2013 For Dummies
von: Bill Fane, David Byrnes
PDF ebook
23,99 €
IBM Workplace Services Express For Dummies
IBM Workplace Services Express For Dummies
von: Stephen R. Londergan, Michael Loria
PDF ebook
16,99 €
Building Flash Web Sites For Dummies
Building Flash Web Sites For Dummies
von: Doug Sahlin
PDF ebook
18,99 €