Introduction to Mesa 2 Macros

Presented by Rollin White
Vice President, Sundial Systems Corporation

Overview

In Mesa terms, a macro is a script for doing something. It can be very simple to extremely complex. Mesa 2 uses REXX as its macro language. Standard REXX is supplemented with Mesa specific functions called MScripts. These functions do everything from manipulating a spreadsheet to executing a traditional Mesa formula. Using the power of REXX and the rich set of MScripts together lets you create powerful applications.

Creating a Macro

We need to start with a workbook open in Mesa. Each macro, or script, in the workbook has its own page. So, to create a new macro we need to first add a page for it:

Right click on the tab region below the spreadsheet to bring up a popup menu.
Select the "Add script" option.

You should now see a new tab on our spreadsheet named Script001. You should also notice that on the right hand side of the Mesa window we have a new toolbar with a triangle button. This toolbar is a shortcut to executing our script.

Script001 isn't a very descriptive name for our macro, so let's rename it:

Click on the Script001 tab to make it the current layer, i.e., the layer on top.
Right click on the Script001 tab and select the Page Settings menu item. This will open the Selection Manager and jump to the Page Settings tab.
The name field for this page in the workbook is at the bottom of the window. Change it to a better name - let's use "Hello" (you'll find out why in just a moment) - and close the Selection Manager.

It's Time to Write Our First Macro

This section could also be titled "Writing Our First REXX Program." In the empty space on the page put the text:

say 'Hello'

That's it! At first glance this may seem pretty trivial. But you'll quickly realize that while this example is simple, it's the process that's important.

If you click on the triangle on the toolbar, it will execute your macro. You'll see a new window popup called the Script Console. It is used to display text based script output as well as collect input from the user. In our script, the REXX say command will display the text "Hello" to the console.

Close the Script Console and we're ready to move on.

Choosing an MScript

Mesa 2 includes a comprehensive reference for its MScript functions in the online help. There's a large section on Creating Scripts and another with a complete List of MScript Functions. To access these references, select one of the Help options and then use Search or Contents.

We are first interested in two basic MScript commands for getting and setting the contents of cells. If we're going to interact with a Mesa 2 spreadsheet, we will be using these tools frequently!

Delete the text of our previous script example and in its place put the following:

PUTC("1", "[A]A1")

The PUTC function (short for PUTCELLCONTENTS) will place the value (in this case it's 1) into the specified cell ([A]A1). You may not be familiar with the syntax used - the "[A]" refers to the layer and the "A1" is the traditional row/column reference. We could have used "A1" by itself, and Mesa would assume that we wanted A1 on the current layer. Since that assumption may or may not be correct, it's always a good idea to specify the complete cell reference.

If you click on the right triangle in the toolbar, our macro will execute. It may not look like it's done anything, but it has. Change to the A layer and you should see that now there is a 1 in the cell A1.

The Next Step

Now that we've mastered the one task macro, let's do something a little more ambitious - two steps!

Erase the PUTC line. In its place put the following two lines:

CellValue = GETC("[A]A1")
PUTC(CellValue, "[A]A2")

The first line introduces two new concepts. The GETC function (for GETCELLCONTENTS) will get the contents of the named cell. It returns those contents and puts them into a REXX variable we've decided to name CellValue. In our second line, rather than putting a fixed number into the specified cell we're using the CellValue variable.

Run the macro. Change to the A layer and change the value in A1. Run your macro again and see that the value in A2 is the same as A1. Do this enough times to convince yourself the script is working.

Let's Do Something Really Useful

Actually, copying the contents of one cell to another can be extremely helpful. But copying the value of a cell often be even more useful. Let's add two additional lines to our example. Here is the entire script:

CellValue = GETC("[A]A1")
PUTC(CellValue, "[A]A2")
CellContents = GETV("[A]A1")
PUTC(CellContents, "[A]A3");

We're using a new MScript - GETV (for GETVALUE) - to retrieve the value of a cell. There is no PUTV function because there is no difference between contents and value during input.

Execute the script and look at the results. It should put the number you've entered into A1, A2 and A3 as well. Now put a formula into A1. It can be something simple like "=1+1". Execute your script again. On the surface the results will look similar. However, if you put the cursor on each of the cells, and watch the Mesa 2 formula bar, you'll notice that there is some difference.

A1 will have your original formula
A2 will also have your original formula because the contents of A1 is that formula.
A3 will have the numerical result of the formula without any reference to the formula. That is the value of the cell A1.

Selecting cell contents versus cell value will depend on what you're trying to accomplish in your macro. If your macro is going to analyze the data it is retrieving, you will want the cell's value. However, if you are moving data around to a different location on the spreadsheet you'll want the cell's contents.