PUP 2000 Features

for Microsoft Excel 97

Overview:

PUP 2000 is a collection of Excel add-ins, and consists of several parts:

50 general purpose Excel utilities
40 new worksheet functions
Enhanced shortcut menus
PUP bookmarks
Workbook task bar
Four strategy games

PUP 2000 is optimized to reduce the amount of memory and system resources used. Each utility is stored in a separate file, so it uses memory only if you need it. And, it's easy to remove any or all utilities to free up memory, if necessary. In addition, free updates are available so you can be assured that you're always using the most recent version of the utilities.

When the PUP 2000 add-in is installed, you can access the utilities from the new PUP 2000 menu (displayed next to Excel's Help menu). As an option, you can create a customizable toolbar that contains buttons which execute the utilities you use most frequently. Complete online help is available.

NOTE: PUP 2000 was developed using Excel VBA exclusively. As such, it does not make any modifications to your system. Therefore, you don't need to worry about this product causing problems with other software that you may use.

The remainder of this document describes the utilities and functions available in PUP 2000. For a comprehensive list of all features, view the comprehensive features list.

The Utilities:

PUP 2000 contains 50 general purpose utilities that extend and enhance Excel. All utilities include extensive error-checking, and provide the ability to Undo the effects (where appropriate). The utilities are grouped into the following categories:

Range Tools
Formula tools
Formatting Tools
Worksheet Tools
Workbook Tools
Chart Tools
Printing Tools
Date & Time Tools
Object Tools
Auditing Tools

Range Tools

Text Tools:
Lets you manipulate the text in cells in the following ways: (1) Add text to the beginning, end, or at a specified character position; Remove characters from the beginning, end, or at a specified character position; (3) Change the case of selected text; (4) Remove excess spaces from text; (5) displays word count and character count statistics. Now uses a tabbed dialog box.
<NEW> Cell Comment Tools:
Change the appearance of all comments, search and replace text within comments, remove the user name from comments, or create a listing of all comments.
Select Cells by Value:
Formerly known as Select by Value. Lets you select cells in a specified range (or an entire worksheet) that meet certain criteria -- for example, select all cells with a value between 12 and 24. Or, select all cells that contain text that ends in "ing."
<NEW> Select Rows by Value:
Select rows based on the contents of a column. This utility, for example, provides a quick way to select (and then delete) all empty rows.
<NEW> Select Cells by Format:
Lets you select cells based on their formatting. For example, you can select all cells that contain a yellow background. Once selected, you can apply other formatting to the cells.
<NEW> Cell Lock / Sheet Protect Utility:
Makes it easy to lock and unlock specific types of cells in a worksheet (for example, lock all cells that contain formulas).
Range Randomize Utilities:
Lets you: (1) Generate a series of consecutive integers, arranged randomly in a range; (2) Randomly rearrange the values in a range.
Export Range to File:
Exports a range to a separate file (either an Excel workbook, or a CSV text file).
<IMPROVED>Export Range as Graphic:
Converts a range (and the graphic objects displayed in the range) to a graphic file (GIF, JPEG, TIF, or PNG format).
<NEW>Transform Vertical Range to Columns:
Makes it easy to convert a single column of entries into rows with multiple columns.

Formula Tools

<NEW> Convert Formulas to Values:
Lets you convert selected formulas to their current values.
<NEW> Exact Formula Copy:
Makes an exact copy of formulas. Range references are not changed.
Convert Relative and Absolute References:
Change cell references from absolute to relative (and vice versa).
<IMPROVED>Unapply Names:
Convert a name reference in a formula to its actual reference (either absolute or relative). For example, the utility converts a formula such as =SUM(Region1) to =SUM(A2:A98).
<IMPROVED>Unit Conversion:
Easily convert measurements from one unit to another. Very flexible.
Error Condition Wizard:
Makes it easy to add an error condition to a formula or group of formulas. This is useful when you want to avoid displaying error values such as $DIV/0! The wizard walks you through the steps to create the formula you need.

Formatting Tools

<NEW> Superscript/Subscript Formatting:
Makes it easy to apply superscript or subscript formatting to characters in a cell.
<NEW> Insert Symbol:
Makes it easy to insert a special character from any installed font.
3-D Cell Shading:
Applies an attractive 3-D border effect to a cell or range.
Alternate Row Shading:
Applies user-specified shading to alternate rows in a range.
Copy Column Widths:
Makes it easy to duplicate the precise column widths from one location to another.

Worksheet Tools

<IMPROVED> Toggle Settings:
Quickly toggle any of the following settings: gridlines, horizontal scrollbar, vertical scrollbar, row and column headers, page breaks, formula view, sheet tabs, status bar, formula bar, full screen display, comment visibility, and object visibility. It's all done interactively, so you can see the effect before you commit to it.
Set Scroll Area:
Makes it very easy to set the scrollable range in a worksheet to prevent users from scrolling outside of the appropriate area and getting lost.

<IMPROVED> Interactive Zooming:
Adjust the worksheet zoom level interactively. A new feature lets you set the zoom factor for all sheets.
Pop-up Calculator:
A handy pop-up calculator (operable from the keyboard or with a mouse). Paste the calculated results into a cell.
<NEW> Reset Last Cell:
Forces Excel to reset the "last cell" -- the cell that is activated when you press Ctrl+End. Excel has always had a problem keeping track of this.

Workbook Tools

Insert File at Cursor:
Insert a complete worksheet, range, or text file into the current worksheet, at the cursor position.
Interactive Hide/Unhide:
Makes it easy to change the hidden status of all open workbooks, or the sheets contained in those workbooks.
<NEW> Sort Sheets:
A fast and flexible way to rearrange the order of the sheets in a workbook.
<IMPROVED> Save With Backup: Makes a backup copy of the active workbook at a specified location.

Chart Tools

<IMPROVED>Export Charts:
Quickly converts Excel charts to stand-alone GIF, JPEG, TIF, or PNG files.
Chart Data Labeler:
Lets you apply labels (contained in a range) to a chart data series -- a feature that is surprisingly missing from Excel.

Printing Tools

Batch Printing:
Print any number of workbooks unattended. The workbooks can be in a specified directory or across multiple directories.
Print Multiple Selection Wizard:
Lets you print non-contiguous ranges of cells without page breaks between the separate ranges.
<NEW> Add Path to Header or Footer:
Creates a VBA procedure that prints the workbook's full path in the page header or footer -- something that's normally not possible to do. You can also control the font and size.

Date & Time Tools

<IMPROVED> Perpetual Calendar:
(1) Displays a handy calendar for any month and year; (2) Inserts a graphic image of a calendar month; (3) Creates a new workbook with up to 12 sheets, each displaying a calendar month -- perfect for schedules.
<IMPROVED> Insert a Date:
Makes it very easy to insert a date (in any format desired) into a cell by picking from a calendar display.
Reminder Alarm:
Displays a reminder (with sound) at a specified time of day, or after a specified period of time has elapsed.
<IMPROVED> Time Tracker:
Track the amount of time spent working on up to six different projects. A new feature maintains the cumulative time across sessions. A new option lets you display the current task in Excel's title bar.

Object Tools

Object Properties:
Makes it easy to adjust various properties of graphic objects and shapes. New "nudge" buttons let you fine-tune an object's size and position.
Object Align, Size Space:
Makes it easy to align shapes, change their size, or adjust their spacing. Although some of these tools are available in Excel 97/2000, this utility offers some distinct advantages.

Auditing Tools

<IMPROVED>Workbook Summary Report:
Creates a useful report that summarizes a workbook.
<NEW> Workbook Link Finder:
Lets you track down workbook links.
<IMPROVED> Worksheet Map:
Creates a handy map that visually identifies cells of various types.
<NEW> Compare Sheets or Ranges:
Lets you identify the differences between two worksheets or ranges.
<NEW> Date Report:
Creates a useful report that summarizes cells that contain dates. This can help you identify potential Y2K problems.
<NEW> Name Lister:
Lets you works with names in ways that are otherwise not possible.
<IMPROVED> Formula Report:
Creates a useful report that lists all formulas in a worksheet or workbook.
<NEW> VBA Project Summary Report:
Creates a summary report of the VBA procedures in a workbook.

Custom Worksheet Functions:

PUP 2000 contains 40 new worksheet functions that you can use in your formulas. The functions are stored in five separate files -- which means less memory used when you only need one or two of the functions.

Text Functions

CONTAINS: Returns TRUE if text1 is contained in text2
DOLLARTEXT: Converts a number into spelled-out text, such as One Hundred Forty-Five and 12/100 Dollars. Thanks to Steven Mark, who developed this function and agreed to let me use it.
INSERTSTRING: Inserts a string in a specified location in another string
ISLIKE: Returns TRUE if text is like pattern (using * and ? Wildcards in pattern)
PARSENAME: Returns either the first, middle, or last name from a full name
REMOVESPACES: Returns a string with no spaces
SCRAMBLE: Returns a string with the characters in random order

Date and Time Functions

DAYSINMONTH: Returns the number of days in the month for a serial number date
MONTHWEEK: Returns the calendar week of the month for a serial number date
TIMEXX: Returns a string that represents a time, accurate to a hundredth of a second (hh:mm:ss:xx). This lets you manipulate times such as 01:04:12:46 (one hour, 4 minutes, 12 seconds, and 46 one-hundredths of a second. An optional argument makes this function suitable for SMPTE time codes (used by film and music production engineers).
TIMEXX_ADD: Returns the sum of two TIMEXX time strings (or SMPTE time codes)
TIMEXX_SUBTRACT: Returns the difference between two TIMEXX time strings (or SMPTE time codes)
TIMEXX_SUM: Returns the sum of any number of TIMEXX time strings (or SMPTE time codes)
WHICHDAY: Returns a date that corresponds to the nth weekday in the month (e.g., the first Friday in June)

Statistical & Math Functions

COUNTAVISIBLE: Counts visible cells that are not empty. Ideal for use with AutoFiltering and Outlining
COUNTBETWEEN: Counts cells in a range that have values between num1 and num2
MAXALLSHEETS: Returns the maximum value of a specified cell in all worksheets
MINALLSHEETS: Returns the minimum value of a specified cell in all worksheets
SELECTONE: Returns a single value selected at random from a list
STATICRAND: Returns a random number between 0 and 1 that does not change when the worksheet is recalculated
SUMALLSHEETS: Returns the sum of a specified cell in all worksheets
SUMVISIBLE: Sums only visible cells. Ideal for use with AutoFiltering and Outlining
TOPAVERAGE: Returns the average of the top n values in a list

Information Functions

EXCELDIR: Returns the directory in which Excel is installed
FILEEXISTS: Returns TRUE if the specified filename exists
FILENAME: Returns the name of the workbook file, or #NA if the workbook has not been saved
GETREGISTRY: Returns a value from the Windows registry
SCREENHEIGHT: Returns the vertical video resolution, in pixels
SCREENWIDTH: Returns the horizontal video resolution, in pixels
SHEETCOUNT: Returns the number of sheets in the workbook
SHEETNAME: Returns the name of the worksheet
USER: Returns the name of the current user
WINDOWSDIR: Returns the directory in which Windows is installed
WINDOWSVERSION: Returns the version of Windows currently running

Lookup & Reference Functions

CREDITCARD: Returns the card type of a credit card number, or returns 'Invalid' if the number is not valid.
HINTERPOLATE: Works like HLOOKUP, but performs a linear interpolation if an exact value is not found.
LASTINCOLUMN: Returns the value in the last non-empty cell in a column
LASTINROW: Returns the value in the last non-empty cell in a row
UNIQUEITEMS: Returns the number of unique items in a list, or returns an array of the unique items
VINTERPOLATE: Works like VLOOKUP, but performs a linear interpolation if an exact value is not found.

Enhanced Shortcut Menus

As an option, you can choose to display enhanced shortcut menus.

Worksheet Cell, Range, Column, or Row:
Adds the following menu items: Print selection, Remove All Formatting, Toggle Word Wrap, Format Number, <NEW> Center Across Selection, <NEW> Change Case, <NEW> Synchronize Sheets
Desktop:
Adds the following menu items: Minimize All Windows, Tile Windows, Activate a Window, Interactive Hide/Unhide
Graphic Object or Shape:
Adds the following menu items: Object Properties, Align, Resize, and Space
Sheet Tab:
Adds the following menu items: Hide Sheet, Activate a Sheet, <IMPROVED> Sort Sheets, <NEW> Delete Empty Sheets
Workbook Title Bar:
Adds the following menu items: Toggle Vertical Scroll Bar, Toggle Horizontal Scroll Bar, Toggle Grid Lines, Toggle Row and Column Headers, Toggle Sheet Tabs, Hide 'Windows.

PUP Bookmarks

<IMPROVED> A handy and easily accessible toolbar that lets you create bookmarks so you can quickly return to a particular workbook, worksheet, or range. If the workbook is not open, it opened for you. The bookmarks you create are saved between sessions. An excellent alternative to Excel's "most recently used file list." .

Workbook Task Bar

<NEW> Displays a toolbar that lets you quickly activate any open workbook. You might find this preferable to Excel 2000's 'Windows in Taskbar' option.

Games:

Need a break from crunching numbers? PUP 2000 includes four games:

Video Poker:
Choose from Joker's Wild or Jacks or Better. Lightning fast, and guaranteed to reduce your office productivity!
<IMPROVED> Dice Game:
Similar to Triple Yahtzee.
Bomb Hunt:
Works just like Microsoft's Minesweeper.
<NEW> Hang Man:
The classic word-guessing game.