Last updated: **1999.08.26**

**Introduction
**

Generally, to manipulate cells, they must be referred to as elements of the spreadsheet, which is a property of the document, or elements of a spreadsheet frame. For the former this would be of the form:

tell application "AppleWorks"

tell spreadsheet of front document

-- script goes here

end tell

end tell

A spreadsheet or database can also call custom functions created in AppleScript, using AppleWorks' MACRO function. To call an AppleScript compiled script file called "My Script File", containing the routine "calculate", for example, a cell or database definition should contain a formula of the form:

=MACRO("My Script File",2,"calculate",reference1, reference2, ...)

and the script file should contain a routine of the form:

on calculate(input1, input2, ...)

-- do some calculations

set myResult to <the final answer>

return myResult

end calculate

Note that the routine name must be all lowercase (bug in AppleWorks).

You have to change the formula to get it to work in some countries (eg not Australia or USA). For instance, in the Danish version of ClarisWorks:

=MAKRO("My Script File";2;"calculate"; reference1, reference2, ...)

Some sample scripts appear below. General installation and use instructions appear on our scripts page. This page is part of BareFeetWare's AppleScripting AppleWorks project.

Purpose

Draws a grid of lines over the borders of the selected cells. Then you can can select those lines, group them, change their color, pattern and thickness. Provides greater flexibility than AppleWorks built in borders. Of course, you can disable the display of the spreadsheet grid for a better effect.

Author: BareFeetWare.

Example

Script

Part of the Enhancement Pack for AppleWorks. 1999.08.26

Purpose

Shades every second row of cells in the selection. Sets the fill color to light blue. In most cases, this can be better achieved using AppleWorks styles.

Author: BareFeetWare.

Script

Download: Shade Alternate Rows. 1999.07.07

Purpose

Calculates the mode (score/s with the highest frequency) of the selected cells or the cells entered in a formula. Run by one of:

Author: BareFeetWare.

- Select some cells in a spreadsheet, run the script from the Scripts menu. A dialog box will display the mode.

orEnter a formula into a cell: =MACRO("Mode",2,"calculate",A1..A5), changing the cell range to refer to your desired input cells. The formula cell will display the mode calculation results and update if any data cells are changed.

Script

Part of the Enhancement Pack for AppleWorks. 1999.07.04

Purpose

I have now made the two scripts for Exponential Regression and Power Regression. The scripts have used your Linear Regression as a template. I have marked the changes from the original Linear Regression with a "--!".

Both scripts require the Satimage scripting addition. Place it in your Scripting Additions folder.

Author: Per Hammershoej Jensen. Last updated 1999.06.22.

Script

Download: Exponential Regression and Power regression. Both require the Satimage scripting addition.

Purpose

Calculates the rank of a value within a range of cells. The rank of a number within a list is the position at which it would appear if the list was sorted.

eg 34 has a rank of 2 in the cell range containing 67, 15, 54, 34, 76.

Enter a formula into a cell: =MACRO("Rank",2,"calculate",theCell+0,theList), changing theCell and theRange to refer to your desired input cells.

eg: If you have a list of numbers in the cells A1 through A5 and want to display the rank of each number in the B column next to each, you would enter =MACRO("Rank",2,"calculate",A1+0,A$1..A$5) into cell B1, then fill it down to B5.

The formula cell will display the rank calculation results and update if any data cells are changed.

Author: BareFeetWare. Inspired by Jim Beckers.

Script

Part of the Enhancement Pack for AppleWorks

Purpose

Link to a cell in another spreadsheet. To get to a cell from another source spreadsheet, use the formula:

=MACRO("Link Cell",2,"getcell",documentName,cellName)

where documentName is the name of the source spreadsheet document and cellName is the name or location of the source cell, eg "sub total" or "B6".

Note that the formula will not auto recalculate when the source cell is changed, but will recalculate when the link cell is moved, edited, or another cell on which it depends is changed.

Author: BareFeetWare. Last updated 1999.06.10.

Script

Download: Link Cell

Purpose

Uses the linear regression (aka least square fitting) method to calculate the slope, y intercept and correlation coefficient of the line best fitting x and y data. Run by one of:

Author: BareFeetWare. Last updated 1999.06.02.

- Select x and y data in two columns in a spreadsheet, run the script from the Scripts menu. A dialog box will display the results.

- Or, enter a formula into a cell: =MACRO("Linear Regression",2,"slope",x_range, y_range), changing the cell ranges to refer to your desired input cells. Replace
slopewithinterceptorcorrelationin another cell to calculate the other results. The formula cells will display the calculation results and update if any data cells are changed.

Script

Part of the Enhancement Pack for AppleWorks including sample spreadsheet using the function.

Purpose

Select some spreadsheet cells in a column and the blank column to its right. Run this script. The first column of cells will contain the first word of the original cells. The rest of the words of each cell will appear in the blank cell to the right.

Author: BareFeetWare.

Example

before:

Mickey Mouse . after:

Mickey Mouse Donald Duck . Donald Duck Fred Flintstone . Fred Flintstone

Download: Separate First Words

Purpose

Calculates the median (middle score of all sorted) of the selected cells or the cells entered in a formula. Run by one of:

Author: BareFeetWare.

- Select some cells in a spreadsheet, run the script from the Scripts menu. A dialog box will display the median.

orEnter a formula into a cell: =MACRO("Median",2,"calculate",A1..A5), changing the cell range to refer to your desired input cells. The formula cell will display the median calculation results and update if any data cells are changed.

Script

Part of the Enhancement Pack for AppleWorks. Last updated 1999.07.04: takes 50% of time on large samples.

**Running Total
**

Author: Hugh Dixon <HDixon@bepp.co.uk>

Download: Running Total

Operates a "running total" on a spreadsheet, with a figure for payment/withdrawal prompted for and inserted into cell B2, and the running total inserted into cell B3. Does not trap inputting errors (letters, signs etc) and does not check that front document is a spreadsheet. Does accept negative numbers.

Purpose

Changes the text in every selected cell to upper case. Author: BareFeetWare.

Script

Download: Upper Case Cells

Purpose

Sets the width of each column in the selection to match the first or second column. Odds match first, evens match second. Author: BareFeetWare.

Script

Download: Alternate Column Widths

Part of the Enhancement Pack for AppleWorks.

Select a range of cells in an AppleWorks spreadsheet. Run this script (from the scripts menu) to set the text color to red for all cells containing negative numbers.

before:

-4 5 8 after:

-4 5 8 3 6 -43 3 6 -43 -7 -89 1 -7 -89 1 2 -5 76 2 -5 76

Download: Sort Through Columns

Select a range of cells in a spreadsheet. This script will sort all cells so that they appear in ascending order with smallest at top of column 1, then larger progressing down column 1 then filling next columns. During run, the user must click OK in the Sort dialog.

before:

4 5 8 after:

1 5 8 3 6 43 2 5 43 7 89 1 3 6 76 2 5 76 4 7 89

Download: Today In Cell Author: BareFeetWare.

Set the value of a the first cell in the selection to today's date.

Please don't hesitate to contact us if you have any questions.

© 1999 BareFeetWare

Return to Parent Folder