Excel 2016 Power Programming With VBA by Alexander, Michael -- Read -- Imperial Library of Trantor

Index

Cover Title Page Copyright About the Authors About the Technical Editor Credits Introduction

Topics Covered What You Need to Know What You Need to Have Conventions in This Book What the Icons Mean How This Book Is Organized About This Book’s Website About the Power Utility Pak Offer

Part I: Introduction to Excel VBA

Chapter 1: Essentials of Spreadsheet Application Development

What Is a Spreadsheet Application? Steps for Application Development Determining User Needs Planning an Application That Meets User Needs Determining the Most Appropriate User Interface Concerning Yourself with the End User Other Development Issues

Chapter 2: Introducing Visual Basic for Applications

Getting a Head Start with the Macro Recorder Working with the Visual Basic Editor VBA Fundamentals Deep Dive: Working with Range Objects Essential Concepts to Remember Don’t Panic — You Are Not Alone

Chapter 3: VBA Programming Fundamentals

VBA Language Elements: An Overview Comments Variables, Data Types, and Constants Assignment Statements Arrays Object Variables User-Defined Data Types Built-In Functions Manipulating Objects and Collections Controlling Code Execution

Chapter 4: Working with VBA Sub Procedures

About Procedures Executing Sub Procedures Passing Arguments to Procedures Error-Handling Techniques A Realistic Example That Uses Sub Procedures

Chapter 5: Creating Function Procedures

Sub Procedures versus Function Procedures Why Create Custom Functions? An Introductory Function Example Function Procedures Function Arguments Function Examples Emulating Excel’s SUM Function Extended Date Functions Debugging Functions Dealing with the Insert Function Dialog Box Using Add-Ins to Store Custom Functions Using the Windows API

Chapter 6: Understanding Excel’s Events

What You Should Know about Events Getting Acquainted with Workbook-Level Events Examining Worksheet Events Monitoring with Application Events Accessing Events Not Associated with an Object

Chapter 7: VBA Programming Examples and Techniques

Learning by Example Working with Ranges Working with Workbooks and Sheets VBA Techniques Some Useful Functions for Use in Your Code Some Useful Worksheet Functions Windows API Calls

Part II: Advanced VBA Techniques

Chapter 8: Working with Pivot Tables

An Introductory Pivot Table Example Creating a More Complex Pivot Table Creating Multiple Pivot Tables Creating a Reverse Pivot Table

Chapter 9: Working with Charts

Getting the Inside Scoop on Charts Creating an Embedded Chart Creating a Chart on a Chart Sheet Modifying Charts Using VBA to Activate a Chart Moving a Chart Using VBA to Deactivate a Chart Determining Whether a Chart Is Activated Deleting from the ChartObjects or Charts Collection Looping through All Charts Sizing and Aligning ChartObjects Creating Lots of Charts Exporting a Chart Changing the Data Used in a Chart Using VBA to Display Arbitrary Data Labels on a Chart Displaying a Chart in a UserForm Understanding Chart Events Discovering VBA Charting Tricks Working with Sparkline Charts

Chapter 10: Interacting with Other Applications

Understanding Microsoft Office Automation Automating Access from Excel Automating Word from Excel Automating PowerPoint from Excel Automating Outlook from Excel Starting Other Applications from Excel

Chapter 11: Working with External Data and Files

Working with External Data Connections Using ADO and VBA to Pull External Data Working with Text Files Text File Manipulation Examples Performing Common File Operations Zipping and Unzipping Files

Part III: Working with UserForms

Chapter 12: Leveraging Custom Dialog Boxes

Before You Create That UserForm . . . Using an Input Box The VBA MsgBox Function The Excel GetOpenFilename Method The Excel GetSaveAsFilename Method Prompting for a Directory Displaying Excel’s Built-In Dialog Boxes Displaying a Data Form

Chapter 13: Introducing UserForms

How Excel Handles Custom Dialog Boxes Inserting a New UserForm Adding Controls to a UserForm Toolbox Controls Adjusting UserForm Controls Adjusting a Control’s Properties Displaying a UserForm Closing a UserForm Creating a UserForm: An Example Understanding UserForm Events Referencing UserForm Controls Customizing the Toolbox Creating UserForm Templates A UserForm Checklist

Chapter 14: UserForm Examples

Creating a UserForm “Menu” Selecting Ranges from a UserForm Creating a Splash Screen Disabling a UserForm's Close Button Changing a UserForm's Size Zooming and Scrolling a Sheet from a UserForm ListBox Techniques Using the MultiPage Control in a UserForm Using an External Control Animating a Label

Chapter 15: Advanced UserForm Techniques

A Modeless Dialog Box Displaying a Progress Indicator Creating Wizards Emulating the MsgBox Function A UserForm with Movable Controls A UserForm with No Title Bar Simulating a Toolbar with a UserForm Emulating a Task Pane with a UserForm A Resizable UserForm Handling Multiple UserForm Controls with One Event Handler Selecting a Color in a UserForm Displaying a Chart in a UserForm Making a UserForm Semitransparent A Puzzle on a UserForm Video Poker on a UserForm

Part IV: Developing Excel Applications

Chapter 16: Creating and Using Add-Ins

What Is an Add-In? Understanding Excel’s Add-In Manager Creating an Add-In An Add-In Example Comparing XLAM and XLSM Files Manipulating Add-Ins with VBA Optimizing the Performance of Add-Ins Special Problems with Add-Ins

Chapter 17: Working with the Ribbon

Ribbon Basics Customizing the Ribbon Creating a Custom Ribbon Using VBA with the Ribbon Creating an Old-Style Toolbar

Chapter 18: Working with Shortcut Menus

CommandBar Overview Using VBA to Customize Shortcut Menus Shortcut Menus and Events

Chapter 19: Providing Help for Your Applications

Help for Your Excel Applications Help Systems That Use Excel Components Displaying Help in a Web Browser Using the HTML Help System

Chapter 20: Leveraging Class Modules

What Is a Class Module? Creating a NumLock Class Coding Properties, Methods, and Events Exposing a QueryTable Event Creating a Class to Hold Classes

Chapter 21: Understanding Compatibility Issues

What Is Compatibility? Types of Compatibility Problems Avoid Using New Features But Will It Work on a Mac? Dealing with 64-Bit Excel Creating an International Application

Part V: Appendix

Appendix A: VBA Statements and Function Reference

Invoking Excel Functions in VBA Instructions

Advert EULA

← Prev
Back
Next →

← Prev
Back
Next →