I was wanting to finish the MScript reference for Mesa 2 version 2.1.6, but I never did it. It covered only CELL through CURRENTPATH, so I decided to start over with Mesa 2 version 2.2.
MScript is a language that needs to be used with some responsibility on your part. A Mesa 2 script can use REXX functions within a script, and so you can do really nasty things with it. OS/2 is durable and should withstand any honest attempt to make a good script. However, this is no excuse for saving your data often while debugging a script. Also, if you want to see if you can do really nasty things to OS/2 from Mesa 2, then keep your data on its own partition or on some kind of backup medium (floppy disk, Zip or Jaz drive, CD-ROM, Internet, etc.).
Make sure all of the required arguments have values in them. Some REXX commands will let you leave fields blank, such as STRIP('12.7000',,0). When I tried CELL(2,,4) in MScript, Mesa 2 crashed with a SYS3175 error. That being stated, Mesa 2 version 2.2 seems forgiving when it comes to empty optional arguments, such as CLEAR(,"B2:B4").
Here's an example of a syntax diagram from the Mesa 2 online help:
This means the following:
AUTOFORMULA is the script equivalent of Mesa 2's AutoFormula feature. AutoFormulas include the AutoSum button on the default toolbar. If you press the AutoSum button, then Mesa 2 will generate an =SUM() function for you.
Given that idea, there are eight AutoFormulas and their type equivalents:
| AutoFormula | type |
|---|---|
| Sum | SUM |
| Standard Deviation | STDEV |
| Variance | VAR |
| Minimum | MIN |
| Maximum | MAX |
| Sum of Squares | SUMSQ |
| Product | PROD |
| Average | AVE |
Mesa 2 will default to SUM if you use the wrong type or omit type.
Note: In the Mesa 2 documentation, the diagram shows type as being required. Both arguments to AUTOFORMULA are optional. Also, the AVE value is not mentioned. It can be seen in the Script Recorder, though, so I don't think of it as undocumented.
It's okay to use empty arguments with AUTOFORMULA. AUTOFORMULA(,"B4:B10") is valid.
range is an area over which you apply the AutoFormula function.
This is a really good way to destroy data! AUTOFORMULA is predictable, but it makes quite a few decisions about where it should be applied. For a full explanation of what AutoFormula does, look at The Flight of the AutoSum. If you want to be safe, then clear the area where you want the formulas to be applied. That makes the decision fairly easy for Mesa 2.
Here's an example of AUTOFORMULA:
/* this script applies an AutoFormula to B2:D4, placing formulas in B4:D4 */
CLEAR("CONTENTS", "B4:D4")
AUTOFORMULA("MAX", "B2:D4")
It will place =MAX() formulas in B4:D4. If there's no data in D2:D3, then it will put =MAX() formulas there, too.
An alternative is to change the AUTOFORMULA range to B4:D4. That will =MAX() only the cells above it, but only if there is continuous data in the cells above it. If C3 is blank, then AUTOFORMULA won't put a formula in C4. If D2 is blank, but D3 has data in it, the AUTOFORMULA will put =MAX(D3) in D4.
The key here is that if range is 2x2 or greater, then you can use range as a container for Mesa 2 to use. Otherwise, be sure you know the arrangement of your data.
CALCULATEFUNCTION calculates a Mesa 2 spreadsheet function. For instance, CALCULATEFUNCTION("SQRT(4)") returns 2. Here's an example script:
/* this script returns the average of (1) and ( sqrt(a)*2 ) */
a=GETV()
a=CALCULATEFUNCTION( "AVE( 1, SQRT("a") *2)" )
SAY a
There aren't many places to go wrong with this function. Here, if a=4, then REXX changes "SQRT("a")" to "SQRT(4)". With functions like these, the trouble comes with REXX concatenation. Just balance your quotes, and everything will be okay.
CELL reports a cell's address. That way, instead of having to think, "The 4th row, 27th column, 1st layer is $TestLayer$AA$4," you can code it as CELL(4,27,1). Here's an example:
/* this script fills [B]A1:[B]A10 with the string "Hello" */
DO a = 1 to 10
PUTC("Hello", CELL( a, 1, 2))
END
CELL gives an almost theoretical answer. If the 3rd layer hasn't been created, then CELL(1,1,3) will return [$C]$A$1 and not "the third layer doesn't exist." However, if you make a third layer and give it the name "ThirdLayer", then CELL(1,1,3) will return [$ThirdLayer]$A$1.
CELL works within Mesa 2's limits. It won't let you get away with CELL(100000,18279,703), but it will let you use CELL(99999,18278,702).
If layer is not given, then CELL will return an address for the first layer.
CLEAR removes parts of a cell. The valid arguments are as follows:
| Clear Method | special |
|---|---|
| Clear | NORMAL |
| Clear special -> Cell | CELL |
| Clear special -> Cell | SINGLECELL |
| Clear special -> Format | FORMAT |
| Clear special -> Values | VALUES |
| Clear special -> Formulas | FORMULAS |
| Clear special -> Strings | STRINGS |
| Clear special -> Numbers | NUMBERS |
| Clear special -> Contents | CONTENTS |
| Clear special -> Notes | ATTACHMENTS |
CONTENTS and ATTACHMENTS are not mentioned in the help file, but they do show in the Script Recorder.
If special is omitted, then Mesa 2 will treat it as a normal Clear. If special is invalid, then Mesa 2 will do nothing.
Here are some valid uses for CLEAR:
CLEARCONSOLE removes all text from the Script Console. The best uses for this function are debugging and user input. Here's an example of clearing the console for a debug session:
/* this script intentionally forces a REXX error message */ CLEARCONSOLE() a=1/0
CLOSE closes the active spreadsheet. Here's an example:
/* this script places a timestamp in [A]A1, then closes the file */
PUTC("=NOW()", "[A]A1")
CONVERTRANGE("VALUES", "[A]A1")
SAVE()
CLOSE()
CLOSECONSOLE closes the script console. A good use for this function is to print a message, wait, then close the console. Here's an example:
/* this script prints a message to the Script Console and closes the console */
RxFuncAdd("SysSleep","RexxUtil","SysSleep")
CLEARCONSOLE()
SAY "Good job!"
SysSleep( 3 )
CLOSECONSOLE()
CONVERTRANGE converts a cell's contents from one form to another form. Here are the valid values:
| Conversion | type |
|---|---|
| Formulas to values | VALUES |
| Strings to numbers | STON |
| Numbers to strings | NTOS |
| To upper case | UPPER |
| To lower case | LOWER |
| To proper case | PROPER |
type can be omitted. For instance, CONVERTRANGE() is valid. If type is invalid, then Mesa 2 will convert formulas to values.
Here's an example of CONVERTRANGE:
/* This script places the cube root of 3 in A1 as a value. */
PUTC( "=ROOT(3,3)", "A1" )
CONVERTRANGE("VALUES", "A1")
CONVERTRANGE is an efficient function. CONVERTRANGE("UPPER","[A]A1:[ZZ]ZZZ99999") won't convert billions of cells when I have data in only five cells.
COPY copies a range of cells onto the clipboard. Consider COPY to be a crutch while learning MScript. The combination of COPY and PASTE can work in a pinch until the specialized (and faster) MScript functions have been learned.
COPY is good for when I want to copy the same range of data onto the clipboard every time I do a Copy operation. The OS/2 Clipboard Viewer is willing to render a Mesa 2 item in the following six formats:
Take note here: COPY doesn't check the "Copy range graphics" in the Mesa 2 Preferences notebook (Options page). The MScript function COPY will still put the range graphics on the OS/2 Clipboard, even when the Mesa 2 user interface isn't set to do so.
Here's an example of COPY:
/* move A1:B5 to A11:B16, using the OS/2 Clipboard */
COPY("A1:B5")
PASTE("MOVE","A11")
COPY returns 1 on success and 0 on error. I'm still looking for an example of when it returns 0. For me, it returns either 1 or a SYS3175 in MESASPRD.DLL. COPY("[Z]A1") is not a good idea when Layer Z doesn't exist.
COPYRANGE and COPYCELLS do the same thing: They do a COPY and PASTE without using the OS/2 Clipboard. This can be much faster than the COPY-SEL-PASTE combination because Mesa 2 is not trying to place six different file formats on the OS/2 clipboard.
type can be one of the following values:
| Paste Method | type |
|---|---|
| Paste | NORMAL |
| Paste link | LINK |
| Paste special -> Paste values | VALUES |
| Paste special -> Paste format | FORMAT |
| Paste special -> Transpose | TRANSPOSE |
| Paste special -> Move | MOVE |
| Paste special -> Insert right and paste | INSERTRIGHT |
| Paste special -> Insert right and paste | RINSERT |
| Paste special -> Insert down and paste | INSERTDOWN |
| Paste special -> Insert down and paste | DINSERT |
type is optional. COPYRANGE(,"A1:B2") is valid. destrange is required, though you'll get only a REXX error if you try to go without it.
LINK is not mentioned in the documentation for PASTE, and so it's not mentioned in the documentation for COPYRANGE. Paste Link is in the Script Recorder, though. The online docs asked me to look at PASTE for instructions, and I did just that.
Here's an example of COPYRANGE. It's a condensed version of the COPY example:
/* move A1:B5 to A11:B16, bypassing the OS/2 Clipboard */
COPYRANGE("MOVE","A11","A1:B5")
CREATEGRAPHIC and CREATEGRAPH do the same thing: Create a graphic or graph.
While there is a distinction between graph and graphic in the Mesa 2 user interface, there is no such distinction in MScript. For instance, you can create a graphic with either CREATEGRAPHIC or CREATEGRAPH.
The only difference between the MScript function and the user interface is this: The MScript function renders graphics with a gray interior instead of as a wire-frame object.
These are the values for type. The values that aren't 100% obvious are shown in a bold font:
| Graphic type | type |
|---|---|
| Oval | OVAL |
| Text box | TEXTBOX |
| Rectangle | RECT |
| Rectangle | RECTANGLE |
| Line, Arrowheads: None | LINE |
| Line, Arrowheads: Start | LEFTARROW |
| Line, Arrowheads: End | RIGHTARROW |
| Line, Arrowheads: Both | BOTHARROW |
| Graph type | type |
| Bar | BARGRAPH |
| Stacked bar | STACKEDBARGRAPH |
| Column and line | BARLINEGRAPH |
| Column | COLUMNGRAPH |
| Stacked column | STACKEDCOLUMNGRAPH |
| Line | LINEGRAPH |
| Scatter | SCATTERGRAPH |
| Area | AREAGRAPH |
| Pie | PIEGRAPH |
| High/Low | HIGHLOWGRAPH |
| Candlestick | CANDLESTICKGRAPH |
| 3-D bar | 3DBARGRAPH |
| 3-D area | 3DAREAGRAPH |
| 3-D pyramid | 3DPYRAMIDGRAPH |
| 3-D ribbon | RIBBONGRAPH |
If type is incorrect, then Mesa 2 will default to COLUMNGRAPH.
rng is an optional range to graph. If a graph is being created, then rng is the area to graph. If a graphic is being created, then rng is the anchor cell for the graphic.
In the Mesa 2 user interface, the commands are grayed when only one cell is selected. If one cell is selected or specified, then Mesa 2 will graph one value, which usually looks like an empty graph.
Here's an example of CREATEGRAPHIC:
/* create an oval that has its anchor in cell B2 */
CREATEGRAPHIC("OVAL","B2")
The next few functions start with the word CURRENT. They have aliases that start with only the letter C. For instance, CURRENTCELL() can also be written as CCELL(). The aliases save a lot of typing.
CURRENTCELL and CCELL return the address of the current base cell. They will return an address, even if a graph or graphic is selected at the time. However, it will return nothing if a script page is showing and not a worksheet.
Here's an example of using CCELL to clear a layer up to the current cell:
/* In this example, [A]Z14 is the current base cell. */ a=CCELL() /* a="[$A]$Z$14" */ a=CELL( 1, 1)":"a /* a="[$A]$A$1:[$A]$Z$14" */ CLEAR( "NORMAL", a )
CURRENTFILE and CFILE return the name current file. Assume nothing beyond that. Mesa 2 doesn't have a "this file has never been saved" test function. For the first new document, CFILE() will return "Blank1.m2" and not an empty string. It might be foolish to assume a file to have never been saved because it has a name like "Blank1.m2".
For me, the best use of CFILE() is to save a file to an alternate location. Here's an example:
/* This will save a file to "D:\backup". Note: If the original file is "C:\original\work.m2", then I'll still be working on "C:\original\work.m2" after the save. */ a = CFILE() SAVE( "D:\backup\"a )
CURRENTPATH and CPATH return the full name of a file. They will return the full file name, even if the file has never been saved.
CPATH works well on networks. If I'm working on \\mfalcon\mlsemon\work.m2 without assigning a drive to \\mfalcon\mlsemon, CPATH still returns \\mfalcon\mlsemon\work.m2.
This example works well when a button is assigned to it. Here is a custom Save function:
/* This script deters people from saving
work on their local hard drives. */
a = CPATH()
a = LEFT( a, 2 )
IF a == "G:" THEN
SAVE()
ELSE
DO
CLEARCONSOLE()
SAY "New company policy: Save your work to the",
"G drive because it is the network drive."
END
CURRENTRANGE and CRANGE return the address of the current range. It returns nothing if a script page is selected. If a graphic has been selected, it will return the graphic's name. This makes me wonder if there's a difference between CRANGE and CSEL.
Here is an example of CRANGE:
/* Save the file with A1 selected, then return to the current range. */ a = CRANGE() SEL( "A1" ) SAVE() SEL( a )
That's about all, for now. Have fun!