- •About the Author
- •About the Technical Editor
- •Credits
- •Is This Book for You?
- •Software Versions
- •Conventions This Book Uses
- •What the Icons Mean
- •How This Book Is Organized
- •How to Use This Book
- •What’s on the Companion CD
- •What Is Excel Good For?
- •What’s New in Excel 2010?
- •Moving around a Worksheet
- •Introducing the Ribbon
- •Using Shortcut Menus
- •Customizing Your Quick Access Toolbar
- •Working with Dialog Boxes
- •Using the Task Pane
- •Creating Your First Excel Worksheet
- •Entering Text and Values into Your Worksheets
- •Entering Dates and Times into Your Worksheets
- •Modifying Cell Contents
- •Applying Number Formatting
- •Controlling the Worksheet View
- •Working with Rows and Columns
- •Understanding Cells and Ranges
- •Copying or Moving Ranges
- •Using Names to Work with Ranges
- •Adding Comments to Cells
- •What Is a Table?
- •Creating a Table
- •Changing the Look of a Table
- •Working with Tables
- •Getting to Know the Formatting Tools
- •Changing Text Alignment
- •Using Colors and Shading
- •Adding Borders and Lines
- •Adding a Background Image to a Worksheet
- •Using Named Styles for Easier Formatting
- •Understanding Document Themes
- •Creating a New Workbook
- •Opening an Existing Workbook
- •Saving a Workbook
- •Using AutoRecover
- •Specifying a Password
- •Organizing Your Files
- •Other Workbook Info Options
- •Closing Workbooks
- •Safeguarding Your Work
- •Excel File Compatibility
- •Exploring Excel Templates
- •Understanding Custom Excel Templates
- •Printing with One Click
- •Changing Your Page View
- •Adjusting Common Page Setup Settings
- •Adding a Header or Footer to Your Reports
- •Copying Page Setup Settings across Sheets
- •Preventing Certain Cells from Being Printed
- •Preventing Objects from Being Printed
- •Creating Custom Views of Your Worksheet
- •Understanding Formula Basics
- •Entering Formulas into Your Worksheets
- •Editing Formulas
- •Using Cell References in Formulas
- •Using Formulas in Tables
- •Correcting Common Formula Errors
- •Using Advanced Naming Techniques
- •Tips for Working with Formulas
- •A Few Words about Text
- •Text Functions
- •Advanced Text Formulas
- •Date-Related Worksheet Functions
- •Time-Related Functions
- •Basic Counting Formulas
- •Advanced Counting Formulas
- •Summing Formulas
- •Conditional Sums Using a Single Criterion
- •Conditional Sums Using Multiple Criteria
- •Introducing Lookup Formulas
- •Functions Relevant to Lookups
- •Basic Lookup Formulas
- •Specialized Lookup Formulas
- •The Time Value of Money
- •Loan Calculations
- •Investment Calculations
- •Depreciation Calculations
- •Understanding Array Formulas
- •Understanding the Dimensions of an Array
- •Naming Array Constants
- •Working with Array Formulas
- •Using Multicell Array Formulas
- •Using Single-Cell Array Formulas
- •Working with Multicell Array Formulas
- •What Is a Chart?
- •Understanding How Excel Handles Charts
- •Creating a Chart
- •Working with Charts
- •Understanding Chart Types
- •Learning More
- •Selecting Chart Elements
- •User Interface Choices for Modifying Chart Elements
- •Modifying the Chart Area
- •Modifying the Plot Area
- •Working with Chart Titles
- •Working with a Legend
- •Working with Gridlines
- •Modifying the Axes
- •Working with Data Series
- •Creating Chart Templates
- •Learning Some Chart-Making Tricks
- •About Conditional Formatting
- •Specifying Conditional Formatting
- •Conditional Formats That Use Graphics
- •Creating Formula-Based Rules
- •Working with Conditional Formats
- •Sparkline Types
- •Creating Sparklines
- •Customizing Sparklines
- •Specifying a Date Axis
- •Auto-Updating Sparklines
- •Displaying a Sparkline for a Dynamic Range
- •Using Shapes
- •Using SmartArt
- •Using WordArt
- •Working with Other Graphic Types
- •Using the Equation Editor
- •Customizing the Ribbon
- •About Number Formatting
- •Creating a Custom Number Format
- •Custom Number Format Examples
- •About Data Validation
- •Specifying Validation Criteria
- •Types of Validation Criteria You Can Apply
- •Creating a Drop-Down List
- •Using Formulas for Data Validation Rules
- •Understanding Cell References
- •Data Validation Formula Examples
- •Introducing Worksheet Outlines
- •Creating an Outline
- •Working with Outlines
- •Linking Workbooks
- •Creating External Reference Formulas
- •Working with External Reference Formulas
- •Consolidating Worksheets
- •Understanding the Different Web Formats
- •Opening an HTML File
- •Working with Hyperlinks
- •Using Web Queries
- •Other Internet-Related Features
- •Copying and Pasting
- •Copying from Excel to Word
- •Embedding Objects in a Worksheet
- •Using Excel on a Network
- •Understanding File Reservations
- •Sharing Workbooks
- •Tracking Workbook Changes
- •Types of Protection
- •Protecting a Worksheet
- •Protecting a Workbook
- •VB Project Protection
- •Related Topics
- •Using Excel Auditing Tools
- •Searching and Replacing
- •Spell Checking Your Worksheets
- •Using AutoCorrect
- •Understanding External Database Files
- •Importing Access Tables
- •Retrieving Data with Query: An Example
- •Working with Data Returned by Query
- •Using Query without the Wizard
- •Learning More about Query
- •About Pivot Tables
- •Creating a Pivot Table
- •More Pivot Table Examples
- •Learning More
- •Working with Non-Numeric Data
- •Grouping Pivot Table Items
- •Creating a Frequency Distribution
- •Filtering Pivot Tables with Slicers
- •Referencing Cells within a Pivot Table
- •Creating Pivot Charts
- •Another Pivot Table Example
- •Producing a Report with a Pivot Table
- •A What-If Example
- •Types of What-If Analyses
- •Manual What-If Analysis
- •Creating Data Tables
- •Using Scenario Manager
- •What-If Analysis, in Reverse
- •Single-Cell Goal Seeking
- •Introducing Solver
- •Solver Examples
- •Installing the Analysis ToolPak Add-in
- •Using the Analysis Tools
- •Introducing the Analysis ToolPak Tools
- •Introducing VBA Macros
- •Displaying the Developer Tab
- •About Macro Security
- •Saving Workbooks That Contain Macros
- •Two Types of VBA Macros
- •Creating VBA Macros
- •Learning More
- •Overview of VBA Functions
- •An Introductory Example
- •About Function Procedures
- •Executing Function Procedures
- •Function Procedure Arguments
- •Debugging Custom Functions
- •Inserting Custom Functions
- •Learning More
- •Why Create UserForms?
- •UserForm Alternatives
- •Creating UserForms: An Overview
- •A UserForm Example
- •Another UserForm Example
- •More on Creating UserForms
- •Learning More
- •Why Use Controls on a Worksheet?
- •Using Controls
- •Reviewing the Available ActiveX Controls
- •Understanding Events
- •Entering Event-Handler VBA Code
- •Using Workbook-Level Events
- •Working with Worksheet Events
- •Using Non-Object Events
- •Working with Ranges
- •Working with Workbooks
- •Working with Charts
- •VBA Speed Tips
- •What Is an Add-In?
- •Working with Add-Ins
- •Why Create Add-Ins?
- •Creating Add-Ins
- •An Add-In Example
- •System Requirements
- •Using the CD
- •What’s on the CD
- •Troubleshooting
- •The Excel Help System
- •Microsoft Technical Support
- •Internet Newsgroups
- •Internet Web sites
- •End-User License Agreement
CHAPTER
Creating Custom
Excel Add-Ins
For developers, one of the most useful features in Excel is the capability to create add-ins. This chapter discusses this concept and provides a practical example of creating an add-in.
What Is an Add-In?
Generally speaking, an add-in is something that’s added to software to give it additional functionality. Excel includes several add-ins, including the Analysis ToolPak and Solver. Ideally, the new features blend in well with the original interface so that they appear to be part of the program.
Excel’s approach to add-ins is quite powerful because any knowledgeable Excel user can create add-ins from workbooks. An Excel add-in is basically a different form of a workbook file. Any Excel workbook can be converted into an add-in, but not every workbook is a good candidate for an add-in.
What distinguishes an add-in form a normal workbook? Add-ins, by default, have an .xlam extension In addition, add-ins are always hidden, so you can’t display worksheets or chart sheets that are contained in an add-in. But, you can access its VBA procedures and display dialog boxes that are contained on UserForms.
IN THIS CHAPTER
Understanding add-ins
Converting a workbook to an add-in
903
Part VI: Programming Excel with VBA
The following are some typical uses for Excel add-ins:
•Store one or more custom worksheet functions. When the add-in is loaded, you can use the functions like any built-in worksheet function.
•Store Excel utilities. VBA is ideal for creating general-purpose utilities that extend the power of Excel. The Power Utility Pak that I created is an example.
•Store proprietary macros. If you don’t want end users to see (or modify) your macros, store the macros in an add-in and protect the VBA project with a password. A user can use the macros, but they can’t view or change them unless the user knows the password. An additional benefit is that the add-in doesn’t display a workbook window, which can be distracting.
As previously noted, Excel ships with several useful add-ins (see the sidebar “Add-Ins Included with Excel”), and you can acquire other add-ins from third-party vendors or the Internet. In addition, Excel includes the tools that enable you to create your own add-ins. I explain this process later in the chapter (see “Creating Add-Ins”).
Working with Add-Ins
The best way to work with add-ins is to use the Excel Add-In Manager. To display the Add-In Manager
1.Choose File Options.
2.In the Excel Options dialog box, select the Add-Ins category.
3.At the bottom of the dialog box, select Excel Add-Ins from the Manage list and then click Go.
Excel displays its Add-Ins dialog box, shown in Figure 45.1. The list box contains all the add-ins that Excel knows about. The add-ins that are checked are open. You can open and close add-ins from this dialog box by selecting or deselecting the check boxes.
Tip
Pressing Alt+TI is a much faster way to display the Add-Ins dialog box. n
Caution
You can also open most add-in files by choosing File Open. After an add-in is opened, however, you can’t choose File Close to close it. The only way to remove the add-in is to exit and restart Excel or to write a macro to close the add-in. Therefore, you’re usually better off opening the add-ins by using the Add-Ins dialog box. n
904
Chapter 45: Creating Custom Excel Add-Ins
FIGURE 45.1
The Add-Ins dialog box.
The user interface for some add-ins (including those included with Excel) may be integrated into the Ribbon. For example, when you open the Analysis ToolPak add-in, you access these tools by choosing Data Analysis Data Analysis.
Add-Ins Included with Excel
The following table lists the add-ins included with Excel 2010. Some add-ins may not have been installed. If you try to use one of these add-ins and it’s not installed, you receive a prompt asking whether you want to install it.
|
Add-In Name |
What It Does |
Where to Find It |
|
|
|
|
|
|
|
Analysis ToolPak |
Statistical and engineering |
Choose Data Analysis Data Analysis. |
|
|
|
tools. See Chapter 38. |
|
|
|
|
|
|
|
|
Analysis ToolPak — |
VBA functions for the |
The functions in this add-in are used by VBA pro- |
|
|
VBA |
Analysis ToolPak. |
grammers, and they are not accessible from Excel. |
|
|
|
|
|
|
|
Euro Currency |
Tools for converting and for- |
Choose Formulas Solutions Euro |
|
|
Tools |
matting the euro currency. |
Conversion and Formulas Solutions Euro |
|
|
|
|
Formatting. |
|
|
|
|
|
|
|
Solver Add-In |
A tool that helps you to use |
Choose Data Analysis Solver. |
|
|
|
a variety of numeric meth- |
|
|
|
|
ods for equation solving |
|
|
|
|
and optimization. |
|
|
|
|
|
|
|
905
Part VI: Programming Excel with VBA
Note
If you open an add-in created in a version prior to Excel 2007, any user interface modifications made by the add-in will not appear as they were intended to appear. Rather, you must access the user interface items (menus and toolbars) by choosing Add-Ins Menu Commands or Add-Ins Custom Toolbars. n
Tip
You can also download additional Excel add-ins from http://office.microsoft.com.
Why Create Add-Ins?
Most Excel users have no need to create add-ins. However, if you develop spreadsheets for others — or if you simply want to get the most out of Excel — you may be interested in pursuing this topic further.
Here are some reasons why you may want to convert your Excel workbook application to an add-in:
•Avoid confusion. If an end user loads your application as an add-in, the file isn’t visible in the Excel window — and, therefore, is less likely to confuse novice users or get in the way. Unlike a hidden workbook, an add-in can’t be unhidden.
•Simplify access to worksheet functions. Custom worksheet functions stored in an addin don’t require the workbook name qualifier. For example, if you have a custom function named MOVAVG stored in a workbook named Newfuncs.xlsm, you have to use a syntax such as the following to use this function in a different workbook:
=NEWFUNC.XLSM!MOVAVG(A1:A50)
However, if this function is stored in an add-in file that’s open, the syntax is much simpler because you don’t need to include the file reference:
=MOVAVG(A1:A50)
•Provide easier access. After you identify the location of your add-in, it appears in the Add-Ins dialog box and can display a friendly name and a description of what it does.
•Permit better loading control. You can automatically open add-ins when Excel starts, regardless of the directory in which they’re stored.
•Omit prompts when unloading. When an add-in is closed, the user never sees the Save Change In prompt because changes to add-ins aren’t saved unless you specifically do so from the VB Editor window.
906