Excel 2019 Power Programming with VBA
37%
off

Excel 2019 Power Programming with VBA

4 (1 rating by Goodreads)
By (author)  , By (author) 

Free delivery worldwide

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

Description

Maximize your Excel experience with VBA Excel 2019 Power Programming with VBA is fully updated to cover all the latest tools and tricks of Excel 2019. 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.

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
Improve your capabilities regarding Excel programming with VBA, unlocking more of your potential in the office

Excel 2019 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 | 784 pages
  • 191 x 233 x 38mm | 1,052g
  • New York, United States
  • English
  • 1. Auflage
  • 1119514924
  • 9781119514923
  • 65,219

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.

THIS BOOK SHOWS 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
show more

Table of contents

Introduction xxxiii


Part I: Introduction to Excel VBA 1


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 7


Concerning Yourself with the End User 12


Other Development Issues 17


Chapter 2: Introducing Visual Basic for Applications 19


Getting a Head Start with the Macro Recorder 19


Working with the Visual Basic Editor 32


VBA Fundamentals 43


Deep Dive: Working with Range Objects 48


Essential Concepts to Remember 52


Don't Panic-You Are Not Alone 54


Chapter 3: VBA Programming Fundamentals 61


VBA Language Elements: An Overview 61


Comments 63


Variables, Data Types, and Constants 65


Assignment Statements 76


Arrays 78


Declaring Arrays 78


Object Variables 80


User-Defined Data Types 81


Built-in Functions 82


Manipulating Objects and Collections 85


Controlling Code Execution 88


Chapter 4: Working with VBA Sub Procedures 105


About Procedures 105


Executing Sub Procedures 108


Passing Arguments to Procedures 119


Error-Handling Techniques 123


A Realistic Example That Uses Sub Procedures 127


Utility Availability 140


Evaluating the Project 141


Chapter 5: Creating Function Procedures 143


Sub Procedures vs. Function Procedures 143


Why Create Custom Functions? 144


An Introductory Function Example 144


Function Procedures 148


Function Arguments 153


Function Examples 153


Emulating Excel's SUM Function 167


Extended Date Functions 170


Debugging Functions 172


Dealing with the Insert Function Dialog Box 173


Using Add-Ins to Store Custom Functions 178


Using the Windows API 178


Chapter 6: Understanding Excel's Events 183


What You Should Know About Events 183


Getting Acquainted with Workbook-Level Events 189


Examining Worksheet Events 197


Monitoring with Application Events 206


Chapter 7: VBA Programming Examples and Techniques 217


Learning by Example 217


Working with Ranges 218


Working with Workbooks and Sheets 246


VBA Techniques 251


Some Useful Functions for Use in Your Code 258


Some Useful Worksheet Functions 263


Windows API Calls 278


Part II: Advanced VBA Techniques 287


Chapter 8: Working with Pivot Tables 289


An Introductory Pivot Table Example 289


Creating a More Complex Pivot Table 295


Creating Multiple Pivot Tables 299


Creating a Reverse Pivot Table 302


Chapter 9: Working with Charts 305


Getting the Inside Scoop on Charts 305


Creating an Embedded Chart 308


Creating a Chart on a Chart Sheet 309


Modifying Charts 309


Using VBA to Activate a Chart 310


Moving a Chart 311


Using VBA to Deactivate a Chart 312


Determining Whether a Chart Is Activated 313


Deleting from the ChartObjects or Charts Collection 313


Looping Through All Charts 314


Sizing and Aligning ChartObjects 317


Creating Lots of Charts 318


Exporting a Chart 321


Changing the Data Used in a Chart 322


Using VBA to Display Custom Data Labels on a Chart 328


Displaying a Chart in a UserForm 331


Understanding Chart Events 334


Discovering VBA Charting Tricks 340


Working with Sparkline Charts 347


Chapter 10: Interacting with Other Applications 351


Understanding Microsoft Office Automation 351


Automating Access from Excel 354


Automating Word from Excel 356


Automating PowerPoint from Excel 360


Automating Outlook from Excel 365


Starting Other Applications from Excel 369


Chapter 11: Working with External Data and Files 377


Working with External Data Connections 377


Power Query Basics 377


Using ADO and VBA to Pull External Data 390


Working with Text Files 397


Text File Manipulation Examples 401


Performing Common File Operations 405


Zipping and Unzipping Files 413


Part III: Working with UserForms 417


Chapter 12: Leveraging Custom Dialog Boxes 419


Alternatives to UserForms 419


Using an Input Box 419


Using the VBA MsgBox Function 426


Using the Excel GetOpenFilename Method 431


Using the Excel GetSaveAsFilename Method 434


Prompting for a Folder 435


Displaying Excel's Built-in Dialog Boxes 435


Displaying a Data Form 438


Chapter 13: Introducing UserForms 441


How Excel Handles Custom Dialog Boxes 441


Inserting a New UserForm 442


Adding Controls to a UserForm 443


Toolbox Controls 443


Adjusting UserForm Controls 448


Adjusting a Control's Properties 450


Displaying a UserForm 456


Closing a UserForm 458


Creating a UserForm: An Example 460


Referencing UserForm Controls 473


Customizing the Toolbox 474


Creating UserForm Templates 477


A UserForm Checklist 478


Chapter 14: Looking at UserForm Examples 479


Creating a UserForm "Menu" 479


Selecting Ranges from a UserForm 481


Creating a Splash Screen 483


Disabling a UserForm's Close Button 486


Changing a UserForm's Size 487


Zooming and Scrolling a Sheet from a UserForm 488


Exploring ListBox Techniques 490


Using the MultiPage Control in a UserForm 512


Using an External Control 513


Animating a Label 516


Chapter 15: Implementing Advanced UserForm Techniques 519


A Modeless Dialog Box 519


Displaying a Progress Indicator 523


Creating Wizards 534


Emulating the MsgBox Function 541


A UserForm with Movable Controls 545


A UserForm with No Title Bar 546


Simulating a Toolbar with a UserForm 548


Emulating a Task Pane with a UserForm 550


A Resizable UserForm 551


Handling Multiple UserForm Controls with One Event Handler 556


Selecting a Color in a UserForm 559


Displaying a Chart in a UserForm 561


Making a UserForm Semitransparent 562


A Puzzle on a UserForm 563


Video Poker on a UserForm 565


Part IV: Developing Excel Applications 567


Chapter 16: Creating and Using Add-Ins 569


What Is an Add-In? 569


Understanding Excel's Add-in Manager 572


Creating an Add-In 574


An Add-In Example 575


Comparing XLAM and XLSM Files 581


Manipulating Add-Ins with VBA 587


Optimizing the Performance of Add-Ins 593


Special Problems with Add-Ins 594


Chapter 17: Working with the Ribbon 599


Ribbon Basics 599


Customizing the Ribbon 601


Creating a Custom Ribbon 606


Using VBA with the Ribbon 628


Creating an Old-Style Toolbar 632


Chapter 18: Working with Shortcut Menus 637


CommandBar Overview 637


Referring to Controls in a CommandBar 640


Properties of CommandBar Controls 641


Displaying All Shortcut Menu Items 642


Using VBA to Customize Shortcut Menus 644


Resetting a shortcut menu 646


Shortcut Menus and Events 654


Chapter 19: Providing Help for Your Applications 659


Help for Your Excel Applications 659


Help Systems That Use Excel Components 661


Displaying Help in a Web Browser 670


Using the HTML Help System 672


Chapter 20: Leveraging Class Modules 679


What Is a Class Module? 679


Creating a NumLock Class 681


Coding Properties, Methods, and Events 685


Exposing a QueryTable Event 688


Creating a Class to Hold Classes 692


Chapter 21: Understanding Compatibility Issues 699


What Is Compatibility? 699


Types of Compatibility Problems 699


Avoid Using New Features 701


But Will It Work on a Mac? 703


Dealing with 64-Bit Excel 704


Creating an International Application 705


Multilanguage Applications 707


VBA Language Considerations 708


Using Local Properties 708


Identifying System Settings 709


Date and Time Settings 711


Part V: Appendix 713


Appendix: VBA Statements and Functions Reference 715


Index 725
show more

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 twenty-five years through online forums, blogging, books, and conferences.
show more

Rating details

1 rating
4 out of 5 stars
5 0% (0)
4 100% (1)
3 0% (0)
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