Copyright: Copyright 1995, by Spreadsheet Solutions


Contents

Custom Add-In Functions and

Utilities for Excel 5.x

Last Revision: 3/20/95

All Custom Add-Ins from Spreadsheet Solutions can be found in the MS Excel Forum on CompuServe.  Search for Contributor 74002,2373.  You may also use this address for E-mail (on the Internet: 74002.2373@compuserve.com, or see the Registration Form for a Postal Mailing Address).

Installation

About the Author

Custom Function/Utilities Topics

HOLIDAYS!	Over 130 HOLIDAYS! to Enjoy ALL Year, Every Year!

PlayWAV	Play any WAV file on your computer, simply by passing the filename!

WhichDOW	36 functions to determine any logical day of the month!

INISYSDB	Handy INI/System/Memory Information Functions.

PERIODIC	The Periodic Table for Excel with 10 Constants like SpeedOfLight().

XL Conversion+	670 Convenient Conversion Functions!

TalkBack	Handy Voice Proofreader - speaks the numbers back from your Worksheet!

Registration

Registration Form

Entering Your Registration Code

DISCLAIMER OF WARRANTY

This software is distributed "as is", without any warranty as to performance or any other warranties whether expressed or implied.  Because of the many hardware and software environments into which this program may be used, no warranty of fitness for a particular purpose is offered. The user must assume the entire risk of using the program.  Any liability of the seller will be limited exclusively to product replacement or the refund of the registration fee.

Excel is a Copyright of Microsoft Inc.

Copyright 1995, by Spreadsheet Solutions


HOLIDAYS!

Welcome to HOLIDAYS!© for Excel 5.x

Copyright Spreadsheet Solutions, 1995.

HOLIDAYS! is Shareware.  If you continue to use HOLIDAYS! after a 30 day evaluation period, you must register it, or remove it from your computer.

 Function Wizard Category = HOLIDAYS!

Also see the HOLDEMO.XLS file included with HOLIDAYS! for Worksheet and VBA examples.

Now over 130 HOLIDAYS! to Enjoy ALL Year, Every Year!

Calling Custom Functions from VBA

HOLIDAYS! Syntax:

=HolidayFunction(year)

HolidayFunction	The name of the Holiday, e.g. MothersDay().

year	Any valid year (*Religious Holidays: 1990-2020, or #N/A)

When a function returns a date serial number, you must format the cell to display a date!

I'll use Bastille Day to make an example of the HOLIDAYS! functions.

Function Example	Result

=BastilleDay(1994)	7/14/94 (Date serial number)

=BastilleDay(A1)	7/14/95*

*where cell A1 contains the VALUE 1995.

=BastilleDay(NOW())	7/14/94*

*where NOW() evaluates to a date in the year 1994.  (your computer clock is sometime in 1994)

=Bastille.Day(YEAR(C3))	7/14/95*

*where cell C3 contains a date serial number which evaluates to a date in the year 1995.

=BastilleDay(YEAR(C3))	Bastille Day - 7/14/95*

*where cell C3 contains a date serial number which evaluates to a date in the year 1995.

IsHoliday() Function

The =IsHoliday() Function determines whether a date (serial_number) falls on any one of the HOLIDAYS! Functions.  The function passes the year of serial_number, to each of the HOLIDAYS! functions, and compares the result of the holiday dates against serial_number.

*** IMPORTANT ***

IsHoliday() Syntax:

=IsHoliday(serial_number,showtext,showdate)

serial_number	Any date serial number for the years 1900-2??? inclusive*

(Religious Holidays: 1990-2020, or #N/A),

or a formula/reference to a cell, resulting in a date serial number.

showtext	0, 1, or omitted - INTEGER

TRUE if serial_number falls on any HOLIDAYS! Function.

FALSE if no HOLIDAYS!.

2, - VARIANT

TEXT (date/name of Holiday), if serial_number falls on any HOLIDAYS!.

Date Serial Number, if no HOLIDAYS!.

Examples

1/1/1995	=IsHoliday($B1,1,TRUE)	=IsHoliday($B1,0,TRUE)	=IsHoliday($B1,0,TRUE)*1

=+A1+1

1/1/1995	New Years Day: 1/1/95	TRUE	1

2-Jan-95	FALSE	FALSE	0

3-Jan-95	FALSE	FALSE	0

4-Jan-95	FALSE	FALSE	0

5-Jan-95	FALSE	FALSE	0

6-Jan-95	Epiphany: 1/6/95	TRUE	1

7-Jan-95	FALSE	FALSE	0

8-Jan-95	FALSE	FALSE	0

1/1/1995	=IsHoliday($B1,1,FALSE)	=IsHoliday($B1,0,FALSE)	=IsHoliday($B1,0,FALSE)*1

=+A1+1

1/1/1995	New Years Day	TRUE	1

2-Jan-95	FALSE	FALSE	0

3-Jan-95	FALSE	FALSE	0

4-Jan-95	FALSE	FALSE	0

5-Jan-95	FALSE	FALSE	0

6-Jan-95	Epiphany	TRUE	1

7-Jan-95	FALSE	FALSE	0

8-Jan-95	FALSE	FALSE	0

IsHoliday() Function	Result

=IsHoliday(DATE(94,12,25))	TRUE  (Logical)

=IsHoliday(DATE(94,12,25),1)	TRUE  (Logical)

=IsHoliday(DATE(94,12,25),2)	12/25/94 - Christmas Day (Text)

=IsHoliday(DATE(94,12,24))	FALSE

=IsHoliday(DATE(94,12,24),1)	FALSE

=IsHoliday(DATE(94,12,24),2)	12/24/94

=IsHoliday(A1,1)	FALSE*

*where cell A1 contains the date 12/24/94.

=IsHoliday(NOW(),2)	12/25/94 - Christmas Day*

*where NOW() evaluates to 12/25/94.

=IsHoliday(C3+5)	TRUE*

*where cell C3 contains the date 6/30/94.  [6/30/94+5 evaluates to 7/4/94]

=IsHoliday(NOW()+5,2)	7/4/94 - Independence Day*

*where NOW() evaluates to 6/30/94.  [6/30/94+5 evaluates to 7/4/94]

Copyright 1995, by Spreadsheet Solutions


PlayWAV

Welcome to PlayWAV© for Excel 5.x

Copyright Spreadsheet Solutions, 1995.

PlayWAV is Shareware.  If you continue to use PlayWAV after a 30 day evaluation period, you must register it, or remove it from your computer.

 Function Wizard Category = PlayWAV

Also see the WAVDEMO.XLS file included with PlayWAV for Worksheet and VBA examples.

Calling Custom Functions from VBA

PlayWAV Example

Syntax:

=PlayWAV("Filename")

Filename	Any valid WAV filename on your system (including the extension).

If the file IS NOT in your \WINDOWS directory, you MUST

include the Full Path.

Example	=PlayWAV("c:\sounds\ring1.wav")

Copyright 1995, by Spreadsheet Solutions


WhichDOW

Welcome to WhichDOW© for Excel 5.x

Copyright Spreadsheet Solutions, 1995.

WhichDOW is Shareware.  If you continue to use WhichDOW after a 30 day evaluation period, you must register it, or remove it from your computer.

 Function Wizard Category = WhichDOW

Also see the DOWDEMO.XLS file included with WhichDOW for Worksheet and VBA examples.

Do your Excel Models depend on Dates, and looking up the first Monday of every month is getting tiresome? WHICHDOW functions make it easy!  All the functions are the combinations of 12 words, First, Second, Third, Fourth, Last, and Sun, Mon, Tue, Wed, Thu, Fri, and Sat.  Just put when before the day, and a period in between! e.g. ThirdSun(94,1) will return the Third Sunday in January 1994.

WhichDOW now also makes available the heart of all the other functions: WdDOWMthYr() (See below).

Calling Custom Functions from VBA

WhichDOW Functions

Syntax

=LastFri(year,month)

year	Any valid year, or formula that results in a valid year.

month	Any valid month, or formula that results in a valid month (1-12).

WHICHDOW Example:

=ThirdThu(94,3)	Result: Third Thu in March 1994.

Where the cell A1 contains 1994 (year), and B1 contains 4 (month-April), then:

=FirstMon(A1,B1) 	Result: First Mon in April 1994.

Or, you may embed DATE type functions as arguments like: =LastMon(YEAR(NOW()),5).

All these functions are just an easier way to implement the WdDOWMthYr() function, where the variables Wd, and DOW, are different sets of constants, and Mth and Yr are simply passed on. So, for example, the FirstSun() function calls the  WdDOWMthYr function with Wd=1, and DOW=1, and passes on the month and year you specify.  WdDOWMthYr does the work, and passes it back to FirstSun.

WdDOWMthYr() Function

Returns a date that corresponds to a specified day in a month. For example, LastSat(1995,5), and WdDOWMthYr(5,7,5,1995) both return the same date (5=Last, 7=Sat).

Syntax

=WdDOWMthYr(Wd,DOW,Month,Year)

Wd: 	An integer between 1 and 5 that corresonds to the week of interest. For

example: 1=First, 2=Second, 3=Third, 4=Fourth, and 5=Last.  Depending on the

month/year, Fourth and Last may return the SAME date.

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

Monday is 2, and so on (same as the WEEKDAY() function).

Month: 	An integer between 1 and 12 that corresponds to the month. January is 1,

February is 2, etc.

Year: 	An integer that corresponds to the year.

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

WdDOWMthYr Examples

WdDOWMthYr(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.

WdDOWMthYr(5,2,4,1995) returns the date serial number for the Last Monday in April, 1995.  Since this month has four Mondays, the same result would be returned by WdDOWMthYr(4,2,4,1995).

Calling WhichDOW functions from your VBA Code:

In order to use WhichDOW in your VBA Code, you must also specify it as a Reference.

Not too tough to do.

   Open WHICHDOW .XLA if it's not already open.

   Select your Module sheet where you want to add a WhichDOW function.

   Select Tools|References... from the menu, and then select WHICHDOW .XLA

   Select OK

You're ready to go.

See Also: DOWDEMO.XLS which should be included in your WhichDOW archive.

WHICHDOW Function Listing

NOTE: Depending on the month/year, Fourth???() and Last???() may return the SAME date.

=WdDOWMthYr(Wd,DOW,Month,Year)

=FirstSun(year,month)

=FirstMon(year,month)

=FirstTue(year,month)

=FirstWed(year,month)

=FirstThu(year,month)

=FirstFri(year,month)

=FirstSat(year,month)

=SecondSun(year,month)

=SecondMon(year,month)

=SecondTue(year,month)

=SecondWed(year,month)

=SecondThu(year,month)

=SecondFri(year,month)

=SecondSat(year,month)

=ThirdSun(year,month)

=ThirdMon(year,month)

=ThirdTue(year,month)

=ThirdWed(year,month)

=ThirdThu(year,month)

=ThirdFri(year,month)

=ThirdSat(year,month)

=FourthSun(year,month)

=FourthMon(year,month)

=FourthTue(year,month)

=FourthWed(year,month)

=FourthThu(year,month)

=FourthFri(year,month)

=FourthSat(year,month)

=LastSun(year,month)

=LastMon(year,month)

=LastTue(year,month)

=LastWed(year,month)

=LastThu(year,month)

=LastFri(year,month)

=LastSat(year,month)

Copyright 1995, by Spreadsheet Solutions


PERIODIC

Welcome to PERIODIC© for Excel 5.x

Copyright Spreadsheet Solutions, 1995.

PERIODIC is Shareware.  If you continue to use PERIODIC after a 30 day evaluation period, you must register it, or remove it from your computer.

 Function Wizard Category = PERIODIC

Also see the PERDEMO.XLS file included with PERIODIC for Worksheet and VBA examples.

Calling Custom Functions from VBA

PERIODIC Function Syntax:

All but one function have the same syntax.

=AtomicNumber(Symbol) is a function which returns the Atomic_Number (required for the remaining functions) when you provide the element symbol, as text. This is useful when you know the element's symbol, but not it's number.

Symbol	any atomic symbol (H, He, Li, Be, B, C, N, O, etc.) as text.

e.g. =AtomicNumber("He") returns the value 2.

For the Remaining Functions:

Atomic_Number	any atomic number, a value from 1 - 110.

Display_Units	FALSE or omitted* - returns the function's value.

(*If omitted, you MUST omit Format_Text as well).

TRUE** - returns the value as TEXT, with its units as suffix.

(**When TRUE, you MUST specify the Format_Text).

Format_Text	is a number format, in text form (e.g. "0.000E+00").

(see the Excel Documentation for FORMAT() or TEXT()).

When Display_Units is TRUE, you may NOT use the result in a formula (it's TEXT!).

e.g.  =AtomicWeight(3,TRUE,"0.0000") [Lithium] returns TEXT as: 13.1000 cm3/mol,

while =AtomicWeight(3) returns 13.1 as a NUMBER (format depends on YOUR cell format).

PERIODIC FUNCTIONS:	UNITS:

=IonPotential1(Atomic_Number,Display_Units,Format_Text)	V

=IonPotential2(Atomic_Number,Display_Units,Format_Text)	V

=IonPotential3(Atomic_Number,Display_Units,Format_Text)	V

=AtomicGroup(Atomic_Number)	NA

=AtomicNumber(Symbol)	NA

=AtomicRadius(Atomic_Number,Display_Units,Format_Text)	A

=AtomicSymbol(Atomic_Number)	NA

=AtomicVolume(Atomic_Number,Display_Units,Format_Text)	cm3/mol

=AtomicWeight(Atomic_Number,Display_Units,Format_Text)	based on carbon-12

=BoilingPoint(Atomic_Number,Display_Units,Format_Text)	Deg C

=CovalentRadius(Atomic_Number,Display_Units,Format_Text)	A

=CrystalStructure(Atomic_Number)	NA

=Density(Atomic_Number,Display_Units,Format_Text)	g/cm3

=DiscoveryDate(Atomic_Number)	NA

=ElectricalConductivity(Atomic_Number,Display_Units,Format_Text)	10^6/cm ohm

=Electronegativity(Atomic_Number)	NA

=ElementName(Atomic_Number)	NA

=FillingOrbital(Atomic_Number)	NA

=HeatOfFusion(Atomic_Number,Display_Units,Format_Text)	kJ/mol

=HeatVaporization(Atomic_Number,Display_Units,Format_Text)	kJ/mol

=MeltingPoint(Atomic_Number,Display_Units,Format_Text)	Deg. C

=OxidationState(Atomic_Number)	NA

=Shells(Atomic_Number)	NA

=SpecificHeat(Atomic_Number,Display_Units,Format_Text)	J/gK

=ThermalConductivity(Atomic_Number,Display_Units,Format_Text)	W/cmK

When a Function returns #VALUE!

There are many combinations of function and element whose result is either unknown, or doesn't apply.  An obvious example of this would be the function  =ElectricalConductivity(1).  Element 1 (Hydrogen) has a conductivity which is indeterminate, and returns #VALUE!, while =Electrical.Conductivity(26) (Lead) returns 0.0993.

If one of my functions returns a #VALUE! error, and you can document a value that's been determined for the element, please feel free to let me know, as I'm always interested in keeping my programs up to date.

PERIODIC CONSTANTS:

Constant Arguments are Optional.

To simply return the value of the Constant, omit both arguments: as in =AvagadrosNumber().

To return the units use: =AvagadrosNumber(TRUE,"0.00E+00").

PERIODIC CONSTANTS:	UNITS:

=AvagadrosNumber(Display_Units,Format_Text)	mol/mole

=SpeedOfLight(Display_Units,Format_Text)	m/sec

=ElectronRestMass(Display_Units,Format_Text)	kg

=ProtonRestMass(Display_Units,Format_Text)	kg

=NeutronRestMass(Display_Units,Format_Text)	kg

=PlancksConstant(Display_Units,Format_Text)	J*s

=BoltzmannsConstant(Display_Units,Format_Text)	J/K

=GravitationalAcceleration(Display_Units,Format_Text)	m/sec^2

=GravitationalConstant(Display_Units,Format_Text)	N*m^2/kg^2

=FaradayConstant(Display_Units,Format_Text)	coul/eq

Copyright 1995, by Spreadsheet Solutions


Installation

INSTALLING Custom Functions

If you received or downloaded an archive, copy the .ZIP to this subdirectory, and "unzip" it there.

I recommend a subdirectory "under" your Excel directory.  Normally, during its install, Excel will create directories something like ... C:\EXCEL\LIBRARY\CROSSTAB, as well as others.  I recommend creating a subdirectory named C:\EXCEL\LIBRARY\SSADDINS, and placing ALL the files there.

IMPORTANT -  In order to use the online help, you must copy, or move the file SSADDINS.HLP to your Excel Directory.

USING Custom Functions

You can use the custom functions by simply Opening the .XLA file from within Excel, or, double click on it's filename in File Manager to load it, and Excel.

Registered users will Avoid the Opening Shareware Dialog Box.

To enter a Custom Function to your worksheet, select [Insert/Function...] from the Excel menu, and then select [HOLIDAYS!], or [User Defined], etc. as the [Function Category:] for a list of the Custom Functions (in the [Paste Function] list).

Select a Custom function, and select Next.  Enter your arguments, or references, and press select Finish.  Change the Number Format of the cell if necessary.  If you know the syntax of the function, you can more easily just type the function, like any other function, =SUM(), =GetINIString(), =PlayWAV(), or =ArborDay().

HINTS:

Copy any ???????.XLA to your XLSTART subdirectory to load it every time you use Excel.

e.g. C:\EXCEL\XLSTART\HOLIDAYS.XLA

If you open a document containing a Custom Function, and the corresponding .XLA is NOT OPEN, a message indicating that the function is "too complex" will appear.  To propery recalculate the functions, you MUST open the corresponding .XLA.

Copyright 1995, by Spreadsheet Solutions


PlayWAV Example
PlayWAV Examples

The following examples play the TADA.WAV file in your \WINDOWS directory.

x returns 1 if the file was played, and 0 if not.

Sub TestWAV()

Dim x As Integer

    'play the file

    x = PlayWAV("tada.wav")

End Sub

Sub TestWAV()

Dim Fname As String

Dim x As Integer

    'somewhere along the lines FName gets a filename...

    FName = "C:\WAVS\TADA.WAV"

    'play the file

    x = PlayWAV(Fname)

End Sub

Copyright 1995, by Spreadsheet Solutions


TalkBack

Welcome to TalkBack© - The WAV Number Proofreader for Excel 5.x

Copyright Spreadsheet Solutions, 1995.

TalkBack is Shareware.  If you continue to use TalkBack after a 30 day evaluation period, you must register it, or remove it from your computer.

NOTE: A sound card is NOT REQUIRED, (but recommended) since TalkBack can use the PC Speaker Driver (available from Microsoft, or from many BBS's).  However, you will need one or the other.  TalkBack is also available with an 8-Bit WAV file if you have an older sound card.

Proofread your worksheets with a new friend.  With TalkBack you no longer need a co-worker to read off a list of numbers for you to proof them. TalkBack does it all for you!  Well, maybe I went too far with that last statement, however I do think you'll find it an invaluable tool.  While there certainly are more sophisticated programs that will read numbers (and text) to you in "correct" english, oftentimes they're complicated, very expensive, and not directly integrated with Excel, making them difficult to use.  TalkBack is none of these!  Directly integrated with Excel (written in VBA, and accessible from the "Tools" menu), TalkBack is both inexpensive, and easy to use.  Simply select the range you want to proof, start TalkBack, and you're on your way!

Using TalkBack

Simply select the range you want read back to you, and then select either Default TalkBack, or

TalkBack... from the Excel "Tools" menu.  These menu items are only available in the "Worksheet" Tools menu, as TalkBack will only work when the active sheet is a Worksheet (as opposed to a Chart, Dialog, or Macro sheet).  A shortcut key combination has also been assigned so you can start Default TalkBack with SHIFT+CTRL+T instead of using the menu.

If you select Default TalkBack, (or SHIFT+CTRL+T) TalkBack will begin speaking immediately, using the defaults as described next.

If you select TalkBack..., TalkBack will display a dialog with five default options. These options will be saved to your EXCEL5.INI file so they can be "remembered" between Excel sessions making it unnecessary reset them every time you use TalkBack.  If you uninstall TalkBack, you should remove these entries from your INI file which is located in you \WINDOWS directory.

Say Columns/Rows (check box)

Enable this box to have TalkBack say the Column/Row numbers of the data whenever it

starts a new column.

Error if Text (check box)

Enable this box to have TalkBack say "Invalid Entry", if the cell contains text.

If Disabled, Talkback will skip to the next cell.

Error if Blank (check box)

Enable this box to have TalkBack say "Blank", if the cell is blank.

If Disabled, Talkback will skip to the next cell.

Delay (msecs) (edit box)

Enter the number of milliseconds TalkBack should wait between speaking the numbers

in each cell of the selected range.  The default is 1000 (0.5 seconds).

Max Decimals (edit box)

Enter the maximum number of decimal places TalkBack is allowed to speak.

Often times you'll format a cell whose formula results in a large number of decimal

places, so you don't see them all.  Without the Max Decimal option, TalkBack would

speak ALL the decimal places of the result, even though you don't see them because

of the formatting.  The Max Decimals option solves this problem by using the

FORMAT(result,"0.*") function on the result before speaking it.

NOTE: This option does not force TalkBack to speak the number of decimals, but is

only the maximum it will speak.  The FORMAT function rounds when it converts, so the

result TalkBack speaks is also rounded to the Max Decimals.  So, if you have Max

Decimals set to 3, and a cell contains 87.1 then TalkBack will say:

Eight, Seven, Point, One, and NOT: Eight, Seven, Point, One, Zero, Zero.

Similarly, 87.17274 would be rounded to 87.173 and spoken as:

Eight, Seven, Point, One, Seven, Three.

When you're set, simply select the Start Talking button, and TalkBack will take it from there, reading back to you all the numbers in the selected range.  Your settings will be saved, so you can then use the Default TalkBack menu item, or SHIFT+CTRL+T in the future, and avoid the dialog.

Simply press ESC at any time if you wish to Pause or End TalkBack before it's finished.  If you later press the Continue button, TalkBack will pick up exactly where it left off, even if it's in the middle of speaking the contents of a cell.

How TalkBack Speaks/Limitations

As I mentioned earlier, TalkBack is not as sophisticated as some other commercial products (but then it doesn't carry their pricetag either).  And there's a reason for this.  MEMORY!  I wanted something small and easily accessible that wouldn't drain my system memory (many commercial products have big DLL's which use large amounts of memory).  Many of the commercial products can use "correct" english when reading numbers (and/or text) from an application, and truely "analyzes" the data it receives, before speaking it.

For instance, a commercial product might read 435.1 as "Four Hundred Thirty Five Point One".  TalkBack on the other hand, will read this same number as "Four Three Five Point One".

In addition, TalkBack will only read numbers, and does not support reading text as some commercial products do.  When TalkBack comes to a blank cell, or one with text, it can say "Blank", "Invalid Entry", or simply skip it and proceed to the next cell.

Speaking of proceeding to the next cell, TalkBack reads all the numbers in the first column, before it moves to the second column (analogous to "Down and then Over" when Printing multiple pages).

Installing TalkBack

TalkBack requires two files:

TALKBACK.XLA	(16-Bit)

TALKBACK.WAV	(16-Bit)

OR

TALKBK08.XLA	(8-Bit)

TALKBK08.WAV	(8-Bit)

NOTE: These files are NOT interchangeable, and must be used together as shown above.

The following assumes you're using the 16-Bit version files.

TALKBACK.WAV MUST be placed in your main EXCEL directory, (e.g. c:\excel\talkback.wav, or c:\msoffice\excel5\talkback.wav) however, the TALKBACK.XLA add-in can be placed in any directory you wish.

If the WAV file is not found in your EXCEL directory when you load TalkBack, it will display a message to that effect (indicating where it should be), and will not allow you to proceed using TalkBack.  You must Exit Excel, move the file to the directory specified in the message, and then restart Excel in order to use TalkBack.

Loading the TalkBack Add-In

In order to use TalkBack it must be loaded.  You can do this in different ways depending on how often you use it.

To make it always available...

Specify it as an Add-In (Tools|Add-Ins... then "Browse" for TALKBACK.XLA)

OR

Place TALKBACK.XLA in your XLSTART directory.

To use it for a single session...

Simply open it as you would any other .XL? file.

You Don't Like My Voice?

My mother would disagree, but I can understand that you might not, so registered TalkBack

users can Customize TalkBack to speak in their own voice, or the voice of anyone they choose.

Simply open the Windows "Sound Recorder" application, and record the following words, in the

following order:  "Row, Column, Blank, Point, Invalid Entry, Minus, Zero, One, Two, Three, Four,

Five, Six, Seven, Eight, Nine".  Save the file in WAV format, and send it along with a $20

processing fee to the normal TalkBack registration address.  I will optimize the WAV file (remove

blank space to keep it small as possible), determine the start/end points of each word, and create

a totally custom version of TalkBack just for your voice file!  Be sure to include your return

address, and please allow two weeks for processing.

Copyright 1995, by Spreadsheet Solutions


Registration
Registration Form

Your registrations allow me to improve the Add-Ins, and write other programs for you to enjoy, at a reasonable cost.  Registration has many other benefits, including: technical support via mail and/or Compuserve, reduced prices on other Shareware from Spreadsheet Solutions, and more!  With registration you'll receive a code to disable the Shareware dialogs.

To register an Add-In, select the Print button above, to print this topic. Enter your information, and send it to (payable to Spreadsheet Solutions in US$):

P. Scott Antony

c/o Spreadsheet Solutions

P.O. Box 11047

Shorewood WI,  53211

Registration Information:

First Name:	MI:	Last Name:

|__|__|__|__|__|__|__|__|__|__|__|__|	|__|	|__|__|__|__|__|__|__|__|__|__|__|__|__|__|

Company:

|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|

Address:

|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|

City:

|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|__|

State:|__|__|    Zip:|__|__|__|__|__|-|__|__|__|__|    CIS E-Mail #|__|__|__|__|__|,|__|__|__|__|

Other Internet Address ____________________________________________

____ Please send me information about a multiple site licence.

Shareware Demo Disk Only	_____  x  $5.00 =	$___________

Any One 	_____	x$10.00 =	$___________

Any Two (Save $5)	_____	x$15.00 =	$___________

Any Three (Save $5)	_____	x$25.00 =	$___________

Any Four (Save $10)	_____	x$30.00 =	$___________

Any Five (Save $15)	_____	x$35.00 =	$___________

Any Six (Save $20)	_____	x$40.00 =	$___________

All Seven (Save $20)	_____	x$50.00 =	$___________

Select the Add-In(s) to register...

Registered Version(s) of HOLIDAYS! 	_____

Registered Version(s) of PlayWAV 	_____

Registered Version(s) of PERIODIC 	_____

Registered Version(s) of WhichDOW 	_____

Registered Version(s) of TalkBack 	_____

Registered Version(s) of INISYSDB 	_____

Registered Version(s) of XL Conversion+	_____

Wisconsin Residents add State/County Sales Tax	$___________

Total Enclosed US$	$___________

I got the Shareware versions from :_______________________________________________________

!!! Thanks for your Support !!!

P. Scott Antony/Spreadsheet Solutions

Copyright 1995, by Spreadsheet Solutions


Entering Your Registration Code

In order to try and keep my custom add-ins as small as possible, I have created a separate application (add-in) for users to register any/all custom Add-Ins by Spreadsheet Solutions.  In this way I don't have to include the registration dialog and code in the add-ins themselves, since you'll only use it once, and would otherwise be wasting your memory.

The application's name is SSREGSTR.XLA, and it's included with all custom Add-Ins by

Spreadsheet Solutions.

To register an add-in, simply start Excel, and open the SSREGSTR.XLA file.

Select the add-in you're registering from the list, and then enter your registration information

EXACTLY as it appears in the confirmation document I've returned to you.

Select the Register button, and you're all set!

At last!  Peace of Mind, and No More Nag Screens!

Copyright 1995, by Spreadsheet Solutions


About the Author

Starting early on with LOTUS 1.1a, Spreadsheet Solutions has been creating Custom spreadsheet applications and tools in 1-2-3, and Excel, on an independent contractor basis, for over 9 years now. Boy how time flies!  The Add-Ins described here are but a very small sampling of the all work done over the years (I have plans to convert more of the work to shareware, and have a whole new horizon with VBA).  So, if you need a function, or tool, to make your work with Excel easier, please let me know.  As well as Computers, I have a backround in Engineering (covering: statics, dynamics, fluid mechanics, circuits, statistics, physics, CAD/CAM/CNC etc.) and am comfortable with the high power math, so come on, try to intimidate me <G>.  I'm also familiar with basic accounting and estimating practices, having written many custom applications in these areas.

Rates to create custom functions/tools/applications are negotiable, or if I believe your request has wide enough appeal, I would be willing to create it as shareware, give you a free registration, and pay you $50 for your idea, whether it sells or not!

E-mail your requests/suggestions to: 74002.2373@compuserve.com, (or see the Registration Form for a Postal Mailing Address).

Copyright 1995, by Spreadsheet Solutions


INISYSDB

Welcome to INISYSDB© for Excel 5.x

Copyright Spreadsheet Solutions, 1995.

INISYSDB is Shareware.  If you continue to use INISYSDB after a 30 day evaluation period, you must register it, or remove it from your computer.

 Function Wizard Category = INISYSDB

Also see the INISDEMO.XLS file included with INISYSDB for Worksheet and VBA examples.

Calling Custom Functions from VBA

INISYSDB contains 22 usefull File and System Information functions which are broken up into four groups.

File/Dir Functions	INI Functions	Memory Functions	System Functions

FileDate()	WriteINI()	FreeSystem()	XLUser()

FileSize()	GetINIInt()	FreeGDI()	CoProc()

FileExists()	GetINIString()	FreeUser()	DriveTypes()

WinDir()	ExtAssociation()	FreeBelowEMS()	KeyBoardType()

SysDir()	FreeAboveEMS()	Win386Mode()

ExcelDir()	WinProtectedMode()

WinCPU()

Copyright 1995, by Spreadsheet Solutions


Function Wizard Category

To display the Function Wizard, select [Insert|Function...] from the Excel menu.  Then select the [Function Category:] for the Add-In (as listed above) to display a list of the all the Custom Functions (displayed in the [Paste Function] list).  Each Add-In from Spreadsheet Solutions has a unique Function Category, however, if you're loading several Add-Ins, I've noticed that they'll sometimes all group up into one, although each of the Categories is listed individually (just a hint).

Copyright 1995, by Spreadsheet Solutions


Calling Functions from VBA
Calling Custom Functions from VBA

In order to use the Custom Functions in your VBA Code, you must specify its XLA as a Reference.

Not too tough to do.

   Open the .XLA if it's not already open.

   Select your Module sheet where you want to add a Custom Function.

   Select Tools|References... from the menu, and then select the .XLA

   Select OK

You're ready to go.

Copyright 1995, by Spreadsheet Solutions


INISYSDB File/Dir Functions

FileDate() function

Returns a serial number of the Date/Time the specified file was last modified.  You must format the cell containing this function (or result in VBA) for the date/time format your prefer.

Syntax

=FileDate(Filename)

Filename	Any valid filename, including the FULL path.

Example	=FileDate("c:\windows\win.ini") might return: 3/16/95 4:30:00 PM.

FileSize() function

Returns in Bytes, the Size of the specified file.

Syntax

=FileSize(Filename)

Filename	Any valid filename, including the FULL path.

Example:  	=FileSize("c:\windows\win.ini") might return: 16,345.

FileExists() function

Returns TRUE/FALSE whether the specified file exists.

Syntax

=FileExists(Filename)

Filename	Any valid filename, including the FULL path.

Example:  	=FileExists("c:\windows\win.ini") might return: TRUE.

WinDir() function

Returns the full path for the Windows Directory on your system.

Syntax

=WinDir()	(No arguments)

Example:  	=WinDir() might return: c:\windows\.

SysDir() function

Returns the full path for the Windows System Directory on your system.

Syntax

=SysDir()	(No arguments)

Example:  	=SysDir() might return: c:\windows\system\.

ExcelDir() function

Returns the full path for the Excel Directory on your system.

Syntax

=ExcelDir()	(No arguments)

Example:  	=ExcelDir() might return: c:\msoffice\excel5\.

Copyright 1995, by Spreadsheet Solutions


INISYSDB INI Functions

WriteINI() function

Writes the Application, Parameter and Value to an INI file.

Syntax

=WriteINI(Filename,Application,Parameter,Value)

Filename	Any valid filename, including the FULL path*.

(*Omitting Filename assumes WIN.INI).

Application	The section of the INI file you're interested in.

INI file sections appear in square brackets within the INI file ([windows]).

Parameter	The entry within a section that you're interested in. For example,

an entry in the [windows] section of WIN.INI is Run.

Value	The value to assign the Parameter. You Must pass this as a String!

Example:

=WriteINI(WinDir()&"MYAPP.INI","Defaults","Zoom","50") will write to C:\WINDOWS\MYAPP.INI, and the contents would look like:

[Defaults]	(the Application)

Zoom=50	(Parameter=Value)

Note that you DON'T include the square brackets for the Application argument.  Keep this example in mind as you use the GetINI functions.

NOTE: If the Filename you specify does not exist, WriteINI will automatically create it, so be judicious in its use.  If you pass the contents of a cell which varies, as say the Application parameter, you could end up creating a very large file.  Be careful not to omit the filename, unless you really want to write to your WIN.INI file!  If you pass the result of a cell as the Value, ($A$1) WriteINI will rewrite the data every time you recalc (but only if the cells result has changed).

For example, a cell in your worksheet might contain:

=WriteINI("C:\MYAPP\MYAPP.INI","West Coast","Total",WCTotal) where WCTotal is a Range Name in the same worksheet containing an =SUM() formula which totals up the 5 divisions of your west coast operation (note that WCTotal is NOT enclosed in quotes since it's a reference, and could have just as easily been $A$1).  If you change the value for one of the divisions, and recalculate (thus altering the SUM), WriteINI will do its thing and write the data to the INI file.  If you now change the values for the East Coast and recalculate, the WriteINI formula for the West Coast will NOT write the data since it's not changed.

GetINIString() function

Returns the String value for a specified parameter in an INI file.

Syntax

=GetINIString(Filename,Application,Parameter)

Filename	Any valid filename, including the FULL path*.

(*Omitting Filename assumes WIN.INI).

Application	The section of the INI file you're interested in. INI file sections

appear in square brackets within the INI file ([windows]).

Parameter	The entry within a section that you're interested in. For example,

an entry in the [windows] section of WIN.INI is Run.

Example:

=GetINIString(WinDir()&"EXCEL5.INI","Microsoft Excel","Font") might return: Arial,10.

GetINIInt() function

Returns the Integer value for a specified parameter in an INI file.

Syntax

=GetINIInt(Filename,Application,Parameter)

Filename	Any valid filename, including the FULL path*.

(*Omitting Filename assumes WIN.INI).

Application	The section of the INI file you're interested in. INI file sections

appear in square brackets within the INI file ([windows]).

Parameter	The entry within a section that you're interested in. For example,

an entry in the [windows] section of WIN.INI is Run.

Example:

=GetINIInt(WinDir()&"EXCEL5.INI","Microsoft Excel","DefSheets") might return: 2 (the number of sheets included by default, in any New Worksheet your create).

ExtAssociation() function

Returns the full path for the program Associated with the specified extension ni File Manager.

NOTE: If the Extension you pass is not Associated with a Program, ExtAssociation will return: #VALUE.

Syntax

=ExtAssociation(Extension)

Extension	Any valid File Extension.

Example:  	=ExtAssociation("BMP") might return: c:\windows\pbrush.exe

(the Windows PaintBrush program)

Copyright 1995, by Spreadsheet Solutions


INISYSDB Memory Functions

FreeSystem() function

Returns the percentage of Free System memory available.

Syntax

=FreeSystem()	(No arguments)

Example:  	=FreeSystem() might return: 0.73 (or 73%).

FreeGDI() function

Returns the percentage of Free GDI memory available.

Syntax

=FreeGDI()	(No arguments)

Example:  	=FreeGDI() might return: 0.53 (or 53%).

FreeUser() function

Returns the percentage of Free GDI memory available.

Syntax

=FreeUser()	(No arguments)

Example:  	=FreeUser() might return: 0.62 (or 62%).

FreeBelowEMS() function

Returns the amount of Free memory below the EMS Bank Line.

Syntax

=FreeBelowEMS()	(No arguments)

Example:  	=FreeBelowEMS() might return: 15,422,016.

FreeAboveEMS() function

Returns the amount of Free memory above the EMS Bank Line.

Syntax

=FreeAboveEMS()	(No arguments)

Example:  	=FreeAboveEMS() might return: 10,762,412.

Copyright 1995, by Spreadsheet Solutions


INISYSDB System Functions

XLUser() function

Returns the name of the current user based on the contents of the Options dialog box ("General").

Syntax

=XLUser()	(No arguments)

Example:  	=XLUser() might return: John Doe.

HINT:

=LEFT(XLUser(),FIND(" ",XLUser())-1) returns the first name of the user, and =RIGHT(XLUser(),LEN(XLUser())-FIND(" ",XLUser())) returns the last name of the user.

CoProc() function

Returns TRUE/FALSE whether the workstation has a Math Coprocessor installed.

Syntax

=CoProc()	(No arguments)

Example:  	=CoProc() might return: TRUE.

DriveTypes() function

Returns TRUE/FALSE whether the workstation has a Math Coprocessor installed.

Syntax

=DriveTypes(Drive)

Drive	Any valid Drive Number or Letter.

If you pass an integer to DriveTypes(), it will return an integer in return.  If you pass a string, likewise it will return a string.

Examples:  	=DriveTypes(0) will return: 2 if drive 0 is a Floppy drive.

=DriveTypes(2) will return: 3 if drive 2 is a Hard drive.

=DriveTypes(3) will return: 4 if drive 3 is a Remote drive.

  	=DriveTypes("A") will return: "A:= Floppy Drive" if drive A is a Floppy drive.

=DriveTypes("C") will return: "C:= Hard Drive" if drive A is a Hard drive.

=DriveTypes("D") will return: "D:= Remote Drive" if drive A is a Remote drive.

(Remote = CD-ROM, Network, etc.)

KeyBoardType() function

Returns a string containing the current Keyboard Type.

Syntax

=KeyBoardType()	(No arguments)

Example:  	=KeyBoardType() might return: Enhanced IBM type 101- or 102-key.

The possible return values are:

    83-key

    102-key Olivetti type

    84-key AT type

    Enhanced IBM type 101- or 102-key

    Nokia 1050

    Nokia 9140

    Japanese type

    Unknown

Win386Mode() function

Returns TRUE/FALSE whether Windows is running in 386 Enhanced Mode.

Syntax

=Win386Mode()	(No arguments)

Example:  	=Win386Mode() might return: TRUE.

WinProtectedMode() function

Returns TRUE/FALSE whether Windows is running in Protected Mode.

Syntax

=WinProtectedMode()	(No arguments)

Example:  	=WinProtectedMode() might return: TRUE.

WinCPU() function

Returns a value signifying the CPU installed in the workstation.

Syntax

=WinCPU()	(No arguments)

Example:  	=WinCPU() might return: 80486 (as a value).

Copyright 1995, by Spreadsheet Solutions


Geometry
Geometric!

Welcome to Geometric!© for Excel 5.x

Copyright Spreadsheet Solutions, 1995.

Geometric! is Shareware.  If you continue to use Geometric! after a 30 day evaluation period, you must register it, or remove it from your computer.

 Function Wizard Category = Geometry

Also see the GEODEMO.XLS file included with Geometric! Worksheet and VBA examples.

Calling Custom Functions from VBA

Copyright 1995, by Spreadsheet Solutions


XL Conversion+

Welcome to XL Conversion+© for Excel 5.x

Copyright Spreadsheet Solutions, 1995.

XL Conversion+ is Shareware.  If you continue to use XL Conversion+ after a 30 day evaluation period, you must register it, or remove it from your computer.

 Function Wizard Category = XL Conversion+

Also see the CVTDEMO.XLS file included with XL Conversion+ Worksheet and VBA examples.

Calling Custom Functions from VBA

XL Conversion+ contains 670 Convenient Conversion Functions to convert everything from, Feet -> Meters, to Watts -> Horsepower.  Due to the vast number of functions, I will not list them all here. Instead, really the best way to use XL Conversion+ is with the Function Wizard.

IMPORTANT: While I like to think that all my custom functions are convenient, in the context of this Add-In, the word convenient means something else.  Specifically it means that XL Conversion+ uses

"convenient multipliers".  This means that if you need your data accurate past 4 significant digits, test the functions carefully.  Most multipliers are integers, or normally have less than 4 significant digits, and will be just fine, however not all are that easy, and may be rounded to the 4th significant digit.  Note: this is not to be confused with 4 decimal places, as a number can have its 4th significant digit in the 7th decimal place.

Using the XL Conversion+ Functions

Start the Function Wizard, and select XL Conversion+ as the Function Category.  The Paste list will display all the XL Conversion+ Functions in alphabetical order by what it solves for.  For example, Centimeters? Functions will return the number of Centimeters given some other value.  The "?" denotes that if there is more than one XL Conversion+ function that solves for a variable, the first will simply use the name, while succeeding functions will be incremented by 1, resulting in the picture below.  Click on any function in the Paste list, and then press C on the keyboard to skip to the C's.  Then continue to press C until you get to Centimeters (with 670 functions, I recommend this approach instead of the scroll bars, or arrow keys, which could take forever).

As you can see, Centimeters can be solved 7 ways.  For each function, you must pass your value in the specified units.

Centimeters(Feet)

Centimeters1(Inches)

Centimeters2(Kilometers)

Centimeters3(Meters)

Centimeters4(Miles)

Centimeters5(Millimeters)

Centimeters6(Yards)

ALL functions require a single argument, which is clearly defined, as you see above.

If somehow there isn't a variation of a function in the units you're using, try combining two or more.

For example: If you're a scientist, and need to report your data in Absolute C (degrees) and you're measuring in Fahrenheit, you'd need to combine two XL Conversion+ functions; AbsTempC(TempC), and TempC(TempF).  Substituting TempC(TempF) for TempC, we get:

=AbsTempC(TempC(TempF))

where TempF is your Fahrenheit reading, which is converted to Celcius, since that's units that AbsTempC() requires (there is no AbsTempC(TempF), I mean I had to stop somewhere!).

If you're measuring the temperature of water, and it's boiling (212°F), then the function would return 373° (Absolute C).  [212°F=100°C+273=373° Absolute C]

Copyright 1995, by Spreadsheet Solutions