Copyright: Copyright 1995, JWalk and Associates Inc.


Contents

Add-in tools for Microsoft© Excel

For specific information about the Power Utility Pak 2.0, select a help topic below.

Overview:

A description of what's included in the Power Utility Pak, including features new to Version 2.0.

The Utilities:

Complete instructions for using each of the utilities included in the package.

Power Worksheet Functions:

How to use the Power Utility Pak worksheet functions.

Enhanced Shortcut Menus:

About the new commands available on the shortcut menus that appear when you right-click a range or object.

Configuring the Power Utility Pak:

Instructions for configuring and customizing the Power Utility Pak.

Technical Support:

How to get help if you need it.

Technical Information:

For technically-minded users, a description of how the Power Utility Pak works.

Disclaimers:

Legal information. Please read this.


Overview

The Power Utility Pak is a series of add-ins that work in conjunction with Microsoft Excel 5.0 or later. It consists of three parts:

1.	A set of 21 general purpose utilities that add new features to Excel. You can access these using the Power Utility Pak toolbar, or from the Utilities menu.

2.	A collection of 23 new worksheet functions that can be used just like Excel's built-in functions.

3.	Optional enhanced shortcut menus. These are the menus that appear when you right-click an object. When this option is in effect, additional commands will be displayed on these menus.

You can choose to have the Power Utility Pak add-in loaded automatically whenever you start Excel, or you can simply load it when you need it. Although you can use the File Open command to load POWER.XLA, the preferred method is to use the Tools Add-Ins command as described below.

To load the Power Utility Pak automatically, choose the Tools Add-Ins command and select Power Utility Pak 2.0. If Power Utility Pak 2.0 does not appear as one of your choices, use the Browse button to locate POWER.XLA. By default, the POWER.XLA add-in file will be in your EXCEL\LIBRARY\POWER directory.

If you do not want the Power Utility Pak to be loaded automatically when you start Excel, choose the Tools Add-Ins command and de-select Power Utility Pak 2.0 before you exit Excel.

NOTE: The Power Utility Pak is written entirely in Excel's VBA macro language. The complete source code is available for a nominal fee. Studying this code is an excellent way to learn more about VBA. Contact JWalk and Associates for more information.

What's New in Version 2.0?

Version 2.0 of the Power Utility Pak adds four new utilities and six new worksheet functions. In addition, the worksheet functions are now in a separate file -- giving you more available memory if you don't need the functions. You'll also find that minor enhancements have been made to many of the original utilities. For example, (where appropriate) all of the utilities now let you undo their effects.

There is also a new toolbar, named "Power Utility Pak 2.0." If you are upgrading from a previous version, the old Power Uitlity Pak toolbar will be deleted automatically.

Here's a rundown of some of the major additions:

*	New Utility: Insert 3D Text

*	New Utility: Auditing Tools

*	New Utility: ToolbarTools

*	New Utility: Bubble Chart Wizard

*	Enhanced Utility: Select By Value

*	Enhanced Utility: Perpetual Calendar

*	New Worksheet Function: CountaVisible

*	New Worksheet Function: SumVisible

*	New Worksheet Function: InsertString

*	New Worksheet Function: FileExists

*	New Worksheet Function: PlaySound

*	New Worksheet Function: WhichDay

See Also...

The Utilities

Worksheet Functions

Enhanced Shortcut Menus

Configuring the Power Utility Pak


The Utilities

The Power Utlity Pak includes 21 utilities, accessible from a custom toolbar or from Excel's menu. For information about a particular utility, choose from the list below.

Text Tools	Reminder Alarm

Select By Value	Time Tracker

Interactive Zooming	Object Properties

Reminder Note	Super GoTo

Object Align, Size and Space	Workbook Contents

Calculator	Save With Backup

3D Cell Shading	Batch Printing

Insert 3D Text	Auditing Tools

Date and Time	ToolbarTools

Insert-a-Date	Bubble Chart Wizard

Perpetual Calendar


  Text Tools

This utility provides several new ways to manipulate text in selected cells. Before using the Text Tools utility, select one or more cells that you want to modify. You can also select entire colums or rows -- or even the entire worksheet. Then, click the Text Tools toolbar button or choose the Utilities Text Tools command. You will then see a dialog box in which you specify your text modifications.

The manipulations available to you are:

1.	Change the case of the text. You can convert the selected text to all upper case (CAPITALIZED), all lower case (no caps), or proper case (The First Letter Of Each Word Is Capitalized).

2.	Add new text to each cell. The new text can be inserted at the beginning of the existing text, or tacked on to the end of the existing text. Enter the new text in the box labeled Text, and then choose where you want the text to be added (to the left or to the right of the existing text).

3.	Delete a fixed number of characters from the text. Enter the number of characters to be deleted in the box labeled No. chars. to delete. Then select where you want the characters removed (from the beginning of the text or from the end).

4.	Remove excess spaces from text. If this checkbox is checked, the utility will remove all spaces from the beginning and end of text strings, and replace two or more spaces with a single space.

The Stats button displays the number of words and total characters in the selected text.

Tips

*	If the result of using this utility is now what you expected, choose the Edit Undo command (or Ctrl+Z) immediately. This will return to text in the selction to its previous state.

*	This utility has many uses. For example, if a worksheet contains a list of part numbers, you can quickly add "Part-" to the left of each label (to produce, for example, "Part-A12"). On the other hand, if you have a list of parts that all begin with the string "Part-" you can quickly remove this redundant text from each cell.

*	If the Ignore non-text cells checkbox is checked, the utility will work only on cells in the selection that contain text. If this checkbox is not checked, the utility will work with cells that contain either text or values. Cells that contain formulas are always skipped.

*	You can perform multiple text manipulations at once by making multiple selections in the dialog box. If you do so, it's important to understand that the operations take place in the following order:

1.	Excess spaces are removed

2.	Text is added

3.	Characters are removed

4.	The case is changed

*	The Text Tools utility is appropriate only for text in worksheet cells. If you attempt to use in any other situation, it won't work. For example, you cannot use the Text Tools utility to make a chart label upper case.

*	The Text Tools utility works only with text entered directly into cells. It does not work on strings that are returned by formulas or worksheet functions. For example, the Text Tools utility will have no effect on a cell that has a formula such as =UPPER(A1), even though that formula displays text in its cell.


  Select by Value

The Select by Value utility lets you select a group of cells based on their contents. In most cases, this will be a noncontiguous selection of cells. You can manually make a noncontiguous selection of cells by holding down the Ctrl key as you select cells, but the Select by Value utility is faster and more reliable.

For example, if you want to identify all cells in a range that have a negative value, the Select by Value utility can quickly select those cells for you. Then you can apply special formatting to make those cells stand out.

Start by selecting a range of cells. You can also select columns, rows, or even the entire worksheet. Then click the Select by Value toolbar button, or choose the Utilities Select by Value command. In the dialog box that appears, select the type of comparison you want, and then enter a value in the Target Value edit box. For example, to select all cells that are negative, you would click the Less than option button and enter 0 as the target value. When you click OK, the utility identifies the cells that qualify and selects them for you. Then, you can do whatever you want with the selection.

This utility also works with cells that contain text, by performing pattern matching based on wildcard characters. For example, if you want to select all cells in the current selection that contain text that begins with the letter J, choose the Similar to pattern option, and enter J* in the Target Value box. To select all cells that contain values that end in 4, use *4 for the Target Value. To select all cells that contain the letter Z, use *Z* for the Target Value.

The table below shows the wildcard characters you can use.

Character(s) in pattern	Matches

?	Any single character

*	Zero or more characters

#	Any single digit (0-9)

Tips

*	Once you've selected the cells, you can give this collection a name using the Insert Name Define command (or Ctrl+F3). Then, you can quickly select those same cells again at a later time by using the Edit Go To command (or the F5 shortcut key).

*	This utility is appropriate only for worksheet cells. If you attempt to use in any other situation, it won't work.

*	Selections made using the Similar to pattern option are not case-sensitive. Therefore, using *A* for the Target Value selects the same cells as using *a*.


  Interactive Zooming

The Interactive Zooming utility lets you magnify or reduce your worksheet or chart sheet interactively. You can preview the zooming you select automatically (if the Auto preview checkbox is checked), or manually by clicking the Preview button.

Tips

*	The Interactive Zooming utility can be activated only when a zoomable sheet is active (i.e., a worksheet or a chart sheet).

*	The amount of zooming in a worksheet or chart does not affect how it is printed. To adjust the printed output, use the File Page Setup command.

*	If you change your mind about zooming, just click the Cancel key to return to the zoom percent that was in effect before the dialog box came up. If you've already closed the dialog box, select the Edit Undo command (or Ctrl+Z) to return to the previous zoom factor.

*	If you select a group of cells (or one or more object on a chart sheet) before executing this utility, you can use the Fit Selection button to automatically zoom the worksheet to display only the selected cells.

*	The minimum zoom percentage is 10%, and the maximum is 400%.

*	If you use this utility when a chart sheet is active, it will automatically turn off the Size With Window option (a command under the View menu).


  Reminder Note

The Reminder Note utility lets you quickly add one of those ubiquitous yellow sticky notes to a worksheet, chart sheet, or dialog sheet. This is a good way to document your work, and you can choose to hide all reminder notes, or display them all.

The Reminder Note dialog box offers four choices:

Add a reminder note: Click this option to add a new reminder note to the active sheet. Clicking this option expands the dialog box to give you some additional options.

Hide all notes: This option hides all reminder notes on the active sheet.

Show all notes: This option unhides any hidden reminder notes on the active sheet. This is the default option.

Delete all notes: This option deletes all reminder notes on the active sheet.

Tips

*	You can double-click any of the four options above. This will make your selection and close the dialog box. If you double-lick the Add a reminder note option, the reminder note is added with the default settings.

*	You can choose the color of the note (yellow, white, or transparent), type style (normal, bold, or italic), whether the note will have a drop shadow effect, and whether the note will be printed with the worksheet.

*	After the reminder note is added to the worksheet, you can start typing the text for the note (there's no need to select it).

*	The reminder note is added to the worksheet at the active cell position.

*	You can move or resize the reminder note using the mouse, or the Object Properties utility.

*	If you don't like the idea of having notes to yourself displayed on the worksheet, use Excel's Insert Note command to insert a cell note. If you're using the Power Utility Pak Enhanced Shortcut Menus, the Cell Note command will appear with you right-click a cell.

*	Reminder notes cannot be put on a VBA module.


  Object Align, Size and Space

The Object Align, Size and Space utility is a fast, easy and reliable way to align, resize, and evenly space drawn objects on a worksheet, chart, or in a custom dialog box.

Start by selecting all of the objects that you want to align or resize (you need to select at least two objects). Then, activate the utility from the toolbar, by choosing the Utilities Object Align Size and Space command, or from the shortcut menu.

Aligning objects:

When aligning objects, one of the objects is the "reference" object -- the object that's used as a basis for the alignment and sizing of the other objects. There is no way to predict which object in the selection is the reference object, but you can change it using the drop-down list box. The alignment options are grouped into vertical (Left, Center, or Right) and horizontal (Top, Center, or Bottom).

Resizing objects:

To resize all selected objects, choose the appropriate Size option. You can make all objects the same vertical size as the reference object, the same horizontal size as the reference object, or the same size (the Both option) as the reference object.

Spacing objects:

You can make all of the selected objects evenly spaced, horizontally or vertically.

Tips

*	If the results of this procedure are not what you expected, use the Edit Undo comand (or Ctrl+Z). This will return the objects to their original size and positions.

*	You can select multiple objects by pressing the Ctrl key as you click each object, or by using the Drawing Selection tool on the Drawing toolbar.

*	It's a good idea to make a mental note of the name of the object you want to be the reference object before you invoke this utility. The object's name appears in the Name Box when you select it.

*	You can make several selections in the dialog box at once. For example, you can change the size of the objects, align them to the left, and space them out vertically -- all in one command.

*	To make precise adjustments for a single graphic object (or to identify its coordinates or size), use the Object Properties utility.


  Calculator

The Calculator utility displays a simple four-function calculator. You can use either a mouse or the keyboard to work with the calculator. You can also paste the result of the calculation into a cell. Sure, you can do all of these calculations right in a cell, but some people prefer the old fashioned way.

Tips

*	If you use the calculator from the keyboard, use the asterisk key (*) for multiplication.

*	The calculator remains active after you click the Paste button. To hide the calculator, click the Exit button.

*	You can change the cell into which the result gets pasted. Click in the edit box labeled Paste to, and then select the cell. The cell you select can be on the active worksheet, or in any other open worksheet.

*	If you select a range of cells while the dialog box is displayed, the result will be placed into all cells in the range.

*	If you invoke the Calculator utility when anything except a cell or range is selected, the Paste button and range box will not appear.


  3D Cell Shading

The 3D Cell Shading utility adds an attractive three-dimensional look to a cell or range of cells. You can choose from a raised look or a depressed look, select the line thickness, and even choose the color and pattern used.

Start by selecting the cell or range that you want to modify. Then, choose the 3D Cell Shading toolbar button or the Utilities 3D Effects 3D Cell Shading command. In the dialog box, select either the Raised or Depressed option, and make your line thickness selection. If you click the Change Color button, you can choose the color and pattern to be used. The Sample box will give you a preview of the results before you commit to it.

If the Erase existing borders checkbox is checked, any existing borders in the selection will be erased.

Tips

*	If the results aren't what you expected, choose the Edit Undo command (or Ctrl+Z) immediately. This will restore the previous formatting of the selected cells.

*	This utility also works with multiple selections. For example, you can select a noncontiguous group of cells, and the formatting will be applied to each group separately.

*	Usually, apply a light gray shading to all of the surrounding cells will maximizes the 3D effect. However, experiment until you get the effect you want.

*	You can change the interior color after the special formatting has been applied simply by selecting the cells and using the Color tool on the Formatting toolbar. The 3D effect will remain, but it will be a different color.

*	You can get some interesting effects by combining 3D Cell Shading with the effects produced by the Insert 3D Text utility.


  Insert 3D Text

This utility makes it easy to create attractive headings or labels that have a 3D look. The heading in the Contents topic in this Help file was created using this utility. The Insert 3D Text utility works by creating two text boxes, one of which is slightly offset from the other. The text boxes are placed precisely over the selected cell(s).

Start by formatting the text in a cell, or a horizontal range of cells (you may want to use Excel's Center Across Columns feature). Then select the cell or range and invoke the Insert 3D Text utility. The dialog box lets you select the shadow offset direction (horizontal and vertical), and also the amount of offset (Less, Normal, or More). You can also choose the color for the shadow. Another option will combine the two resulting text boxes into a single "group" object (this makes it easier to move the 3D text box to another location).

Tips

*	This utility works with the text contained in the upper left cell of the selection. If other cells in the selection also contain text the result probably won't be what you expect.

*	The text must be true text. In other words, this utility will not work with values or formulas.

*	If the shadow color is the same as the cell's background, the shadow will not be visible. For example, if you choose a white shadow and the cell has a white background, this utility will appear to have no effect. Hoever, if you move the 3D text box over some shaded the effect will be apparent.

*	The original text remains in the cell, and the 3D text box is placed precisely over the cell. You can, of course, move the 3D text box to any location (or even paste it into a chart). You can also delete the original text if you like.

*	The 3D text is not linked to the original cell. Therefore, if you change the text in the original cell you'll have to use this utility again.

*	Experiment with different fonts and colors. For example, there are manyTrueType outline fonts that look particularly good with this effect.

*	In general, you should try the Less option for smaller type sizes and the More option for larger type sizes.

*	If you don't choose to convert the results to a single grouped object, you can move one of the two text boxes slightly for a more pronounced 3D effect. You can ungroup the objects after the fact using Excel's Format Placement Ungroup command.

*	You can get some interesting effects by combining 3D text with 3D Cell Shading.

*	You might want to experiment with different character formatting. For example, you can use different fonts, sizes or colors within a single cell. The resulting 3D text box will reflect all of the cell's formatting.


 Date and Time

Why clutter your screen with a clock window? The current date, time, and day of the week is just a mouse click away.

Tips

*	The date and time displayed is retrieved from your system's clock. It should go without saying that if your clock is set incorrectly, this utility won't be of much value.

*	The date is displayed in the format specified in the Windows International settings. The time will be displayed in either 12-hour or 24-hour format, depending on the user's International settings.

*	To change the date or time of your system's clock, click the Fix Date/Time button. This will execute the Windows Control panel program that lets you correct your system's clock.

*	Changing your system's time will affect the times used by the Time Tracker and Reminder Alarm utilities.


  Insert-a-Date

The Insert-a-Date utility provides an easy and foolproof way to insert a date into a cell -- formatted exactly as you need it.

When this utility is executed the current date is displayed, along with a representation of the current month's calendar. You can change the month or year by using the scroller bars, and choose the exact day of the month by clicking any button within the calendar display. To change the formatting of the date, choose a format from the Date Format list box. The displayed date's format will change as you scroll through this list box.

The Insert button will paste the selected date (and format) to the cell indicated in the Paste to edit box.

Tips

*	If you change your mind after using this utility, select the Edit Undo command (or press Ctrl+Z). The worksheet will be returned to its former state (including any altered column widths).

*	The Insert-a-Date utility is only appropriate when a worksheet cell or range is selected. It will not work in any other situations.

*	The Insert-a-Date dialog box does not go away after you paste a date. This lets you select another cell and paste another date. To close the dialog box, click the Close button.

*	If the Adjust column width checkbox is checked, the column with will be adjusted automatically to fit the largest entry in the column in which you insert the date.

*	Clicking the Today button will display the current date and the current month's calendar.

*	If the date displayed when the utility is invoked is not the current date, this means that your system's clock is wrong. Use the Date and Time utility to change your system's clock.


 Perpetual Calendar

The Perpetual Calendar utility displays a handy pop-up calendar for any month in history (well, actually any month in years between 1900 and 2078). You can even paste a picture of the calendar into a worksheet or chart by clicking the Paste Calendar Picture button. Another option creates a schedule worksheet, all set up and formatted to display the monthly calendar you select.

When the Perpetual Calendar utility is invoked, it displays a calendar for the current month. You can select a different month or year by clicking the Year and Month scrollers.

Tips

*	If the Paste Calendar Picture button does not appear, it means that you are not in a place that can accept a pasted picture -- for example, a VBA module.

*	You can also access the Month and Year scrollers by pressing Alt+M and Alt+Y, respectively. Then you can use the arrow keys to adjust the month or year.

*	If the month displayed when the utility is invoked is not the current month, this means that your system's clock is wrong. Use the Date and Time utility to change your system's clock.

*	Clicking the Make Calendar Workbook button creates a new workbook with a single sheet, formatted as a calendar for the selected month. You can enter appointments or other information into the cells. This calendar prints nicely on a single sheet of paper in landscape mode.

*	By default, text entered into the cells in the Calendar Workbook is Arial 10-point, "word wrapped," centered vertically and aligned left (you can easily change this formatting using standard techniques). Also, note that you can begin a new line in a cell by pressing Alt+Enter.

*	When working in a Calendar Workbook, you may want to adjust the zoom factor so you can see the entire calendar on your screen. Use the Zoom Control on the Standard toolbar, or the Interactive Zooming utility.

*	The dates displayed in a Calendar Workbook are actually text boxes, which are protected so they cannot be activated. If you unprotect the worksheet you can move or change the text in the text boxes.

*	If you want to see a powerful example of Excel's array feature, examine the source code for this utility. The entire calendar is calculated using a single array formula.


 Reminder Alarm

The Reminder Alarm utility lets you enter a time, and will pop up a reminder message for you at the specified time. This is useful for those who get so wrapped up in their work that they lose track of time and miss meetings and appointments.

Accessing the Reminder Alarm utility displays a dialog box in which you enter the time at which you want to be reminded. Make sure the time is in the proper format ("hh:ss") and the appropriate option button (am or pm) is selected.

Tips

*	After setting an alarm, Excel's title bar displays the time the alarm is set for.

*	If Excel is not active when the alarm goes off, you'll still hear the three beeps that accompany the reminder alarm. Excel's title bar will also flash -- but you won't be able to see it if the application you're working in is maximized.

*	If the current time displayed when the utility is invoked is not accurate, this means that your system's clock is wrong. Use the Date and Time utility to change your system's clock.

*	If you change your system's clock when an alarm is set, the alarm time will not be reliable.


 Time Tracker

The Time Tracker utility will track the amount of time you spend working on various projects. You can track time spent on up to four projects.

When Time Tracker is loaded, it starts timing the first job (labeled "Not accounted for" by default). When you start working on another job, call up Time Tracker again and click the Start button that corresponds to the job you're working on. Timing for the current job stops, and timing for the next job begins.

Tips

*	The job that's currently being timed appears with a red symbol next to it.

*	To reset the time for a job to zero, click the appropriate Reset button.

*	You'll probably want to give meaningful names to the projects you're working on. To do so, click the Change Descriptions button to bring up a dialog box that lets you enter meaningful names for the four jobs. If you check the checkbox labeled Save these names, the names will be saved and will be used whenever you start Excel.

*	The names for your four jobs are stored in EXCEL5.INI, under the [Power Utility Pak] section. If you prefer, you can edit this file manually, and the changes will take effect the next time you invoke the Time Tracker utility.

*	If you change your system's clock when you're timing jobs, the times may not be reliable.

*	Time Tracker performs its calculations by using the number of seconds past midnight. Therefore, if you work past midnight, the time tracked will be incorrect.


Object Information
  Object Properties

The Object Properties utility displays information about graphic objects. It tells you the dimensions and position of the object, and whether it is hidden. You can change any of these properties in the Object Properties dialog box.

The Objects list box displays the names of all graphic objects on the active sheet. If you invoke the utility when an object is selected, that object will be selected in the list box. As you scroll through the list, the dimensions and coordinates of the object appear in the right side of the dialog box.

To change any of these properties, just activate the appropriate edit box, enter your change, and click the Change button.

Tips

*	If you change your mind after using this utility, select the Edit Undo command (or press Ctrl+Z). All of the altered objects will be returned to their prior condition.

*	The dialog box stays open after you click the Change button. This lets you work with another object.

*	If the object is hidden, you can check the Unhide object checkbox to unhide it. If the object is not hidden, you can check the Hide object checkbox to hide it.

*	If you don't have a mouse connected, this utility is the only way you can adjust graphic objects (short of writing a macro to do so). Also this utility is the only way you can hide or unhide a graphic object without using a macro.

*	The measurement units used by Excel do not have infinite resolution. In fact, Excel always converts the values you enter so they are divisible by 0.75. For example, if you enter 6.8 it is converted to 6.75.

*	The Object Properties utility works with only one object at a time. In other words, you can't select multiple objects and adjust them all at once.

*	To size, align, or evenly space a collection of graphic objects, use the Object Align, Size and Space utility.


  Super GoTo

The Super GoTo utility is useful when you have several workbooks open, and you need to activate or view a particular sheet or range. You can think of it as a more versatile version of the Workbook Contents utility.

When the utility is invoked, it displays a list of all open workbooks (including hidden workbooks) in a drop-down list (it also tells you how many workbooks are open). The list box to the right shows the names of all sheets in the selected workbook. To view a particular sheet, select a workbook from the drop-down list and a sheet from the list box, and click the Preview button. To activate the selected sheet, click the Go To button.

If you click the More >> button, the dialog box expands to display another list box that contains all of the named ranges and constants in the workbook. If you choose a name from the list before you click the Go To button, that range will be selected when the sheet is activated. The expanded dialog box also shows the definition for the selected name.

Tips

*	If the Auto View checkbox is checked, you don't need to click Preview to view a sheet -- it will be displayed automatically when you select a sheet from the list box.

*	When the dialog box is expanded, the checkbox labeled Sheet level names only lets you limit the display of names to the selected worksheet only. Ranges names are not sheet level names unless you specifically designate them as such. Therefore, you may not see any names when you check this option.

*	If you choose Auto View when the dialog box is expanded to show the second list box, it will automatically shrink to its original size.

*	The Super GoTo utility also lets you preview sheets in hidden workbooks and workbooks that are minimized. If you choose to Go To such a workbook, the workbook will be unhidden or restored in size.


  Workbook Contents

The Workbook Contents utility displays a handy table of contents that lists all sheets in the active workbook (much like Excel 4 did). When you select a sheet from the list box, the sheet type is displayed below (worksheet, macro sheet, chart, etc.). You can also filter the displayed list to show only worksheets, modules, or dialog sheets. Clicking the GoTo button will quickly activate the selected sheet. The Super GoTo utility is a more versatile version of this utility.

Tips

*	You can double-click a sheet name to activate it rather than clicking the GoTo button.

*	The sheets are listed in the order in which they appear in the workbook. If you want to rearrange your sheets alphabetically, choose the Sort Workbook Tabs command on the shortcut menu that appears when you right-click a tab. For more information, see Enhanced Shortcut Menus.

*	This utility is also available on the shortcut menu that appears when you right-click a worksheet tab. For more information, see Enhanced Shortcut Menus.


  Save With Backup

The Save With Backup utility saves your worksheet and also makes a backup on a floppy disk or in another directory.

Normally, saving a workbook to a floppy disk to make a backup is a cumbersome procedure since Excel "remembers" the floppy drive as the workbook's location. When you save your file again, it will be saved to the floppy disk -- unless you remember to use the File Save As command to redirect the save back to your hard drive. This utility saves your work to the floppy disk (or other location) of your choice, and then saves it back to its original directory -- all in one step.

Tips

*	The backup medium does not have to be a floppy disk. If you click the Other option button, you can specify a different directory or drive. For example, some people like to make a backup copy of their work on a shared network drive.

*	If you last saved your file to a floppy disk, this utility will not let you make a backup. It's not a good practice to save files to a floppy disk (except for backup purposes).

*	If you haven't yet saved your file (i.e., it has a name like "Book1"), you'll have the opportunity to provide a better name before making the backup.

*	Saving to a floppy disk is much slower than saving to your hard drive, so be patient.


  Batch Printing

The Batch Printing utility lets you print the contents of several workbooks on disk with a single command. You can choose the workbooks and specify what to print (worksheets, chart sheets, VBA modules, or dialog sheets). The Batch Printing utility loads each file you select, prints what you want, and then closes the file. After you select the files, you can go to lunch and come back and retrieve your print outs.

If you want to leave while the printing occurs, choose Unattended mode. In this mode, worksheets that have links will not be updated before printing (normally, Excel prompts you for this information). See the tip below for more details.

If you choose the Quiet mode checkbox, you will not see the workbooks being loaded. If you prefer to watch the files being loaded, don't use quiet mode.

Tips

*	The Batch Printing utility has two dialog boxes. In the first one, you choose which sheet types you want to print. In the second, you select the files you want to print.

*	To select multiple files, hold down the Ctrl key as you click the file names. Hold down Shift to select a group of contiguous file names.

*	In unattended mode, prompts for password-protected workbooks will still appear. Therefore, for truly unattended batch printing, make sure that none of the files you select are password-protected.

*	Printing is done using the print specifications stored in the workbook.


  Auditing Tools

This utility generates a new workbook with useful information about the active worksheet. The new workbook contains up the three sheets:

Worksheet Map

A graphical map of the worksheet. This is coded so you can identify formulas, values, text, logicals, and error values.

Formula List

A list of all of the formulas on the worksheet. This is similar to the feature found in other spreadsheet products.

Summary Report

Contains summary type information about the worksheet and its workbook, including a list of all named ranges.

Tips

*	The Worksheet Map can often help you located cells with incorrect information. For example, it makes it easy to spot a value accidentally placed within a group of formulas.

*	The Worksheet Map is based on the worksheet's "active area." If you delete some rows or columns, the active area will not be updated until you save and then re-open the workbook (this is one of Excel's quirks). Therefore, the Worksheet Map may not accurately reflect the worksheet's "true" active area.

*	The workbook created by this utility identifies the audited workbook and worksheet in its title bar.


  Bubble Chart Wizard

The Bubble Chart Wizard converts an existing X-Y (Scatter) chart to a "bubble chart." Normally, Excel does not support this type of chart. The utility works by replacing the X-Y chart markers with circles (i.e., bubbles). The size of the bubbles are proportional to a user-designated data range. You can also specify the color for each bubble.

The first step is to create an X-Y chart from your data. This chart must be embedded on a worksheet. After creating your X-Y chart, select the chart by clicking it. Then select the Utilites Bubble Chart Wizard command or the Bubble Chart Wizard toolbar button. Either method will display the Bubble Chart Wizard dialog box.

The upper portion of the dialog box has two options:

Add bubbles to chart

Choosing this option will add bubbles to the selected X-Y chart. If the chart already has bubbles, they will be re-generated. You may want to regenerate the bubbles to adjust the bubble scaling factor (change the relative size of the bubbles).

Remove bubbles from chart

Choosing this option will remove the bubbles from an X-Y chart. Use this option to convert a bubble chart back to a standard X-Y chart.

The scale factor determines the relative size of the bubbles. Increasing the scale factor increases the size of the bubbles. You can enter a scale factor manually, or click the Auto scaling option. When auto scaling is in effect, the bubbles will be sized automatically to fit into the chart. You may have to experiment with the scale factor to get the bubbles sized to your liking.

The Bubble Chart Wizard can use three additional ranges of data. The size of each range must correspond to the number of data points in the chart's Y series.

Bubble data range

Required. This range holds the data that is used to create the bubbles.

Bubble colors

Optional. This range holds integer codes that determine the color of each bubble. The colors are based on the current color palette. Valid color codes are 0 through 56. If this range is not specified, the bubbles will all be white.

Data labels range

Optional. This range holds data labels that will be displayed next to each bubble.

The Bubble Chart Wizard dialog box has two other options.

Re-scale axes to fit bubbles

If this option is chosen, the chart's axes will be re-scaled to accommodate the bubbles. In other words, the scales will be extended on both ends. Check this option if you want to ensure that the bubbles are completely visible.

Don't change data labels

If this option is checked, the data labels will not be re-applied when the bubbles are re-generated. Check this option if you've manually repositioned the data labels and don't want their positions to change.

Tips

*	Because of some quirks with Excel 5, this utility does not work with charts stored on separate Chart sheet.

*	Only the first data series in the X-Y chart is converted to bubbles.

*	The ranges you specify as the Bubble data range, Bubble colors, and Data labels are not linked to the chart. Therefore, if you make any changes to these ranges you must use the utility again.

*	When you specify a Data labels range, often the labels are not placed optimally. You can reposition the data labels manually by clicking and dragging.

*	A bubble chart may not be appropriate if the X-Y chart has too many data points.

*	The Clear button removes the ranges specified in the Bubble data range, Bubble colors, and Data labels range edit boxes.


  Toolbar Tools

This utility provides some additional tools that are useful when working with custom toolbars. It lets you perform functions that are otherwise impossible (copying or renaming a toolbar), or that require a macro (changing the tooltips).

When you invoke the Toolbar Tools utility, the dialog box displays a list of all toolbars available. The following buttons are available, which work on the selected toolbar:

Hide/Show

Displays the selected toolbar if it is not already displayed. Hides the selected toolbar if it is displayed. The toolbar is displayed at its most recent position. In some cases, it may be obscured by the Toolbar Tools dialog box.

Delete

Deletes the selected toolbar. This button is not available when a built-in toolbar is selected, since these toolbars cannot be deleted. You will be asked to confirm this selection.

Rename

Renames the selected toolbar. This button is not available when a built-in toolbar is selected since these toolbars cannot be renamed. You will be asked for a new name for the toolbar.

Copy

Makes an exact copy of the selected toolbar. You will be asked for a name for the new toolbar.

Edit ToolTips

Displays another dialog box that lets you change the tooltips for each toolbar button. (A tooltip is the small yellow box that appears when the mouse pointer pauses over a toolbar button.) This dialog box is described below.

Help

Displays the help topic you're currently reading.

Exit

Closes the dialog box.

ToolTip Editor

When you click the Edit ToolTips button, the ToolTip Editor dialog box is displayed. The top part lets you select a toolbar to work with. The listbox contains the tooltips for each button in the selected toolbar. Notice that "gaps" between the buttons are treated as a button (but they don't have tooltips). As you scroll through this listbox, the toolbar button's image is displayed in the dialog box.

To change the tooltip for the selected toolbar button, click the Change ToolTip button. You will then be prompted to enter a new tooltip. You can also select a different toolbar from this dialog box. When you are finished editing tootips, click the Exit button.

Tips

*	Changes made to toolbars are not saved permanently until you exit Excel. If you make many changes, you may want to exit Excel in order to save them (if Excel crashes before you exit, your toolbar changes will be lost). Toolbars are stored in EXCEL5.XLB, which is located in your WINDOWS directory.

*	It's a good idea to delete any toolbars that you no longer need. This reduces Excel's start-up time and helps conserve system resources.


New Worksheet Functions
 Power Worksheet Functions

The Power Utility Pak 2.0 includes 23 new functions that you can use in your worksheets (or within your VBA code). These functions work exactly like Excel's built-in functions. You can even use Excel's Function Wizard to select the function you need and get help with the arguments. The shareware version contains only a sample (eight) of the 23 functions.

Important:
The function definitions are stored in PUPFUNCS.XLA. If you create a formula that uses one of these functions, the PUPFUNCS.XLA file must be loaded whenever you use that workbook -- otherwise the formula will return the #NAME? error. Similarly, if you distribute a workbook that uses any of these functions, make sure you include the PUPFUNCS.XLA file. This file (and this file only) may be distributed freely to end users of your applications.

You can install PUPFUNCS.XLA using the Tools Add-Ins command. After selecting this command, use the Browse button to locate PUFUNCS.XLA. If it is installed using this method, there is no need to load it using the Power Utility Pak's Utility menu or toolbar.

Note:
In this Help file, the functions are grouped into four categories. However, if you use the Function Wizard, look for these new functions under the Power Utility Pak category.

To use any of these functions in your VBA code, you must first establish a reference to PUPFUNCS.XLA. To do this, activate a VBA module and choose the Tools References command. Then click the PUPFUNCS.XLA checkbox in the Available References listbox. In most cases, it will be more efficient to include the original VBA function in your code. If you purchase the source files for the Power Utility Pak, you will be able to copy the desired functions to your own VBA module and avoid having to set up a reference to PUPFUNCS.XLA.

To learn more about the Power Utility Pak worksheet functions, choose a category from the list below.

Text functions

Date functions

Information functions

Other functions

See Also...

The Utilities

Enhanced Shortcut Menus


Text Functions Overview
Text Functions

These functions add new capabilities when dealing with text information in cells.

Contains	RemoveSpaces

InsertString	Scramble

IsLike	SelectOne

ParseName

See Also...

Date functions

Information functions

Other functions


CONTAINS function

Returns TRUE if a string contains a specified sub-string.

Syntax

CONTAINS(text1,text2,casesensitive)

Arguments

text1: A text string or reference to a text string.

text2: A text string or reference to a text string. If text1 is contained within text2, the function returns TRUE.

casesensitive: Optional. Determines whether the comparison is case-sensitive. If this argument is missing or zero, the comparison is case sensitive. If the argument is anything than missing or zero, the comparison is not case sensitive.

Tips

*	If the third argument is omitted, the comparison will be case-sensitive.

*	The arguments can be cell references or entered directly.

*	The function also works with values (see the example below).

*	For an inexact comparison, see IsLike.

Examples

CONTAINS("a","Farmer") returns TRUE since "Farmer" contains "a".

CONTAINS("A","Farmer") returns FALSE since "Farmer" does not contain "A". Since the third argument is omitted, the comparison is case-sensitive.

CONTAINS("A","Farmer",1) returns TRUE since "Farmer" contains "A". Since the third argument is not omitted or zero, the comparison is not case-sensitive.

CONTAINS(A1,A2,1) will return TRUE if the text in cell A1 is contained in the text in cell A2 (not case sensitive).

CONTAINS(12,123) return TRUE because 12 is contained in 123. Notice that this example uses values rather than text strings.


INSERTSTRING function

Inserts a text string at a specified location within another string.

Syntax

INSERTSTRING(instring,origstring,pos)

Arguments

instring: A text string or reference to a text string. This is the text that gets inserted.

origstring: A text string or reference to a text string. This is the string that will contain InString.

pos: The position in OrigString in which to insert InString. A value of 1 inserts InString before the first character in OrigString.

Tips

*	If Pos is less than or equal to zero, the function returns OrigString.

*	If Pos is greater than the length of OrigString, the function inserts InString at the end of OrigString .

Examples

INSERTSTRING("Q. ","John Public",6) returns "John Q. Public". This is equivalent to the more complex formula: LEFT("John Public",5)&"Q. "&RIGHT("John Public",6)

INSERTSTRING(A1,A2,1) simply concatenated the text in A1 with the text in A2. This is equivalent to A1&A2.


ISLIKE function

Returns TRUE if a string matches a pattern.

Syntax

ISLIKE(text1,pattern)

Arguments

text1: A text string or a reference to a cell that contains a text string.

pattern: A string that contains wildcard characters according to the list below

Character(s) in pattern	Matches in text1

?	Any single character.

*	Zero or more characters.

#	Any single digit (0-9).

[charlist]	Any single character in charlist.

[!charlist]	Any single character not in charlist.

Tips

*	This function is quite versatile, and also works with values (see examples below).

*	Comparisons are alway case-sensitive (but see the fourth example below).

Examples

ISLIKE("guitar","g*") returns TRUE, since * matches any number of characters. It will return TRUE if the first argument is any text that begins with "g".

ISLIKE("Unit1","Unit?") returns TRUE since ? matches any single character. If the first argument were "Unit12", the function would return FALSE.

ISLIKE("a","[aeiou]") returns TRUE since the first argument is a single character in the second argument. To determine if a string contains multiple characters, see Contains.

ISLIKE(UPPER(A1),UPPER("[aeiou]")) returns TRUE if cell A1 contains either a, e, i, o, u, A, E, I, O or U. Using the UPPER function for the arguments makes it not case-sensitive.

ISLIKE("g","[!aeiou]") returns TRUE since the first argument is a single character that's not in the second argument.

ISLIKE(A1,"1##") returns TRUE if cell A1 contains a value that begins with 1 and has exactly three digits (i.e., any integer between 100 and 199).


PARSENAME function

Splits a string that contains a name into its component parts: first name, middle name, and last name.

Syntax

PARSENAME(nametext,name_seq)

Arguments

nametext: Text in the form of a name. Examples include "John Q. Public", "Bob Jones", Mrs. Doris M. Johnson", and "Dr. Richard Kimball, Jr."

name_seq: A number that determines which name is returned, according to the following list.

Name_Seq	Name returned

1	First name

2	Middle name

3 	Last name

Tips

*	This function is relatively intelligent, since it can deal with titles (such as Mr. and Ms.) and even people who have a "Jr." in their name. Titles are simply ignored, and Jr. is tacked on to the last name.

*	To completely parse a name, use this function three times, each with a different second argument.

*	If the name to be parsed is all upper case, you can combine this with the PROPER function to return a name that includes bother upper and lower case (see the example below).

Examples

PARSENAME("Tom M. Smith",3) returns Smith.

PARSENAME("Mr. Tom M. Smith",1) returns Tom.

PARSENAME("Mr. Tom M. Smith Jr.",1) returns Smith Jr.

PARSENAME("Tom Smith",2) returns nothing since there is no middle name.

PROPER(PARSENAME("TOM M. SMITH",3)) returns Smith.


REMOVESPACES function

Removes all of the spaces from a string.

Syntax

REMOVESPACES(text)

Argument

text: Text that is to be de-spaced.

Tips

*	This function gets rid of all spaces. If you simply want to get rid of extra spaces (i.e., convert two or more to a single space) use Excel's TRIM function. Or, use the Text Tools utility supplied with the Power Utility Pak.

Example

REMOVESPACES("Happy Birthday to You") returns "HappyBirthdayToYou".


SCRAMBLE function

Accepts text or a value and returns it scrambled randomly.

Syntax

SCRAMBLE(text,recalc)

Arguments

text: Text that is to be scrambled.

recalc: Optional. Determines whether the text will be rescrambled when the worksheet is recalculated. If this argument is missing or zero, the text will not be rescrambled. It it's anything other than missing or zero, the text will be rescrambled when the worksheet is calculated.

Tips

*	This function might be useful if you like to do those Jumble games that are published in some newspapers.

*	This function also works with values.

Examples

SCRAMBLE("Hello") returns "lolHe" (or some other scrambled version of "Hello").

SCRAMBLE(1234) returns 3142 (or some other scrambled version of 1234).


Date Functions Overview
Date Functions

These functions add new capabilities when dealing with date information in cells.

DaysInMonth

MonthWeek

WhichDay

See Also...

Text functions

Information functions

Other functions


DAYSINMONTH function

Returns the number of days in the month for a date.

Syntax

DAYSINMONTH(serial_number)

Argument

serial_number: Any valid date serial number.

Tips

*	This function is useful for applications that deal with time -- payroll calculation, for example.

Examples

DAYSINMONTH(34456) returns 31, since 34456 is the serial number for May 2, 1994 and May, 1994 has 31 days.

DAYSINMONTH(TODAY()) returns the number of days in the current month.


MONTHWEEK function

Returns the calendar week for a date serial number

Syntax

MONTHWEEK(serial_number)

Argument

serial_number: Any valid date serial number

Tips

*	The maximum number returned by this function is 6. This can occur, for example, when a month starts with a Saturday or Sunday and has 31 days.

Examples

MONTHWEEK(34516) returns 1, since 34516 is the serial number for July 1, 1994 (a Saturday).

MONTHWEEK(34518) returns 2, since 34518 is the serial number for July 3, 1994 (a Monday).

MONTHWEEK(34546) returns 6, since 34546 is the serial number for July 31, 1994 (a Sunday).


WHICHDAY function

Returns a date that corresponds to a specified day in a month. For example, this function can tell you the date of the first Friday in January for a given year.

Syntax

WHICHDAY(weekdaynum,DOW,themonth,theyear)

Arguments

weekdaynum: An integer between 1 and 5 that corresonds to the day of interest. For example, to return the first Friday in a month, this argument will be 1. A value of 5 returns the last specified weekday in the month.

DOW: An integer between 1 and 7 that corresponds to the day. Sunday is 1, Monday is 2, and so on.

themonth: An integer between 1 and 12 that corresponds to the month. January is 1, February is 2, and so on.

theyear: An integer that corresponds to the year

Tips

*	The value returned is a date serial number. You may have to format the cell to make it appear as a date.

*	A value of 5 for weekdaynum always returns the last specified day. Depending on the month, this will return either the fourth or fifith occurrence of that date.

Examples

WHICHDAY(1,2,3,1995) returns 34764, the date serial number for March 6, 1995. This date is the first (1) Monday (2) in March (3), 1995.

WHICHDAY(5,2,4,1995) returns the date serial number that corresponds to the last Monday in April, 1995. Since this month has four Mondays, the same result would be returned by WHICHDAY(4,2,4,1995).


Information Functions Overview
Information Functions

These functions return various types of information about your system.

DOSVersion	GetINISetting

ExcelDir	User

FileName	WindowsDir

FileExists	WindowsVersion

See Also...

Text functions

Date functions

Other functions


DOSVERSION function

Returns the version number of DOS that is currently running.

Syntax

DOSVERSION()

Arguments

(no arguments)

Tips

*	This is useful if you have a macro that uses calls that will only work with a certain version of DOS.

Example

DOSVERSION() will return 6.20 if the system is running DOS version 6.2.


EXCELDIR function

Displays the full path for the directory in which Excel is installed.

Syntax

EXCELDIR()

Arguments

(no arguments)

Tips

*	The path returned does not include a final backslash, so if you're combining this with a filename, make sure you add a "\". See the example for GetINISetting.

Example

EXCELDIR() returns the drive and name of the directory in which Excel is installed. For example, it may return C:\EXCEL.


FILENAME function

Displays the full path and file name of the workbook.

Syntax

FILENAME()

Arguments

(no arguments)

Tips

*	If the file has not been saved yet, this function will return #NA.

*	This function is useful if you like to have the workbook's full path and file name appear on your printouts. Just enter the FILENAME function in any cell in the print range.

Example

FILENAME() returns the drive, directory, and name of the workbook file. For example, it may return C:\SHEETS\BUDGET\LASTYEAR.XLS.


FILEEXISTS function

Returns TRUE if the specified file exists; FALSE otherwise.

Syntax

FILEEXISTS(filename)

Arguments

filename: The name of a file, and (optionally) it's complete path.

Tips

*	If you omit the path, the function looks for the file in the current directory. The current directory can change. For example, if you open a file, the current directory is changed to the directory of the opened file.

Example

FILEEXISTS("c:\windows\win.ini") returns TRUE if WIN.INI is found in the C:\WINDOWS directory.


GETINISETTING function

Returns the setting for a specified entry in an INI file.

Syntax

GETINISETTING(INIFile,section,entry)

Arguments

INIfile: The name and complete path of the INI file you're interested in. For example, this argument might be "C:\WINDOWS\EXCEL5.INI".

section: The section of the INI file you're interested in. INI file sections appear in square brackets within the INI file. For example, [Add-In Manager] is a section in EXCEL5.INI.

entry: The entry within a section that you're interested in. For example, an entry in the [AutoSave] section of EXCEL5.INI is Frequency.

Tips

*	Excel's INI file is stored in the WINDOWS directory. Use the WindowsDir function to determine the exact path of this directory.

Example

GETINISETTING(WINDOWSDIR()&"\WIN.INI","Desktop","Wallpaper") returns the file used for the Windows wallpaper.

GETINISETTING(WINDOWSDIR()&"\EXCEL5.INI","Recent File List","File1") returns the first file from the recent file list.


USER function

Returns the name of the current user.

Syntax

USER()

Arguments

(no arguments)

Tips

*	This is based on the information entered in the Options dialog box (General tab). If the name listed there is wrong, the User function will also return the wrong name.

*	This is useful for personalizing messages -- for example, "Insert the disk, John."

Example

USER() returns the name of the user.

LEFT(USER(),FIND(" ",USER())) returns the first name of the user.


WINDOWSDIR function

Displays the full path for the Windows directly on the system.

Syntax

WINDOWSDIR()

Arguments

(no arguments)

Tips

*	The path returned does not include a final backslash, so if you're combining this with a filename, make sure you add a "\". See the example for GetINISetting.

Example

WINDOWSDIR() returns the drive and name of the directory in which Windows is installed. For example, it may return C:\WINDOWS.


WINDOWSVERSION function

Returns the version number of Windows that is currently running.

Syntax

WINDOWSVERSION()

Arguments

(no arguments)

Tips

*	This is useful if you have a macro that uses calls that will only work with a certain version of Windows.

*	This function returns 3.10 if the system is running Windows for Workgroups 3.11. Apparently Microsoft does not consider WFW 3.11 to be a different version than standard Windows 3.1.

Example

WINDOWSVERSION() will return 3.10 if the system is running Windows 3.1.


Other Functions Overview
Other Functions

These six functions add new capabilities to your worksheet formulas.

NoExtremesAvg

PlaySound

SelectOne

StaticRand

SumVisible

CountaVisible

See Also...

Text functions

Date functions

Information functions


NOEXTREMESAVG function

Returns the average of a list of values, but excludes the highest and lowest values.

Syntax

NOEXTREMESAVG(number1,number2,...)

Arguments

number1, number2...: 1 to 30 arguments from which to calculate the average. Only number1 is required.

Tips

*	Excluding extremes from averages is a relatively common procedure in some disciplines, since it eliminate the effects of extreme values or outliers.

*	You can mix and match the argument types. For example, NOEXTREMESAVG(A1:A10,25,12) is a valid usage.

Examples

NOEXTREMESAVG(A1:A10) returns the average of the values in A1:A10, excluding the highest and lowest value.

NOEXTREMESAVG(1,2,3,4,5,6,7,8,9,10) returns 5.5, which is the average of 2, 3, 4, 5, 6, 7, 8, and 9.


SELECTONE function

Returns the value in a cell chosen at random from a range.

Syntax

SELECTONE(value1,value2,...)

Arguments

value1, value2...: 1 to 30 arguments from which to select a random value. The arguments can be cell or range references, or values that you enter directly. Only value1 is required.

Tips

*	This function can be used to randomly select a "winner" from a list of entries.

*	You can mix and match the argument types. For example, SELECTONE(A1:A10,"Bob",25) is a valid usage.

Examples

SELECTONE(A1:A10) returns a randomly selected value from the range A1:A10.

SELECTONE(1,2,3,4,5) returns either 1, 2, 3, 4, or 5.

SELECTONE(TRUE,FALSE) returns either logical TRUE or FALSE.

SELECTONE(A1:A10,"Bob",25) returns either a randomly selected value from the range A1:A10, the string "Bob", or the value 12. Each of the 12 outcomes is equally likely.

SELECTONE(1,2,3,4,5,6)+SELECTONE(1,2,3,4,5,6) simulates the toss of two dice.

SELECTONE("Ace",2,3,4,5,6,7,8,9,10,"Jack","Queen","King")&" of "&SELECTONE("Hearts","Spades","Clubs","Diamonds") simulates drawing one playing card from a deck.


STATICRAND function

Returns a random number that doesn't change when the worksheet is recalculated.

Syntax

STATICRAND()

Arguments

(no arguments)

Tip

*	This function is very similar to Excel's RAND() function -- except RAND() returns a different value whenever the worksheet is recalculated.

Examples

STATICRAND() returns a random number between 0 and 1

STATICRAND()*(100-1)+1 returns a random number between 1 and 100.


PLAYSOUND function

Plays a specified .WAV sound file.

Syntax

PLAYSOUND(filename)

Arguments

filename: The name and path (optional) of the sound file to play.

Tip:

*	You can omit the path if the sound file is located in a directory that is in the DOS path.

*	If the sound file cannot be found, the function returns FALSE.

Examples

PLAYSOUND("recalc.wav") plays a sound file named RECALC.WAV whenever the worksheet is calculated.

IF(A1>100, PLAYSOUND("tada.wav")) plays a sound file named TADA.WAV only if the value in cell A1 is greater than 100.


COUNTAVISIBLE function

COUNTAVISIBLE is similar to Excel's COUNTA function, but it returns the count of just the visible cells with values. This function is well-suited for use with AutoFiltering and Outlining.

Syntax

COUNTAVISIBLE(value1,value2...)

Arguments

value1, value2...: 1 to 30 arguments that will be counted. The arguments can be cell or range references, or values that you enter directly. Only value1 is required.

Tips

*	Use this function in conjunction with the Data Filter AutoFilter command to obtain the number of rows that pass the filter.

*	Because of a flaw in Excel, this function should not be used with an argument that encompasses entire rows or columns. Recalulation will take far too long.

Examples

COUNTAVISIBLE(A1:A100) returns the number of cells that contain values in the visible rows in the range A1:A100.

COUNTAVISIBLE(A:A) is valid, but it will take far to long to recalculate. It's best to avoid this type of usage.

See Also...

SumVisible


SUMVISIBLE function

SUMVISIBLE is similar to Excel's SUM function, but it returns the SUM of just the visible cells. This function is well-suited for use with AutoFiltering and Outlining.

Syntax

SUMVISIBLE(number1,number2...)

Arguments

number1, number2...: 1 to 30 arguments that will be summed. The arguments can be cell or range references, or values that you enter directly. Only number1 is required.

Tips

*	Use this function in conjunction with the Data Filter AutoFilter command to obtain the sum of rows that pass the filter.

*	Because of a flaw in Excel, this function should not be used with an argument that encompasses entire rows or columns. Recalulation will take far too long.

Examples

SUMVISIBLE(A1:A100) returns the sum of the cells in the visible rows in the range A1:A100.

SUMVISIBLE(A1:A100, A501:A600) returns the sum of the cells in the visible rows in the range A1:A100, plust the sum of the cell sin the visible rows in the range A501:A600.

SUMVISIBLE(A:A) is valid, but it will take far to long to recalculate. It's best to avoid this type of usage.

See Also...

CountAVisible


Enhanced Shortcut Menus

Excel's shortcut menus are great -- right-click after selecting an object and you get a handy menu of commands. But When the Power Utility Pak is loaded, you'll have even more choices in those menus. Choices that will make you work even more efficiently.

In some situations you may prefer not to use the enhanced shortcut menus. You can return to the normal shortcut menus by choosing the Configure Power Utility Pak command on the Utilities menu (or by clicking the Configure Power Utility Pak button on the Power Utility Pak toolbar). Simply uncheck the option labeled Display enhanced shortcut menus. See Configuring the Power Utility Pak.

Following is a list of the enhanced shortcut menu commands. The commands will vary depending upon what is right-clicked.

Worksheet Cell or Range

*	Object Properties...

Displays a list of all graphic objects on the active sheet, and lets you change their dimensions or position. See Object Properties.

*	Remove All Formatting

Removes all formatting from the selection. Equivalent to the Edit Clear Formats command.

*	Cell Note...

Displays the Cell Note dialog box for the active cell. Equivalent to the Insert Note command.

*	Toggle Word Wrap

Toggles the Wrap Text setting for the current selection. The toggling is based on the active cell in the selection.

*	Format Number...

Apply numeric formatting to the selection. It displays the Number tab from the Format cells dialog box.

Desktop

*	Minimize All Windows

Minimizes all worksheet windows.

*	Tile Windows

Equivalent to the Window Arrange command, with the Tiled option.

*	Super GoTo...

Quickly preview or activate any workbook, sheet, or range. See Super GoTo.

Graphic Object

*	Object Properties...

Displays a list of all graphic objects on the active sheet, and lets you change their dimensions or position. See Object Properties.

*	Align, Resize, and Space...

Lets you change the alignment, size, or spacing of multiple graphic objects. See Object Align, Size and Space.

Sheet Tab

*	Hide Sheet

Hides the active sheet. Equivalent to the Format Sheet Hide command (or the Edit Sheet Hide command when a VBA Module is active).

*	Workbook Contents...

Makes it easy to activate a particular sheet by displaying a list of all sheet names in the workbook. See Workbook Contents.

*	Sort Workbook Tabs

Sorts the sheet names in alphabetical order. This is a feature unique to the Power Utility Pak.

Workbook Title Bar

*	Toggle Vertical Scroll Bar

Toggles the display of the vertical scroll bar. Equivalent to the Tools Options command, with the View tab selected (Vertical Scroll Bar checkbox).

*	Toggle Horizontal Scroll Bar

Toggles the display of the horizontal scroll bar. Equivalent to the Tools Options command, with the View tab selected (Horizontal Scroll bar checkbox).

*	Toggle Grid Lines

Toggles the display of the grid lines. Equivalent to the Tools Options command, with the View tab selected (Gridlines checkbox).

*	Toggle Row and Column Headers

Toggles the display of the row and column headers. Equivalent to the Tools Options command, with the View tab selected (Row & Column Headers checkbox).

*	Toggle Sheet Tabs

Toggles the display of the workbook sheet tabs. Equivalent to the Tools Options command, with the View tab selected (Sheet Tabs checkbox).

See Also...

The Utilities

New Worksheet Functions

Configuring the Power Utility Pak


  Configuring the Power Utility Pak

The registered version of the Power Utility Pak includes SETUP.XLA. This file installs the files for you automatically. To install the shareware version, refer to the instructions in README.WRI.

Important: POWER.HLP must be located in your EXCEL directory.

Once it's installed and loaded into memory, you can configure the Power Utility Pak by clicking the toolbar button labeled Configure the Power Utility Pak, or by choosing the Utilities Configure the Power Utility Pak command from the menu.

Either of these methods displays a dialog box with the following options.

Utilities

This is a list of all of the Power Utility Pak utilities. Those that are checked are currently loaded into memory. If you would like to free up some memory, simply uncheck the utilities that you want to remove. The Unload All button is a fast way to uncheck all of the boxes. If you later need to use one of the utilities that has been unloaded, it will load itself again. The utilities are not unloaded until you click OK to close the dialog box.

Show the Power Utility Pak Toolbar

Checking this box will display the Power Utility Pak toolbar. Unchecking it will hide the toolbar. The toolbar status is recorded in the [Power Utility Pak] section of your EXCEL5.INI file.

Display enhanced shortcut menus

Checking this box will enable the enhanced shortcut menus. Unchecking it will disable these shortcuts. The enhanced shortcut menu status is recorded in the [Power Utility Pak] section of your EXCEL5.INI file. See Enhanced Shortcut Menus for details.

Restore the original Power Utility Pak toolbar

Checking this box will delete the current Power Utility Pak toolbar (if it exists) and replace it with a fresh copy. You would use this option only if you accidentally deleted the toolbar, or if you made changes to it and didn't like the changes. This option is disabled in the shareware version.

Note: To remove the entire Power Utility Pak add-in from memory, choose the Tools Add-Ins command and uncheck the Power Utility Pak add-in. This will unload POWER.XLA and all of the utilities that are currently loaded. If the Power Utility Pak add-in was opened with the File Open command, it cannot be removed from memory without writing a macro to do so. Therefore, you should always use the Tools Add-Ins command to load the Power Utility Pak.

See Also...

The Utilities

New Worksheet Functions

Enhanced Shortcut Menus


Technical Support

Registered users are entitled to unlimited free technical support for the Power Utility Pak. This assistance is available directly from the author through the following e-mail services:

Internet............ jwalk@crash.cts.com

CompuServe.......... 70363,3014

MCI Mail............ 329-9078

America On-Line..... "Walkenbach"

Questions asked via e-mail are typically answered within 24 hours, often within the same day.

You can also use the U.S. mail service:

JWalk and Associates
P.O. Box 12861
La Jolla, CA 92039-2861

Registered users can also purchase the complete source code for a modest fee. For more information contact JWalk and Associates.

If you discover a bug or problem with the a utility or worksheet function, please report it using one of the options above. The author will fix the problem and send you a corrected version.

We're always looking for suggestions for the next version of the program. So if you have an idea for a useful Excel utility, please let us know.

See Also...

Technical Information

About the Author


Technical Information

The Power Utility Pak was developed with the programming tools that are supplied with every copy of Excel -- the Visual Basic for Applications (VBA) macro language. VBA is an incredibly powerful tool that makes the macro languages in other spreadsheets pale by comparison. The VBA macro language, along with Excel's ability to create and work with add-ins, is what makes the Power Utility Pak possible.

How it works

The POWER.XLA file is the main add-in. It contains the custom menu and shortcut menu definitions, and it controls loading and unloading of the utilities and worksheet functions. The individual utilities are also Excel add-ins, but they have a UTL extension so they won't normally appear in the Browse dialog box when the Browse button is clicked from the Add-Ins dialog box. The exception to this is PUPFUNCS.XLA (the add-in that contains the custom worksheet functions).

The custom toolbar is named "Power Utility Pak 2.0". In the registered version, the custom toolbar is stored in POWER.BAR, and is loaded only when you need to restore the toolbar to its original state (this reduces the size of the add-in). In the shareware version, the toolbar is stored in POWER.XLA.

POWER.XLA loads the utilities on an "as needed" basis so they won't use up memory and system resources if they are not used. POWER.XLA keeps track of which utilities are in memory, and can remove them if you desire. This is done in the Power Utility Pak Configuration  dialog box.

EXCEL5.INI

The Power Utility Pak stores its settings in your EXCEL5.INI file, which is located in your WINDOWS directory. This file has a section called [Power Utility Pak], with the following entries:

Toolbar

Either True or False. Determines whether to display the custom toolbar when the Power Utility Pak is loaded.

EnhancedMenus

Either True or False. Determines whether to display the enhanced shortcut menus.

Job1

The name for the first job tracked by the Time Tracker utility.

Job2

The name for the second job tracked by the Time Tracker utility.

Job3

The name for the third job tracked by the Time Tracker utility.

Job4

The name for the fourth job tracked by the Time Tracker utility.

FunctionMsg

Either True or False. Determines whether a message is displayed when the PUPFUNCS.XLA file is opened.

Software versions used

The Power Utility Pak was developed using the English language version of Microsoft Excel 5.0c running under Microsoft Windows for Workgroups 3.11. There may be some problems if you run the software with an earlier version of Excel, or with a version designed for another language. Also, the Power Utility Pak was not tested with Windows NT or with Windows 95. Because it relies heavily on calls to Windows DLLs, the Power Utility Pak will not work with Excel for Macintosh.

Source code

Registered users may purchase the complete source code for the Power Utility Pak. This consists of all of the XLS workbooks that were compiled into the add-ins (including the SETUP workbook).

Examining this well-documented code is an excellent way to learn VBA programming. With these files, you'll learn exactly how each utility and function was written, and you can adapt these techniques to your own work. The source code files are available from JWalk and Associates for a modest fee.

Using the worksheet functions in your VBA code

To use any of the Power Utility Pak worksheet functions in your VBA code, you must first establish a reference to PUPFUNCS.XLA. To do this, activate a VBA module and choose the Tools References command. Then click the PUPFUNCS.XLA checkbox in the Available References listbox. In most cases, it will be more efficient to include the original VBA function in your code. If you purchase the source files for the Power Utility Pak, you will be able to copy the desired functions to your own VBA module and avoid having to set up a reference to PUPFUNCS.XLA.

Custom projects

JWalk and Associates can develop custom VBA macros and custom worksheet functions for your business. Contact John Walkenbach for more information.

Refer to Technical Support for information on how to contact JWalk and Associates.

See Also...

About the Author


Disclaimers

The Power Utility Pak is available in two versions:

1. Shareware version:

The shareware version is for demonstration purposes only. It may be used for up to 30 days. After 30 days, the user must register the software or delete it from his/her disk. The shareware version does not include all of the functionality of the registered version, and may periodically display messages that remind the user to register.

The shareware version of the Power Utility Pak may be freely distributed, providing that the files are distributed in their original condition, and no files are removed or modified.

For complete details on how to register the Power Utility Pak, please open the REGISTER.XLS workbook. This file is distributed with the Shareware version only.

2. Registered version:

The registered version of the Power Utility Pak is licensed to a single user. However, the user may make a backup copy, and may load the software on more than one machine, provided that the software is not running simultaneously on more than one machine at any given time.

When used on a shared network, a separate copy must be licensed for each user who has access to the software. Significant multi-user discounts are available.

If you develop an application that uses any of the functions in PUPFUNCS.XLA, you may distribute PUPFUNCS.XLA to the end-users of your application. PUPFUNCS.XLA is the only file in the package that may be distributed in this manner.

Limitation of Liability: Because software is inherently complex and may not be completely free of errors, you are advised to verify your work carefully and to make backup copies of your files. By using this software (either the Shareware version or the Registered version), you agree that JWalk and Associates will not be held liable for any type of damages arising out of the use or inability to use this software. In no case shall the liability of JWalk and Associates exceed the amount you paid for the software.

The Power Utility Pak

©1995 by JWalk and Associates Inc.

All rights reserved


The Power Game Pak

The registered version of the Power Utility Pak includes XLS files for three strategy games:

Video Poker

Simulates the video poker games found in casinos. This game is fairly self-explanatory. The objective is to get the best poker hand. After the deal, you can click on the cards to discard.

Yahtzee

Simulates the popular dice game. Help is included in the POKER.XLS file.

BombHunt

A game similar to Microsoft's MineSweeper. Help is included in the BOMBHUNT.XLS file.


About the Author

The Power Utility Pak was developed by John Walkenbach, principal of JWalk and Associates Inc. JWalk and Associates is a small consulting firm based in La Jolla, California.

John has been using spreadsheets since the early days of VisiCalc. He has written approximately 300 articles and reviews, and his work has appeared in publications such as PC World, Windows, PC/Computing, and InfoWorld. In addition, he's the best-selling author of a dozen spreadsheet books, many of which have been translated into several foreign languages. If you develop Excel applications, you may be interested in obtaining a copy of his Excel 5 for Windows Power Programming Techniques book (IDG Books, 1994; ISBN: 1-56884-303-8). This book discusses various topics related to developing spreadsheet applications, with special emphasis on VBA. The book includes a diskette with dozens of useful and informative examples.

JWalk and Associates can develop custom VBA macros and custom worksheet functions for your business. Contact John Walkenbach for more information.

See Also...

Technical Information

Technical Support