Cover image for Excel 2016 Power Programming with VBA

Excel 2016 Power Programming with VBA

Author Richard Kusleika , Michael Alexander
Release Date: 2016/02/01
ISBN: 9781119067726
Topic:
0%
37
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

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

Table of Contents

  1. Introduction
    1. Topics Covered
    2. What You Need to Know
    3. What You Need to Have
    4. Conventions in This Book
    5. What the Icons Mean
    6. How This Book Is Organized
    7. About This Book’s Website
    8. About the Power Utility Pak Offer
  2. Part I: Introduction to Excel VBA
    1. Chapter 1: Essentials of Spreadsheet Application Development
      1. What Is a Spreadsheet Application?
      2. Steps for Application Development
      3. Determining User Needs
      4. Planning an Application That Meets User Needs
      5. Determining the Most Appropriate User Interface
      6. Concerning Yourself with the End User
      7. Other Development Issues
    2. Chapter 2: Introducing Visual Basic for Applications
      1. Getting a Head Start with the Macro Recorder
      2. Working with the Visual Basic Editor
      3. VBA Fundamentals
      4. Deep Dive: Working with Range Objects
      5. Essential Concepts to Remember
      6. Don’t Panic — You Are Not Alone
    3. Chapter 3: VBA Programming Fundamentals
      1. VBA Language Elements: An Overview
      2. Comments
      3. Variables, Data Types, and Constants
      4. Assignment Statements
      5. Arrays
      6. Object Variables
      7. User-Defined Data Types
      8. Built-In Functions
      9. Manipulating Objects and Collections
      10. Controlling Code Execution
    4. Chapter 4: Working with VBA Sub Procedures
      1. About Procedures
      2. Executing Sub Procedures
      3. Passing Arguments to Procedures
      4. Error-Handling Techniques
      5. A Realistic Example That Uses Sub Procedures
    5. Chapter 5: Creating Function Procedures
      1. Sub Procedures versus Function Procedures
      2. Why Create Custom Functions?
      3. An Introductory Function Example
      4. Function Procedures
      5. Function Arguments
      6. Function Examples
      7. Emulating Excel’s SUM Function
      8. Extended Date Functions
      9. Debugging Functions
      10. Dealing with the Insert Function Dialog Box
      11. Using Add-Ins to Store Custom Functions
      12. Using the Windows API
    6. Chapter 6: Understanding Excel’s Events
      1. What You Should Know about Events
      2. Getting Acquainted with Workbook-Level Events
      3. Examining Worksheet Events
      4. Monitoring with Application Events
      5. Accessing Events Not Associated with an Object
    7. Chapter 7: VBA Programming Examples and Techniques
      1. Learning by Example
      2. Working with Ranges
      3. Working with Workbooks and Sheets
      4. VBA Techniques
      5. Some Useful Functions for Use in Your Code
      6. Some Useful Worksheet Functions
      7. Windows API Calls
  3. Part II: Advanced VBA Techniques
    1. Chapter 8: Working with Pivot Tables
      1. An Introductory Pivot Table Example
      2. Creating a More Complex Pivot Table
      3. Creating Multiple Pivot Tables
      4. Creating a Reverse Pivot Table
    2. Chapter 9: Working with Charts
      1. Getting the Inside Scoop on Charts
      2. Creating an Embedded Chart
      3. Creating a Chart on a Chart Sheet
      4. Modifying Charts
      5. Using VBA to Activate a Chart
      6. Moving a Chart
      7. Using VBA to Deactivate a Chart
      8. Determining Whether a Chart Is Activated
      9. Deleting from the ChartObjects or Charts Collection
      10. Looping through All Charts
      11. Sizing and Aligning ChartObjects
      12. Creating Lots of Charts
      13. Exporting a Chart
      14. Changing the Data Used in a Chart
      15. Using VBA to Display Arbitrary Data Labels on a Chart
      16. Displaying a Chart in a UserForm
      17. Understanding Chart Events
      18. Discovering VBA Charting Tricks
      19. Working with Sparkline Charts
    3. Chapter 10: Interacting with Other Applications
      1. Understanding Microsoft Office Automation
      2. Automating Access from Excel
      3. Automating Word from Excel
      4. Automating PowerPoint from Excel
      5. Automating Outlook from Excel
      6. Starting Other Applications from Excel
    4. Chapter 11: Working with External Data and Files
      1. Working with External Data Connections
      2. Using ADO and VBA to Pull External Data
      3. Working with Text Files
      4. Text File Manipulation Examples
      5. Performing Common File Operations
      6. Zipping and Unzipping Files
  4. Part III: Working with UserForms
    1. Chapter 12: Leveraging Custom Dialog Boxes
      1. Before You Create That UserForm . . .
      2. Using an Input Box
      3. The VBA MsgBox Function
      4. The Excel GetOpenFilename Method
      5. The Excel GetSaveAsFilename Method
      6. Prompting for a Directory
      7. Displaying Excel’s Built-In Dialog Boxes
      8. Displaying a Data Form
    2. Chapter 13: Introducing UserForms
      1. How Excel Handles Custom Dialog Boxes
      2. Inserting a New UserForm
      3. Adding Controls to a UserForm
      4. Toolbox Controls
      5. Adjusting UserForm Controls
      6. Adjusting a Control’s Properties
      7. Displaying a UserForm
      8. Closing a UserForm
      9. Creating a UserForm: An Example
      10. Understanding UserForm Events
      11. Referencing UserForm Controls
      12. Customizing the Toolbox
      13. Creating UserForm Templates
      14. A UserForm Checklist
    3. Chapter 14: UserForm Examples
      1. Creating a UserForm “Menu”
      2. Selecting Ranges from a UserForm
      3. Creating a Splash Screen
      4. Disabling a UserForm's Close Button
      5. Changing a UserForm's Size
      6. Zooming and Scrolling a Sheet from a UserForm
      7. ListBox Techniques
      8. Using the MultiPage Control in a UserForm
      9. Using an External Control
      10. Animating a Label
    4. Chapter 15: Advanced UserForm Techniques
      1. A Modeless Dialog Box
      2. Displaying a Progress Indicator
      3. Creating Wizards
      4. Emulating the MsgBox Function
      5. A UserForm with Movable Controls
      6. A UserForm with No Title Bar
      7. Simulating a Toolbar with a UserForm
      8. Emulating a Task Pane with a UserForm
      9. A Resizable UserForm
      10. Handling Multiple UserForm Controls with One Event Handler
      11. Selecting a Color in a UserForm
      12. Displaying a Chart in a UserForm
      13. Making a UserForm Semitransparent
      14. A Puzzle on a UserForm
      15. Video Poker on a UserForm
  5. Part IV: Developing Excel Applications
    1. Chapter 16: Creating and Using Add-Ins
      1. What Is an Add-In?
      2. Understanding Excel’s Add-In Manager
      3. Creating an Add-In
      4. An Add-In Example
      5. Comparing XLAM and XLSM Files
      6. Manipulating Add-Ins with VBA
      7. Optimizing the Performance of Add-Ins
      8. Special Problems with Add-Ins
    2. Chapter 17: Working with the Ribbon
      1. Ribbon Basics
      2. Customizing the Ribbon
      3. Creating a Custom Ribbon
      4. Using VBA with the Ribbon
      5. Creating an Old-Style Toolbar
    3. Chapter 18: Working with Shortcut Menus
      1. CommandBar Overview
      2. Using VBA to Customize Shortcut Menus
      3. Shortcut Menus and Events
    4. Chapter 19: Providing Help for Your Applications
      1. Help for Your Excel Applications
      2. Help Systems That Use Excel Components
      3. Displaying Help in a Web Browser
      4. Using the HTML Help System
    5. Chapter 20: Leveraging Class Modules
      1. What Is a Class Module?
      2. Creating a NumLock Class
      3. Coding Properties, Methods, and Events
      4. Exposing a QueryTable Event
      5. Creating a Class to Hold Classes
    6. Chapter 21: Understanding Compatibility Issues
      1. What Is Compatibility?
      2. Types of Compatibility Problems
      3. Avoid Using New Features
      4. But Will It Work on a Mac?
      5. Dealing with 64-Bit Excel
      6. Creating an International Application
  6. Part V: Appendix
    1. Appendix A: VBA Statements and Function Reference
      1. Invoking Excel Functions in VBA Instructions
  7. Advert
  8. EULA
18.218.59.168