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:

1. Select some cells in a spreadsheet, run the script from the Scripts menu. A dialog box will display the mode.
   
2. or Enter 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.
   
   

Author: BareFeetWare.

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:

1. 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.
   
   
2. 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 slope with intercept or correlation in another cell to calculate the other results. The formula cells will display the calculation results and update if any data cells are changed.
   
   

Author: BareFeetWare. Last updated 1999.06.02.

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