Excel 2016 Power Programming with VBA E-Book

Michael Alexander

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Serie: Mr. Spreadsheet's Bookshelf
• Sprache: Englisch
• Veröffentlichungsjahr: 1966

Seitenzahl: 920

Beschreibung

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.

Leseprobe

Excel® 2016 Power Programming with VBA

Michael AlexanderDick Kusleika

Excel® 2016 Power Programming with VBA

Published by John Wiley & Sons, Inc. 10475 Crosspoint Boulevard Indianapolis, IN 46256 www.wiley.com

Copyright © 2016 by John Wiley & Sons, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-1-119-06772-6

ISBN: 978-1-119-06756-6 (ebk)

ISBN: 978-1-119-06762-7 (ebk)

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEB SITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.

For general information on our other products and services please contact our Customer Care Department within the United States at [phone], outside the United States at [phone] or fax [phone].

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Control Number: 2016930004

Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

About the Authors

Michael Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Microsoft Excel. He has more than 15 years of experience consulting and developing Microsoft Office solutions. Mike has been named a Microsoft MVP for his ongoing contributions to the Excel community. In his spare time, he runs a free tutorial site, www.datapigtechnologies.com, where he shares Excel and Access tips.

Dick Kusleika has been awarded as a Microsoft MVP for 12 consecutive years and has been working with Microsoft Office for more than 20. Dick develops Access- and Excel-based solutions for his clients and has conducted training seminars on Office products in the United States and Australia. Dick also writes a popular Excel-related blog at www.dailydoseofexcel.com.

About the Technical Editor

Niek Otten lives in the Netherlands. He started in data processing in 1967 in an insurance company. He ran into Visicalc in 1980 and has been addicted to spreadsheets ever since. His first encounter with Excel (version 1!) was in 1985 on a Macintosh. Since 2005, Niek has been self-employed — reviewing books, writing articles, and developing Excel-related software, such as a high-performance actuarial add-in.

Credits

Acquisitions Editor

Stephanie McComb

Project Editor

Kelly Talbot

Technical Editor

Niek Otten

Production Editor

Rebecca Anderson

Copy Editor

Kelly Talbot Editing Services

Manager of Content Development and Assembly

Mary Beth Wakefield

Marketing Director

David Mayhew

Marketing Manager

Carrie Sherrill

Professional Technology & Strategy Director

Barry Pruett

Business Manager

Amy Knies

Executive Editor

Jody Lefevere

Project Coordinator, Cover

Patrick Redmond

Proofreader

Nancy Carrasco

Indexer

Johnna VanHoose Dinse

Cover Designer

Wiley

CONTENTS

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

List of Tables

Chapter 1

Table 1.1

Chapter 2

Table 2.1

Chapter 3

Table 3.1

Table 3.2

Table 3.3

Table 3.4

Chapter 5

Table 5.1

Chapter 6

Table 6.1

Table 6.2

Table 6.3

Table 6.4

Chapter 9

Table 9.1

Chapter 11

Table 11.1

Table 11.2

Chapter 12

Table 12.1

Table 12.2

Table 12.3

Chapter 13

Table 13.1

Chapter 21

Table 21.1

Table 21.2

Table 21.3

Appendix A

Table A.1

Table A.2

List of Illustrations

Chapter 1

Figure 1.1 A customized shortcut menu.

Figure 1.2 A dialog box created with Excel’s UserForm feature.

Figure 1.3 You can add UserForm controls to worksheets and link them to cells.

Figure 1.4 Using the Ribbon to add controls to a worksheet.

Figure 1.5 Using the Protect Sheet dialog box to specify what users can and can’t do.

Chapter 2

Figure 2.1 The Record Macro dialog box.

Figure 2.2 Your pre-totaled worksheet containing two tables.

Figure 2.3 Your post-totaled worksheet.

Figure 2.4 The Excel Macro dialog box.

Figure 2.5 Recording a macro with relative references.

Figure 2.6 The Trusted Locations menu allows you to add directories that are considered trusted.

Figure 2.7 You can find the form controls in the Developer tab.

Figure 2.8 Assign a macro to the newly-added button.

Figure 2.9 Adding a macro to the Quick Access toolbar.

Figure 2.10 The VBE with significant elements identified.

Figure 2.11 This Project window lists two projects. They are expanded to show their objects.

Figure 2.12 Code modules are visible in the Project window in a folder called Modules.

Figure 2.13 The Editor tab in the Options dialog box.

Figure 2.14 Change the VBE’s looks with the Editor Format tab.

Figure 2.15 The General tab of the Options dialog box.

Figure 2.16 The Docking tab of the Options dialog box.

Figure 2.17 Object Browser is a great reference source.

Chapter 3

Figure 3.1 VBA’s way of telling you that your procedure contains an undeclared variable.

Figure 3.2 VBA displays a list of constants that you can assign to a property.

Figure 3.3 Displaying a list of VBA functions in VBE.

Chapter 4

Figure 4.1 The Macro dialog box.

Figure 4.2 The Macro Options dialog box lets you assign a Ctrl key shortcut and an optional description to a procedure.

Figure 4.3 The References dialog box lets you establish a reference to another workbook.

Figure 4.4 Assigning a macro to a button.

Figure 4.5 Executing a procedure by entering its name in the Immediate window.

Figure 4.6 VBA error messages aren’t always user friendly.

Figure 4.7 You can create a message box to display the error code and description.

Figure 4.8 The

SpecialCells

method generates this error if no cells are found.

Figure 4.9 Using the VBE Immediate window to test a statement.

Figure 4.10 An empty procedure in a module located in the Personal Macro Workbook.

Figure 4.11 Using a temporary procedure to test the

BubbleSort

code.

Figure 4.12 This message box tells the user that the sheets cannot be sorted.

Figure 4.13 This message box appears before the sheets are sorted.

Figure 4.14 Adding a new command to the Ribbon.

Chapter 5

Figure 5.1 Using a custom function in a worksheet formula.

Figure 5.2 Using a custom function in a VBA procedure.

Figure 5.3 Using a custom VBA function for conditional formatting.

Figure 5.4 Calling a Function procedure from the Immediate window.

Figure 5.5 Using a function to display the result of a calculation.

Figure 5.6 Different ways of passing an array or a single value to a worksheet.

Figure 5.7 Comparing SUM with MYSUM.

Figure 5.8 The Extended Date functions used in formulas.

Figure 5.9 Use the Immediate window to display results while a function is running.

Figure 5.10 The Insert Function and Function Arguments dialog boxes for a custom function.

Figure 5.11 Using Windows API functions to determine which keys were pressed.

Chapter 6

Figure 6.1 The components for each VBA project are listed in the Project window.

Figure 6.2 The best way to create an event procedure is to let VBE do it for you.

Figure 6.3 This message box was triggered by a

SheetActivate

event.

Figure 6.4 Clicking No cancels the print operation by changing the

Cancel

argument in the event-handler procedure.

Figure 6.5 When this message appears,

Workbook_BeforeClose

has already done its thing.

Figure 6.6 A message displayed by the

Workbook_BeforeClose

event procedure.

Figure 6.7 This message box describes the problem when the user makes an invalid entry.

Figure 6.8 The

Worksheet_Change

procedure ensures that data validation isn’t deleted.

Figure 6.9 Moving the cell cursor shades the active cell’s row and column.

Figure 6.10 This workbook uses a class module to monitor all Application-level events.

Figure 6.11 This message box was programmed to display at a particular time of day.

Figure 6.12 Pressing Shift+F10 displays this message.

Chapter 7

Figure 7.1 The number of rows in the data range changes every week.

Figure 7.2 This workbook uses a custom shortcut menu to demonstrate how to select variably sized ranges by using VBA.

Figure 7.3 The InputBox function gets a value from the user to be inserted into a cell.

Figure 7.4 Validate a user’s entry with the VBA InputBox function.

Figure 7.5 A macro for inserting data into the next empty row in a worksheet.

Figure 7.6 Use an input box to pause a macro.

Figure 7.7 A VBA procedure analyzes the currently selected range.

Figure 7.8 Using the intersection of the used range and the selected range results in fewer cells to process.

Figure 7.9 The goal is to duplicate rows based on the value in column B.

Figure 7.10 New rows were added, according to the value in column B.

Figure 7.11 Using a function to determine the type of data in a cell.

Figure 7.12 Displaying the time to write to a range and read from a range, using a loop.

Figure 7.13 Using Excel’s InputBox method to prompt for a cell location.

Figure 7.14 All rows and columns are hidden, except for a range (G7:L19).

Figure 7.15 Hyperlinks to each worksheet, created by a macro.

Figure 7.16 A message box displaying the date and time.

Figure 7.17 Using a function to display time differences in a friendly manner.

Figure 7.18 Listing font names in the actual fonts.

Figure 7.19 Comparing the time required to perform sorts of various array sizes.

Figure 7.20 Examples of the SPELLDOLLARS function.

Figure 7.21 Determining the path and name of the application associated with a particular file.

Chapter 8

Figure 8.1 This table is a good candidate for a pivot table.

Figure 8.2 A pivot table created from the data in Figure 8.1.

Figure 8.3 The data in this workbook will be summarized in a pivot table.

Figure 8.4 A pivot table created from the budget data.

Figure 8.5 The Pivot Table Fields task pane.

Figure 8.6 Several pivot tables created by a VBA procedure.

Figure 8.7 The summary table on the left will be converted to the table on the right.

Figure 8.8 This dialog box asks the user for the ranges.

Chapter 9

Figure 9.1 These charts use different formatting.

Figure 9.2 A simple macro applied consistent formatting to the four charts.

Figure 9.3 Each row of data will be used to create a chart.

Figure 9.4 A sampling of the 50 charts created by the macro.

Figure 9.5 This chart always displays the data from the row of the active cell.

Figure 9.6 Data labels from an arbitrary range show the percent change for each week.

Figure 9.7 Data labels created from a range of data are not compatible with versions of Excel before 2013.

Figure 9.8 An XY chart that would benefit by having data labels.

Figure 9.9 This XY chart has data labels, thanks to a VBA procedure.

Figure 9.10 Showing a chart within a userform.

Figure 9.11 Selecting an event in the code module for a Chart object.

Figure 9.12 This chart serves as a clickable image map.

Figure 9.13 After converting a chart to a picture, you can manipulate it by using a variety of formatting options.

Figure 9.14 A text box displays information about the data point under the mouse pointer.

Figure 9.15 Range B7:C9 contains data point information that’s displayed in the text box on the chart.

Figure 9.16 An example of a scrollable chart.

Figure 9.17 Sparkline examples.

Figure 9.18 The result of running the

SparklineReport

procedure.

Chapter 10

Figure 10.1 Add a reference to the object library for the application you are automating.

Chapter 11

Figure 11.1 Choose the source database that contains the data you want imported.

Figure 11.2 Select the Access object you want to import.

Figure 11.3 Choosing how and where to view your Access data.

Figure 11.4 Data imported from Access.

Figure 11.5 As long as a connection to your database is available, you can update your table with the latest data.

Figure 11.6 Choose the Properties button for the connection you want to change.

Figure 11.7 On the Definition tab, select the SQL command type and enter your SQL Statement.

Figure 11.8 Designate a cell that will trap the criteria selection.

Figure 11.9 Take note of the connection name (Facility Services in this example).

Figure 11.10 You now have an easy-to-use mechanism for pulling external data for a specified market.

Figure 11.11 Select the latest version of the Microsoft ActiveX Data Objects Library.

Chapter 12

Figure 12.1 The VBA InputBox function at work.

Figure 12.2 Using the VBA InputBox function with a long prompt.

Figure 12.3 Using the InputBox method to specify a range.

Figure 12.4 Excel’s InputBox method performs validation automatically.

Figure 12.5 Another example of validating an entry in Excel’s InputBox.

Figure 12.6 The buttons argument of the MsgBox function determines which buttons appear.

Figure 12.7 Displaying lengthy text in a message box.

Figure 12.8 This message box displays text with tabs and line breaks.

Figure 12.9 The GetOpenFilename method displays a dialog box used to specify a file.

Figure 12.10 This dialog box was displayed with a VBA statement.

Figure 12.11 Using the Customize Ribbon panel to identify a command name.

Figure 12.12 Some users prefer to use Excel’s built-in data form for data-entry tasks.

Chapter 13

Figure 13.1 The Properties window for an empty UserForm.

Figure 13.2 Use the Toolbox to add controls to a UserForm.

Figure 13.3 This UserForm displays all of the controls.

Figure 13.4 Use the Format ➜ Align command to change the alignment of controls.

Figure 13.5 The OptionButton controls, aligned and evenly spaced.

Figure 13.6 The Properties window for an

OptionButton

control.

Figure 13.7 Use the Tab Order dialog box to specify the tab order of the controls in a Frame control.

Figure 13.8 This dialog box asks the user to enter a name and a sex.

Figure 13.9 The

CommandButton1_Click

procedure is executed when the button on the worksheet is clicked.

Figure 13.10 The CommandButton’s Click event procedure displays the UserForm.

Figure 13.11 The event list for a CheckBox control.

Figure 13.12 This SpinButton is paired with a TextBox.

Figure 13.13 The Toolbox, with a new page of controls.

Figure 13.14 The Additional Controls dialog box lets you add other ActiveX controls.

Chapter 14

Figure 14.1 This dialog box uses CommandButtons as a menu.

Figure 14.2 This dialog box uses a ListBox as a menu.

Figure 14.3 The RefEdit control allows the user to select a range.

Figure 14.4 This splash screen is displayed briefly when the workbook is opened.

Figure 14.5 A dialog box before and after displaying options.

Figure 14.6 Here, ScrollBar controls allow zooming and scrolling of the worksheet.

Figure 14.7 Setting the RowSource property at design time.

Figure 14.8 A

Collection

object is used to fill a ListBox with the unique items from column B.

Figure 14.9 This message box displays a list of items selected in a ListBox.

Figure 14.10 The contents of this ListBox depend on the OptionButton selected.

Figure 14.11 Building a list from another list.

Figure 14.12 The buttons allow the user to move items up or down in the ListBox.

Figure 14.13 This ListBox displays a three-column list with column headers.

Figure 14.14 A two-column ListBox filled with data stored in an array.

Figure 14.15 This ListBox makes selecting rows in a worksheet easy.

Figure 14.16 This dialog box lets the user activate a sheet.

Figure 14.17 Use a TextBox to filter a ListBox.

Figure 14.18 MultiPage groups your controls on pages, making them accessible from a tab.

Figure 14.19 The Windows Media Player control in a UserForm.

Figure 14.20 The Windows Media Player control.

Figure 14.21 Generating a random number.

Figure 14.22 A random number has been chosen.

Chapter 15

Figure 15.1 This modeless dialog box remains visible while the user continues working.

Figure 15.2 This modeless UserForm displays various information about the active cell.

Figure 15.3 A UserForm displays the progress of a macro.

Figure 15.4 This UserForm will serve as a progress indicator.

Figure 15.5 The user specifies the number of rows and columns for the random numbers.

Figure 15.6 Page2 of the MultiPage control will display the progress indicator.

Figure 15.7 The progress indicator will be hidden by reducing the height of the UserForm.

Figure 15.8 The progress indicator in action.

Figure 15.9 The steps are listed in a ListBox control.

Figure 15.10 Files are added to the list to show progress.

Figure 15.11 This four-step wizard uses a MultiPage control.

Figure 15.12 Clicking the Cancel button displays a confirmation message box.

Figure 15.13 The result of the MsgBox emulation function.

Figure 15.14 The UserForm for the MyMsgBox function.

Figure 15.15 You can drag and rearrange the three Image controls by using the mouse.

Figure 15.16 This UserForm lacks a title bar.

Figure 15.17 Another UserForm without a title bar.

Figure 15.18 A UserForm set up to function as a toolbar.

Figure 15.19 The UserForm that simulates a toolbar.

Figure 15.20 A UserForm designed to look like a task pane.

Figure 15.21 This UserForm is resizable.

Figure 15.22 The UserForm after it was increased.

Figure 15.23 VBA code converts Label control movements into new Width and Height properties for the UserForm.

Figure 15.24 Multiple CommandButtons with a single event-handler procedure.

Figure 15.25 The

ButtonGroup_Click

procedure describes the button that was clicked.

Figure 15.26 This dialog box lets the user select a color by specifying the red, green, and blue components.

Figure 15.27 The user’s

ScrollBar

values are stored in the Windows Registry and retrieved the next time the

GetAColor

function is used.

Figure 15.28 With a bit of trickery, a UserForm can display “live” charts.

Figure 15.29 A semitransparent UserForm.

Figure 15.30 Creating a light-box effect in Excel.

Figure 15.31 A sliding tile puzzle in a UserForm.

Figure 15.32 A feature-packed video poker game.

Chapter 16

Figure 16.1 Excel warns you if an add-in uses a nonstandard file extension.

Figure 16.2 These settings affect whether add-ins can be used.

Figure 16.3 The Add-Ins dialog box.

Figure 16.4 The Export Charts workbook will make a useful add-in.

Figure 16.5 The Add-Ins dialog box with the new add-in selected.

Figure 16.6 Making an add-in not an add-in.

Figure 16.7 One way to remove a member of the AddIns collection.

Figure 16.8 A table that lists information about all members of the

AddIns

collection.

Figure 16.9 When attempting to open the add-in incorrectly, the user sees this message.

Chapter 17

Figure 17.1 The Page Layout tab contains many different control types.

Figure 17.2 The Customize Ribbon tab allows you to add macros to the Ribbon.

Figure 17.3 The Rename dialog lets you choose an icon for your Ribbon button.

Figure 17.4 The custom Ribbon button executes the

HelloWorld

macro.

Figure 17.5 You can add a macro to the Quick Access Toolbar.

Figure 17.6 The new QAT button executes your macro.

Figure 17.7 Excel can’t find the macro associated with the Ribbon button.

Figure 17.8 XML to create two buttons in a custom group.

Figure 17.9 The editor generates VBA code to use in your workbook.

Figure 17.10 Modify the callback procedures in the VBE.

Figure 17.11 Two new buttons add to the Data tab.

Figure 17.12 This check box control is always in synch with the page break display of the active sheet.

Figure 17.13 A new Ribbon tab with five groups of controls.

Figure 17.14 A Ribbon group with two labels.

Figure 17.15 An editBox control in a custom Ribbon group.

Figure 17.16 Three controls in a custom Ribbon group.

Figure 17.17 This group contains built-in controls.

Figure 17.18 This Ribbon group contains two galleries.

Figure 17.19 A gallery that displays month names, plus a button.

Figure 17.20 A gallery of images.

Figure 17.21 The

dynamicMenu

control lets you create a menu that varies depending on the context.

Figure 17.22 Using the Customize Ribbon tab of the Excel Options dialog box to determine the name of a control.

Figure 17.23 An old-style toolbar, located in the Custom Toolbars group of the Add-Ins tab.

Chapter 18

Figure 18.1 A simple macro generates a list of all shortcut menus.

Figure 18.2 Displaying the

Caption

property for controls.

Figure 18.3 Listing the items in all shortcut menus.

Figure 18.4 The Cell shortcut menu with a custom menu item.

Figure 18.5 This shortcut menu has a submenu with three submenu items.

Figure 18.6 A new shortcut menu appears only when the user right-clicks a cell in the shaded area of the worksheet.

Chapter 19

Figure 19.1 Using cell comments to display help.

Figure 19.2 Using a shape object with text to display help for the user.

Figure 19.3 An easy method is to put user help in a separate worksheet.

Figure 19.4 Clicking one of the arrows on the SpinButton changes the text displayed in the Labels.

Figure 19.5 Inserting a

Label

control inside a

Frame

control adds scrolling to the Label.

Figure 19.6 Using a drop-down list control to select a help topic.

Figure 19.7 Displaying help in a web browser.

Figure 19.8 Displaying an MHTML file in a web browser.

Figure 19.9 An example of HTML Help.

Figure 19.10 Using HTML Help Workshop to create a help file.

Figure 19.11 Specify a context ID for a custom function.

Chapter 20

Figure 20.1 An empty class module named

CNumLock

.

Figure 20.2 A message box shows the change in status of the Num Lock key.

Figure 20.3 A web query for financial information.

Figure 20.4 The code pane lists available events.

Figure 20.5 After a web query is refreshed, the last update time is recorded.

Figure 20.6 Excel tables hold the information for the objects.

Figure 20.7 The commission calculation is output to the Immediate Window.

Chapter 21

Figure 21.1 Compatibility Checker.

Figure 21.2 A summary report from Microsoft Office Code Compatibility Inspector.

Figure 21.3 The Wizard Demo in English, Spanish, and German.

Guide

Cover

Table of Contents

1

Pages

xxvii

xxviii

xxix

xxx

xxxi

xxxii

1

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

289

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

559

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

685

686

687

688

689

690

691

692

693

694

695

696

697

699

701

702

703

704

705

706

707

708

PART IIntroduction to Excel VBA

Chapter 1

Essentials of Spreadsheet Application Development

Chapter 2

Introducing Visual Basic for Applications

Chapter 3

VBA Programming Fundamentals

Chapter 4

Working with VBA Sub Procedures

Chapter 5

Creating Function Procedures

Chapter 6

Understanding Excel’s Events

Chapter 7

VBA Programming Examples and Techniques

1Essentials of Spreadsheet Application Development

In This Chapter

Discovering the basic steps involved in spreadsheet application development

Determining end users’ needs

Planning applications to meet users’ needs

Developing and testing your applications

Documenting your development efforts and writing user documentation

What Is a Spreadsheet Application?

For the purposes of this book, a spreadsheet application is a spreadsheet file (or group of related files) that is designed so that someone other than the developer can perform useful work without extensive training. According to this definition, most of the spreadsheet files that you’ve developed probably don’t qualify as spreadsheet applications. You may have dozens or hundreds of spreadsheet files on your hard drive, but it’s a safe bet that most of them aren’t designed for others to use.

A good spreadsheet application:

Enables the end user to perform a task that he or she probably would not be able to do otherwise.

Provides the appropriate solution to the problem. (A spreadsheet environment isn’t always the optimal approach.)

Accomplishes what it is supposed to do. This prerequisite may be obvious, but it’s not at all uncommon for applications to fail this test.

Produces accurate results and is free of bugs.

Uses appropriate and efficient methods and algorithms to accomplish its job.

Traps errors before the user is forced to deal with them.

Does not allow the user to delete or modify important components accidentally (or intentionally).

Has a clear and consistent user interface so that the user always knows how to proceed.

Has well-documented formulas, macros, and user interface elements that allow for subsequent changes, if necessary.

Is designed so that it can be modified in simple ways without making major changes. A basic fact is that a user’s needs change over time.

Has an easily accessible help system that provides useful information on at least the major procedures.

Is designed to be portable and to run on any system that has the proper software (in this case, a copy of the appropriate version of Excel).

It should come as no surprise that it is possible to create spreadsheet applications for many different usage levels, ranging from a simple fill-in-the-blank template to an extremely complex application that uses a custom interface and may not even look like a spreadsheet.

Steps for Application Development

There is no simple, surefire recipe for developing an effective spreadsheet application. Everyone has his or her own style for creating such applications. In addition, every project is different and, therefore, requires its own approach. Finally, the demands and technical expertise of the people you work with (or for) also play a role in how the development process proceeds.

Spreadsheet developers typically perform the following activities:

Determine the needs of the user(s)

Plan an application that meets these needs

Determine the most appropriate user interface

Create the spreadsheet, formulas, macros, and user interface

Test and debug the application

Attempt to make the application bulletproof

Make the application aesthetically appealing and intuitive

Document the development effort

Develop user documentation and Help systems

Distribute the application to the user

Update the application when necessary

Not all these steps are required for each application, and the order in which these activities are performed varies from project to project. We describe each of these activities in the pages that follow. For most of these items, we cover the technical details in subsequent chapters.

Determining User Needs

When you undertake a new Excel project, one of your first steps is to identify exactly what the end users require. Failure to thoroughly assess the end users’ needs early on often results in additional work later when you have to adjust the application so that it does what it was supposed to do in the first place.

In some cases, you’ll be intimately familiar with the end users — you may even be an end user yourself. In other cases (for example, if you’re a consultant developing a project for a new client), you may know little or nothing about the users or their situations.

How do you determine the needs of the user? If you’ve been asked to develop a spreadsheet application, it’s a good idea to meet with the end users and ask specific questions. Better yet, get everything in writing, create flow diagrams, pay attention to minor details, and do anything else to ensure that the product you deliver is the product that is needed.

Here are some guidelines that may help make this phase easier:

Don’t assume that you know what the user needs. Second-guessing at this stage almost always causes problems later.

If possible, talk directly to the end users of the application, not just their supervisor or manager.

Learn what, if anything, is currently being done to meet the users’ needs. You might be able to save some work by simply adapting an existing application. At the very least, looking at current solutions will familiarize you with the operation.

Identify the resources available at the users’ site. For example, try to determine whether you must work around any hardware or software limitations.

If possible, determine the specific hardware systems that will be used. If your application will be used on slow systems, you need to take that into account.

Identify which versions of Excel are in use. Although Microsoft does everything in its power to urge users to upgrade to the latest version of the software, the majority of Excel users don’t.

Understand the skill levels of the end users. This information will help you design the application appropriately.

Determine how long the application will be used and whether any changes are anticipated during the lifetime of the project. Knowing this information may influence the amount of effort that you put into the project and help you plan for changes.

And finally, don’t be surprised if the project specifications change before you complete the application. This occurrence is common, and you’re in a better position if you expect changes rather than being surprised by them. Just make sure that your contract (if you have one) addresses the issue of changing specifications.

Planning an Application That Meets User Needs

After you determine the end users’ needs, it’s tempting to jump right in and start fiddling around in Excel. Take it from someone who suffers from this problem: Try to restrain yourself. Builders don’t construct a house without a set of blueprints, and you shouldn’t build a spreadsheet application without some type of plan. The formality of your plan depends on the scope of the project and your general style of working, but you should spend at least some time thinking about what you’re going to do and coming up with a plan of action.

Before rolling up your sleeves and settling down at your keyboard, you’ll benefit by taking some time to consider the various ways you can approach the problem. This planning period is where a thorough knowledge of Excel pays off. Avoiding blind alleys rather than stumbling into them is always a good idea.

If you ask a dozen Excel experts to design an application based on precise specifications, chances are you’ll get a dozen different implementations of the project that meet those specifications. Of those solutions, some will be better than the others because Excel often provides several options to accomplish a task. If you know Excel inside and out, you’ll have a good idea of the potential methods at your disposal, and you can choose the one most appropriate for the project at hand. Often, a bit of creative thinking yields an unusual approach that’s vastly superior to other methods.

So at the beginning stage of this planning period, consider some general options, such as these:

File structure:

Think about whether you want to use one workbook with multiple sheets, several single-sheet workbooks, or a template file.

Data structure:

You should always consider how your data will be structured and also determine whether you will be using external database files or storing everything in worksheets.

Add-in or workbook file:

In some cases, an add-in may be the best choice for your final product. Or perhaps you might use an add-in with a standard workbook.

Version of Excel: