--- Crosstabulation ---

2016

Until I figured out how to create stub and banner tables with LibreOffice pivot tables, I was considering making a tabling routine for DANSYSX but that would be redundant, so my focus for crosstabulation became just the 2-way and 3-way statistics programs. Since they are involved and, from scratch, it will give me a chance to talk about my structured approach to developing Calc routines.

XTab is a big (!) matrix function, but it's a sequential function, meaning that it has many procedures that are completed before the next process is started. My first task is to outline the processes with comments. A comment in LibreOffice Basic begins with a single apostrophe. Here, I include a header:

FUNCTION XTab(optional InMat,optional InType, optional CellCont as string,
_)

(It's not complete, because I'll be adding parameters as different procedures require them.) Then, comes a descriptive comment:

'Performs a complete analysis on crosstabulation data
'from a raw data table or a crosstabulation of data.
'The input is the data and the output is a matrix containing
'a stub-and-banner table of cell data followed by
'a table of statistics. InType=1 (Crosstab), 2 (raw data)
'CellCont is a binary string designated data to be displayed in
'cells of output: 10 places: counts, exp counts, row%, column%,
'Total%, residuals, stand. residuals, adj, residuals, chi square,
'likelihood chi square, odds, compare column proportions (Bonferroni).

and a declarations and initialization section:

'Declarations and initialization. If InMat isn't specified,
'InMat=MatArray1. If InType isn't specified, InType=1 (crosstab).
'If CellCont isn't specified, CellCont="100000000000"

DIM OutMat(), Xi(), Yj(), OutR as integer, OutC as integer
DIM I AS INTEGER, J AS INTEGER, K AS INTEGER, L AS INTEGER

That will also grow as I add code. I'll declare other variables in alphabetical order, inserting each as I use them, so I can keep up with names that I've used. Double declarations and accidentally using the same variable for multiple purposes without knowing it can be major headaches.

Then, I know that I'll have to crosstabulate data if a raw data table is fed to the function, so I annotate that section:

'Crosstabulation, if needed.


Redim OutMat(), Xi(1,2), Yj(1,2)

Thinking ahead, I know I'll have to redimension the output matrix when I know what the stub-and-banner part of the display will look like. Everything below that will be pretty straightforward and I can add size as I finish each section. Each section is independent of the ones below it, so I can test each one as I finish it. I have a test data on the Report sheet of DANSYSX that I will use to progressively test each section.

The final section will just display the OutMat matrix which, by that time, will already be constructed by the procedures I have coded. OutMat is a convention I use for all macros that display a matrix.

I will have the crosstabulation saved in MatArray3, just to increase it's utility and make it compatible with the matrix functions I've programmed for DANSYS and DANSYSX.

I use a lot of spreadsheet functions in the program and that's a little tricky. The topic isn't covered in the programming guide, but you can find information online.

To use a spreadsheet function in a Basic routine, you must first declare an object variable to hold the function. You only have to declare it once since you can't use more than one spreadsheet function at a time. I use the same name, svc, and declare it as an object variable:

DIM svc AS OBJECT

Then the variable has to be set as a function variable. That only has to be done once, also.

svc=createUnoService("com.sun.star.sheet.FunctionAccess")

Uno is the object manipulation language used by LibreOffice. This statement invokes a Uno service called FunctionAccess.

After the variable is set, it can be used to call spreadsheet functions such as :

PCS=svc.CallFunction("CHIDIST",Array(CSQW,dfCS))

This statement calls the CHIDIST spreadsheet function. CHIDIST evaluates a chi square value at a specified degrees of freedom value. The CallFunction method for a function variable requires the name of the function as a string (in quotes) followed by the information to be transferred to the function in an array. The function requires a value and degrees of freedom (if you look the function up in the Calc Function Wizard, you can see the structure of the function) to be passed as an array. Even if you pass an array to a spreadsheet function, you have to use the Array statement followed by the name of the array in parentheses. You can see that here:

XMIN=SVC.CALLFUNCTION("MIN",ARRAY(Eij))

in a statement that finds the minimum value in the array Eij.

To get the return value, a variable, PCS, is set equal to the function variable.

One of the aggravating things about LibreOffice Basic is you can't return a matrix like this, which is why I went about programming a complete matrix language for DANSYS.

Displaying the results is easy. I just set the function to the output matrix, like:

XTab=OutMat.

There was some debugging that had to be done, but you can see the final product when I post DANSYSX in the Therian Timeline, here:

http://www.theriantimeline.com/excursions/labbooks

Note: DANSYSX version 1.0 is available now on the timeline.

--- Graphics ---

2016

One of my more frustrating programming episodes occurred with one of OpenOffices updates. I tried to install the new version and it wouldn't install. The suggestion on the user forum was to uninstall the older version and then try to install the new version. The new install still didn't work so I had no working version of OpenOffice and a lot of work on DANSYS that I had not saved. Dumb of me - irritating of OpenOffice. That was when I decided to junk OpenOffice and switch to LibreOffice.

The biggest loss was a set of programs I had put a lot of work into that expanded the ability of OpenOffice Calc to produce statistical graphs. They constituted a graph building suite. Instead of creating a set of predesigned graphs (as with the charts currently produced by Calc), I could build graphs from elements such as axes, points, lines, and such. I decided to ditch the idea. I just didn't have the heart to redo all that work, and I had other programs that would produce all the statistical graphs I would ever need.

But then I started thinking (often a big mistake, but one that often leads to adventure) that it would be nice to have DANSYSX be able to generate some simple graphics. Points and lines and such are useful for a lot more than making statistical graphs.

One limitation of spreadsheet functions is that they can only output data into cells, so I couldn't use functions to generate geometrical objects on a spreadsheet. I would have to use subroutines, and thus commands, to do the job, and that meant creating a menu of graphical commands.

I also wanted to be able to translate the positions of the geometrical objects into a specified area - a frame. I had done that with the OpenOffice commands, so I should be able to do it again for LibreOffice.

This is a different kind of programming than creating a function to output a value or matrix of values. I had to actually place objects on a spreadsheet. That meant that I had to have the program determine where everything had to go and place it there. LibreOffice Basic can do that and I'll show you the frame routine. You can find the others in the Graphics folder of the DANSYSX macros.

One thing I remember doing over and over in the older programs was translating data points from one range (the range of data values) to another (the range of positions on a frame on a spreadsheet). That got old, so, this time I created a very simple function to do that. It looks like this:

Function Rescale(c,a,b,y,z)
'Rescales a value, c, between a and b to be proportionally
'between y and z)
Rescale=(c-a)*(z-y)/(b-a)+y

End Function

That was easy and, if you went through the article, DANSYSX: Complex cubic equations, you know what that all means.

The way I had the older macros work is that the user would create a specification form on a spreadsheet for a specific object, fill it out, process the specifications, and then display the object on the spreadsheet.  I've trimmed this four step process down in DANSYSX except for the frame. You still have to process the specifications for the frame. A frame has to be generated (whether it's displayed or not) so LibreOffice knows where to place the graphics object.

The Specification routine is tedious (there's a lot of information required to specify the frame) but straightforward. A frame is just a rectangle. Draw a rectangle and you have a frame that you can situate everything else on.

The only complication is that I need different specifications according to the kind of background to have on the frame, so I had to work up a dialog to select the kind of background before printing the specification form.

Here is the specification routine:

SUB FrameSpec()
'Prints out a frame specification form onto a spreadsheet
'beginning at the current cell.
DIM I AS INTEGER, J AS INTEGER
DIM RP, CP, DLG AS OBJECT, STL AS INTEGER, LBX AS OBJECT
DIM oDoc AS OBJECT, oSheet AS OBJECT, oSel AS OBJECT
DIM addr AS OBJECT, M AS INTEGER, N AS INTEGER
DIM oCell AS OBJECT, oView AS OBJECT



'Display dialog Frame

DialogLibraries.LoadLibrary("Standard")
DLG=CreateUnoDialog(DialogLibraries.Standard.Frame)
DLG.Execute()


LBX=DLG.getControl("ListBox1")
STL=LBX.SelectedItemPos

IF DLG.Execute()=0 THEN EXIT SUB


'Print form
 oDoc=ThisComponent
 oView=ThisComponent.getCurrentController()
 oSheet=oView.getActiveSheet()
 oSel=oDoc.getCurrentSelection
 addr=oSel.getRangeAddress()

 m=addr.StartRow
 n=addr.StartColumn

oCell=oSheet.getCellByPosition(n,m)
oCell.String="Frame"
oCell=oSheet.getCellByPosition(n,m+1)
oCell.String="Position 1/10 mm"
oCell=oSheet.getCellByPosition(n+1,m+2)
oCell.String="X"
oCell=oSheet.getCellByPosition(n+1,m+3)
oCell.String="Y"
oCell=oSheet.getCellByPosition(n,m+4)
oCell.String="Size 1/10 mm"
oCell=oSheet.getCellByPosition(n+1,m+5)
oCell.String="Width"
oCell=oSheet.getCellByPosition(n+1,m+6)
oCell.String="Height"
oCell=oSheet.getCellByPosition(n,m+7)
oCell.String="Margins: 1/10 mm"
oCell=oSheet.getCellByPosition(n+1,m+8)
oCell.String="Top"
oCell=oSheet.getCellByPosition(n+1,m+9)
oCell.String="Bottom"
oCell=oSheet.getCellByPosition(n+1,m+10)
oCell.String="Left"
oCell=oSheet.getCellByPosition(n+1,m+11)
oCell.String="Right"
oCell=oSheet.getCellByPosition(n,m+12)
oCell.String="Caption"
oCell=oSheet.getCellByPosition(n+1,m+13)
oCell.String="Top"
oCell=oSheet.getCellByPosition(n+1,m+14)
oCell.String="Bottom"
oCell=oSheet.getCellByPosition(n+1,m+15)
oCell.String="Left"
oCell=oSheet.getCellByPosition(n+1,m+16)
oCell.String="Right"
oCell=oSheet.getCellByPosition(n,m+17)
oCell.String="Transparency: percent"
oCell=oSheet.getCellByPosition(n,m+18)
oCell.String="Border"
oCell=oSheet.getCellByPosition(n+1,m+19)
oCell.String="Line Style: NONE, SOLID, DASH"
oCell=oSheet.getCellByPosition(n+1,m+20)
oCell.String="Line Color: RGB"
oCell=oSheet.getCellByPosition(n+1,m+21)
oCell.String="Line Transparency :percent"
oCell=oSheet.getCellByPosition(n+1,m+22)
oCell.String="Line Width: 1/10 mm"
oCell=oSheet.getCellByPosition(n+1,m+23)
oCell.String="Line Joint: NONE, MIDDLE, BEVEL, MITER, ROUND"
SELECT CASE STL
CASE=0
oCell=oSheet.getCellByPosition(n,m+24)
oCell.String="Background: NONE"
CASE=1
oCell=oSheet.getCellByPosition(n,m+24)
oCell.String="Background: SOLID"
oCell=oSheet.getCellByPosition(n+1,m+25)
oCell.String="Red"
oCell=oSheet.getCellByPosition(n+1,m+26)
oCell.String="Green"
oCell=oSheet.getCellByPosition(n+1,m+27)
oCell.String="Blue"
CASE=2
oCell=oSheet.getCellByPosition(n,m+24)
oCell.String="Background: HATCH"
oCell=oSheet.getCellByPosition(n+1,m+25)
oCell.String="Style: SINGLE, DOUBLE, TRIPLE"
oCell=oSheet.getCellByPosition(n+1,m+26)
oCell.String="Color: RGB"
oCell=oSheet.getCellByPosition(n+1,m+27)
oCell.String="Distance: 1/100 mm"
oCell=oSheet.getCellByPosition(n+1,m+28)
oCell.String="Angle: 1/10 degree"
CASE=3
oCell=oSheet.getCellByPosition(n,m+24)
oCell.String="Bitmap - Named"
oCell=oSheet.getCellByPosition(n+1,m+25)
oCell.String="Name"
oCell=oSheet.getCellByPosition(n+1,m+26)
oCell.String="Style: REPEAT, STRETCH, NO_REPEAT"
CASE=4
oCell=oSheet.getCellByPosition(n,m+24)
oCell.String="Bitmap - URL"
oCell=oSheet.getCellByPosition(n+1,m+25)
oCell.String="URL"
CASE=5
oCell=oSheet.getCellByPosition(n,m+24)
oCell.String="Gradient - Named"
oCell=oSheet.getCellByPosition(n+1,m+25)
oCell.String="Name"
CASE=6
oCell=oSheet.getCellByPosition(n,m+24)
oCell.String="Gradient - Custom"
oCell=oSheet.getCellByPosition(n+1,m+25)
oCell.String="Style: LINEAR, AXIAL, RADIAL, ELLIPTICAL, SQUARE, RECT"
oCell=oSheet.getCellByPosition(n+1,m+26)
oCell.String="Start Color: RGB"
oCell=oSheet.getCellByPosition(n+1,m+27)
oCell.String="End Color: RGB"
oCell=oSheet.getCellByPosition(n+1,m+28)
oCell.String="Angle: 1/10 degree"
oCell=oSheet.getCellByPosition(n+1,m+29)
oCell.String="X Offset: 1/10 mm"
oCell=oSheet.getCellByPosition(n+1,m+30)
oCell.String="Y Offset: 1/10 mm"
oCell=oSheet.getCellByPosition(n+1,m+31)
oCell.String="Start Intensity: percent"
oCell=oSheet.getCellByPosition(n+1,m+32)
oCell.String="End Intensity: percent"
oCell=oSheet.getCellByPosition(n+1,m+33)
oCell.String="Step Count: number of color graduations"
END SELECT

It's long but fairly simple. It does illustrate how you can get things from a program onto a spreadsheet, though. Let's look at the sections.

Variables can be set to hold things like documents, sheets, cells, ranges, shapes, and such but they have to be declared as objects. This program uses several object variables.

DialogLibraries.LoadLibrary("Standard")
DLG=CreateUnoDialog(DialogLibraries.Standard.Frame)
DLG.Execute()


LBX=DLG.getControl("ListBox1")
STL=LBX.SelectedItemPos

IF DLG.Execute()=0 THEN EXIT SUB

This section displays the dialog that has the list box of styles for backgrounds. I constructed the dialog in the dialog editor (Select Macro>Organizer>Dialogs tab) with a listbox (I specified the items in the listbox in the properties settings for the listbox.), a Okay button, and a Cancel button. Here's what the dialog editor looked like:

"DialogLibraries.LoadLibrary("Standard")" loads the library that contains the Frame dialog.
"DLG=CreateUnoDialog(DialogLibraries.Standard.Frame)" sets the DLG object variable to contain the Frame dialog.
"DLG.Execute()" displays the dialog. As long as the dialog is displayed and working, nothing else runs in Calc.
"LBX=DLG.getControl("ListBox1")" loads the listbox1 control into object variable LBX and "STL=LBX.SelectedItemPos" gets the selected text string from the listbox1.
"IF DLG.Execute()=0 THEN EXIT SUB" specifies that, if the Cancel button is clicked (which generates a 0 from the DLG object), that the subroutine is closed without doing anything.

 oDoc=ThisComponent
 oView=ThisComponent.getCurrentController()
 oSheet=oView.getActiveSheet()
 oSel=oDoc.getCurrentSelection
 addr=oSel.getRangeAddress()

 m=addr.StartRow
 n=addr.StartColumn

The oDoc variable is set to contain the document that is currently open (DANSYSX)
The oView variable is set to contain the current documents view.
The oSheet variable is set to contain the currently active sheet in the view of the document.
The oSel variable is set to contain the currently active range (in this case, just one cell, the top left cell intended to start the Frame form.)
The addr variable contains the address structure of oSel. It contains the starting and ending cell row and columns as a data structure.

Data structures are addressed by the name of the data structure (in this case addr) followed by a period and the name of the specific value to be accessed. For instance, addr.StartRow returns the value of the StartRow value in addr. The integer variable m is set to the StartRow value and the integer variable n is set to the StartColumn value.

The rest of the subroutine is just a long list of statement of the form:

oCell=oSheet.getCellByPosition(n,m)
oCell.String="Frame"

Here, the oCell variable is set to the cell at column n and row m on the currently active sheet. Notice that cell positions are opposite to matrix positions. Matrix positions are addressed by (row,column); cell positions are addresses by (column, row) order. This is in keeping with the way cells are addressed on spreadsheets. A cell at position C5 is at column C and row 5. It's also not obvious here, but the first row on a spreadsheet is row 0, and the first column is column 0.

I use a Select Case structure to decide the labels to output onto the spreadsheet according to which background style is chosen. The Select Case structure works like this:

SELECT CASE (statement)
CASE (value)
CASE (value)
...
CASE (value)
END SELECT

The statement in the first line specifies what the following case values refer to. In this example, the case is the value of STL, the numerical position of the selected style in the dropdown list of the dialog. The first CASE statement is CASE 0, which is the value of the first item, NONE, in the list. If STL=0, the block of statements following CASE 0 is executed and, then, the other case statements are ignored. END SELECT marks the end of the Select Case structure. Notice that the Case values can be numerical or string values, inequalities like >5 or <=3, or statements like "between 2 and 5".

When this routine is run from the Graphics menu>FrameSpec, a form is printed onto the active spreadsheet beginning at the selected cell. This form can be filled in to specify a frame for other graphic objects.

Saving all this information so that it doesn't go away between sessions requires that I construct a hidden spreadsheet to save it on. You can see that by unhiding the Graphics sheet in DANSYSX. Right click on any sheet tab and select the Show Sheet... command. It will open a list of hidden sheet. Double click Graphics. Be careful and don't change anything. You'll the information generated by the FrameSpec command in the top part of the sheet. Below that, though, are several calculated value. These are values necessary to scale values to the frame, taking margin specifications into account.

The FrameGen command transfers information from the frame table to the FrameSpec sheet. It uses much of the same Basic and Uno commands but there are two sheets to deal with, the sheet the table is on and the FrameSpec sheet. You will recognize most of the code if you look at it.

This pattern is repeated a lot.

oCell=oSheet.getCellByPosition(n1+1,m1+0)
aCell=aSheet.getCellByPosition(1,0)
aCell.Value=oCell.Value

That, of course, transfers the information from the first column to the right and top row of the frame table to the first column and top row of the Frame Spec sheet. These lines are quite simple but tedious to write. Luckily, there is a lot of cut, paste, and modification going on.

There is an interesting little subroutine tagged onto the end that parses out a string of color specifications. Here, color is specified by three numbers from 0 to 225 specifying how much red, green, or blue is used to create the color desired. The subroutine is called using the statement:

GOSUB FG10

The cell containing the string is first specified as aCell. The subroutine has to come after the last line of the main subroutine:

EXIT SUB

and it must begin with the calling label. Here is the subroutine:

FG10: 'RGB subroutine. Sets R, G, and B.
RGBSTR=aCell.String
Clr=""
S=1
FOR I=1 TO LEN(RGBStr)
Clr=Clr & MID(RGBStr,I,1)
IF MID(RGBStr,I,1)="," OR I=LEN(RGBStr) THEN
IF S=1 THEN
R=Val(Clr)
ELSEIF S=2 THEN
G=Val(Clr)
ELSE
B=Val(Clr)
END IF
S=S+1
Clr=""
END IF
NEXT I

RETURN


 END SUB

The string in aCell is transferred into the RGBStr variable and the following loop structure takes the string apart, converting the parts between the commas into numerical values stored in the R, G, and B variables. The last line of the subroutine is RETURN, which switches processing back to the line immediately following the last GOSUB statement. Finally, the whole macro is closed out using the END SUB statement.

More than one subroutine with different line labels can be placed at the end of a macro, but they all have to come before the END SUB or END FUNCTION statement.

The FrameShow command acually places the graphics frame on the spreadsheet. It is just a drawn rectangle and the LibreOffice Basic Programmer's Guide very adequately explains how to code for various drawn shapes and I will direct you to that since there are a lot of possibilities, but I will point out a few things that it doesn't make terribly clear.

First, every spreadsheet has it's own drawing page. In Draw and Impress, this is pretty explicit, but not for Calc. The way the drawing page for the active sheet is specified is:

oSheet = aDoc.getcurrentcontroller.activesheet
DPAGE=oSheet.DrawPage

The first line defines the active sheet and loads it into an object variable (oSheet). The second loads the drawing page of oSheet into another object variable called DPAGE. Later, the frame, which is a rectangle stored in the object variable oRect, is placed on the drawing page by the following line:

DPAGE.add(oRect)

The frame is built up by sequentially adding the frame and all the specified captions (which are rectangles with text) with similar commands.

Other commands to create points, lines, ellipses, and boxes will be very similar. They will all be in the Graphics folder of the DANSYSX macro library.

--- DANSYSX: Complex cubic equations ---

2016

Both LibreOffice Calc and DANSYS are able to do basic complex arithmetic, but I want DANSYSX to be able to do advanced complex mathematics. There are a number of algorithms on Jean Pierre Moreau's excellent website (http://jean-pierre.moreau.pagesperso-orange.fr/index.html) that I want to adapt to this purpose.

The first is an algorithm to determine the roots of a cubic equation with complex coefficients. It uses the cubic formula (like the quadratic formula, but for cubic functions):

z(1...3) = cube root (-q/2 + square root(q^2/4 + p^3/27)) + cube root(-q/2 - square root(q^2/4 + p^3/27))

where: p = (c/a) - (b^2/3a^2)
           q = (2b^3/27a^3) + (d/a) - (bc)/3a^2)

I want the complex mathematics programs to be in their own folder so I use Tools>Macros>Organize Macros>LibreOffice Basic>Organizer to create the new folder, Complex under DANSYSX.ods>Standard. To begin programming, I open the LibreOffice Basic Macros dialog and select DANSYSX.ods>Standard>Complex and click the Edit button.

I am more comfortable programming in an environment where indexes run from 1 to whatever (instead of beginning at 0), so I add the Option Base 1 to the top of the editor Window, and change the name of the blank macro from Main to CompCube. I will also want the Macro to be a function so I change the header from Sub to Function and the result looks like this:

REM  *****  BASIC  *****

Option Base 1
Function CompCube

End Function

Now, everything is ready for me to start programming. I have to decide what I want my program to look like - the inputs and outputs. And I want to add a descriptive comment to the top of the code.

Looking at the Moreau algorithm, I see that I will need to input four complex numbers and I want to stick to the LibreOffice convention of designating complex numbers as strings of the form a±bi, so I want to pass four strings to the function. I will also want to output three complex numbers because a cubic equation will have up to three unique roots. So I'll start with the following header:

REM  *****  BASIC  *****

Option Base 1
Function CompCube(Z1 AS STRING, Z2 AS STRING, Z3 AS STRING, Z4 AS STRING)
'The following code determines the roots of a cubic equation
'defined by four complex coefficients:
'a z^3 + b z^2 + c z + d = 0
'The algorithm is an adaptation of one (CRoot3) from the Jean Pierre Moreau
'website: http://jean-pierre.moreau.pagesperso-orange.fr/Basic/croot3_bas.txt
'Several subroutines are used to perform elementary complex
'calculations and are shared with other DANSYSX programs.
'The global variables CMPN 1, 2, ..., 6 are also used to
'transfer complex variables between subroutines.

End Function

Most of the code will be in upper caps. This is not required by LibreOffice Basic, but I have found that I catch errors a lot quicker if everything is capitalized.

The Function statement names the program CompCube. The "Comp" part is a convention I established in DANSYS. Functions that deal with complex numbers begin with "Comp". The variables passed to the function (also called "parameters" or "arguments") are defined in the parentheses following the function name.

One of the flaws in LibreOffice Basic (all programming languages have weak spots) is that you can't transfer matrix values to and from functions and subroutines. (When I say "can't", I mean that I looked in all the resources I could find and tried to figure it out and couldn't - there may be a way but it remains a mystery to me.) I will have to transfer two value matrices (representing complex numbers), so I will store each value in a 1x2 array called CMPN1, CMPN2, to CMPN6. These arrays are defined as global variables (variables that can be used by all routines in DANSYSX and that won't lose their values between calls) which as designated in the Structures folder as, for instance:

Global CMPN1(1,2)

This generates a global array that has 1 row and 2 columns.

Global variables have to be defined outside of any function or subroutine. I collect them in the Structures folder.

I will also have to output three strings representing complex numbers so, to call this function from a spreadsheet, the user will select a 3x1 range of cells and type "=CompCube()' Inside the parentheses will either be four strings or the addresses of four cells containing strings.

I have written programs before that take complex numbers and have saved the code to do it in a KeyNote file of other handy code snippets that I use over and over. I will do that in the initialization section of the program.

Now, I go through the original code and figure out all the variables that I will need and declare them. At first guess, I come up with:

DIM A(2), B(2), C(2), D(2), U(2), V(2), W(2)
DIM DET(2), P(2), Q(2), SDET(2), U1(2), U2(2)
DIM V1(2), V2(2), ZT1(2), ZT2(2), Z(9,2)

The DIM statement is simple. It is just DIM followed by a list of variables separated by commas.

All the variables but the last are arrays with two places. Those are complex numbers.

Using my tried and true complex number decoders in the initialization section to load A, B, C, and D from Z1, Z2, Z3, and Z4, I wrote:

DIM A(2), B(2), C(2), D(2), U(2), V(2), W(2)
DIM DET(2), P(2), Q(2), SDET(2), U1(2), U2(2)
DIM V1(2), V2(2) ZT1(2), ZT2(2), Z(9,2)
DIM I AS INTEGER, ST AS STRING
DIM OutMat(3,1) AS STRING

OutMat is a convention I use to output arrays. I load all the data I want to display into an array and I set the function equal to that array. In this case, I'll be displaying three complex number strings - 3 rows and 1 column.

Notice that the last two variables are explicitly specified to be integers. LibreOffice allows you to specify variables as INTEGER (between values of -32768 and 32767), LONG (integers between -2147483648 and 2147483647), SINGLE (floating-point values between 3.402823E38 and 1.401298R-45), DOUBLE (floating-point values between 1.79769313486232E308 and 4.94065645841247E-324), CURRENCY (64 bit numbers displayed as fixed decimal numbers with 15 non-whole and 4 decimal places ranging from -922337203685477.5808 to 922337203685477.5807), STRING (character strings holding up to 65,535 characters), BOOLEAN (stores TRUE/1 or FALSE/0), and DATE (date and time values stores in an internal format.) Constants can also be declared using the keyword CONST (instead of DIM).

Variable types can also be specified by tagging a declaration symbol to the end of a variable name as follows:

Single !
Double #
Currency @
String $
For instance, DIM Money@ would declare the variable Money as the type Currency.

ST=""
FOR I=1 TO LEN(Z1)
IF MID(Z1,I,1)="+" OR MID(Z1,I,1)="-" THEN
A(1)=VAL(ST)
ST=""
END IF
ST=ST & MID(Z1,I,1)
NEXT I
A(2)=VAL(ST)

ST=""
FOR I=1 TO LEN(Z2)
IF MID(Z2,I,1)="+" OR MID(Z2,I,1)="-" THEN
B(1)=VAL(ST)
ST=""
END IF
ST=ST & MID(Z2,I,1)
NEXT I
B(2)=VAL(ST)

ST=""
FOR I=1 TO LEN(Z3)
IF MID(Z3,I,1)="+" OR MID(Z3,I,1)="-" THEN
C(1)=VAL(ST)
ST=""
END IF
ST=ST & MID(Z3,I,1)
NEXT I
C(2)=VAL(ST)

ST=""
FOR I=1 TO LEN(Z4)
IF MID(Z4,I,1)="+" OR MID(Z4,I,1)="-" THEN
D(1)=VAL(ST)
ST=""
END IF
ST=ST & MID(Z4,I,1)
NEXT I
D(2)=VAL(ST)

MSGBOX A(1)
MSGBOX A(2)
MSGBOX B(1)
MSGBOX B(2)
MSGBOX C(1)
MSGBOX C(2)
MSGBOX D(1)
MSGBOX D(2)

Each block of code beginning with ST works like this: First a blank string is generated in the string variable ST. Then, the following loop looks at each character of an input string Z. If the character is a + or a -, the first cell of a two place array is loaded with the numerical value of whatever is in ST and ST is emptied. If the character is anything else, it is tagged onto the right end of the string in ST and the next character comes up for review.  This goes on until each character of the input string (LEN(Z) is the number of characters in string Z) has been considered, then the numerical value of whatever is left in ST is loaded into the second cell of the two place variable.

The MID(Z,I,1) extracts 1 character from the ith position of string Z.

MSGBOX is a command that causes the following to be displayed in a message box. In this case, I use it to make sure that the input values are being transferred to the complex value arrays. They work so I'm happy. The MSGBOX commands will be deleted.

The next step requires that I add a subroutine that performs complex division and one that performs complex multiplication. I design these so that they work on the complex numbers stored in CMPN1 and CMPN2 and return an answer in CMPN3. You notice that the CMPN global variables act like a stack of registers for complex numbers. Here are CDIV and CMUL:

SUB CDIV()
'Subroutine to divide two complex numbers.
DIM CC(2), D AS DOUBLE, Z(2)

'Determine the value of the denominator
D=CMPN2(1,1)*CMPN2(1,1)+CMPN2(1,2)*CMPN2(1,2)
IF D<1e-15 THEN
MSGBOX "Complex divisiion by 0"
EXIT SUB
ELSE
CC(1)=CMPN2(1,1)
CC(2)=-CMPN2(1,2)
CMPN4(1,1)=CMPN1(1,1)
CMPN4(1,2)=CMPN1(1,2)
CMPN5(1,1)=CMPN2(1,1)
CMPN5(1,2)=CMPN2(1,2)
CMPN2(1,1)=CC(1)
CMPN2(1,2)=CC(2)
CMUL
Z(1)=CMPN3(1,1)
Z(2)=CMPN3(1,2)
CMPN2(1,1)=CMPN5(1,1)
CMPN2(1,2)=CMPN5(1,2)
CMPN3(1,1)=CMPN3(1,1)/D
CMPN3(1,2)=CMPN3(1,2)/D
END IF
END SUB

SUB CMUL()
'DETERMINE THE PRODUCT OF CMPN1 AND CMPN2
'SAVE THE RESULT IN CMPN3
CMPN3(1,1)=CMPN1(1,1)*CMPN2(1,1)-CMPN1(1,2)*CMPN2(1,2)
CMPN3(1,2)=CMPN1(1,1)*CMPN2(1,2)+CMPN1(1,2)*CMPN2(1,1)
END SUB

You will recognize most of the arithmetic operators. * is LibreOffice's multiplication operator (instead of x) and an exponent is denoted by the symbol ^. For instance, x squared would look like: x^2.

The difference between a function and a sub (or subroutine) is that the function returns a value and a sub does something but does not output a value. For instance, CMUL calculates the product of two complex numbers and loads them into a global array, but does not return the product to the program that called the sub. That program has to get the value from the global array. Here's the part of CompCube that uses CDIV and retrieves the result:

CMPN1(1,1)=C(1)
CMPN1(1,2)=C(2)
CMPN2(1,1)=A(1)
CMPN2(1,2)=A(2)
CDIV
U(1)=CMPN3(1,1)
U(2)=CMPN3(1,2)
MSGBOX U(1)
MSGBOX U(2)

This divides the complex number in C by the one in A. In order for CDIV to work C has to be loaded into CMPN1 and A has to be loaded into CMPN2. To call CDIV, I simply type CDIV. Then to get the result back as U, I load CMPN3 into U. Again, the MSGBOX commands display the result and, after I'm satisfied that CDIV works, I'll delete them. That's called "debugging". I prefer to debug as I go.

Now, to complete the calculation of P:

CMPN1(1,1)=B(1)
CMPN1(1,2)=B(2)
CMPN2(1,1)=B(1)
CMPN2(1,2)=B(2)
CMUL
V(1)=CMPN3(1,1)
V(2)=CMPN3(1,2)

CMPN1(1,1)=A(1)
CMPN1(1,2)=A(2)
CMPN2(1,1)=A(1)
CMPN2(1,2)=A(2)
CMUL
W(1)=CMPN3(1,1)
W(2)=CMPN3(1,2)

W(1)=3*W(1)
W(2)=3*W(2)

CMPN1(1,1)=V(1)
CMPN1(1,2)=V(2)
CMPN2(1,1)=W(1)
CMPN2(1,2)=W(2)
CDIV
ZT1(1)=CMPN3(1,1)
ZT1(2)=CMPN3(1,2)
P(1)=U(1)-ZT1(1)
P(2)=U(2)-ZT1(2)

You should be able to follow that. It's the same process I used to calculate c/a. It checks out, so I can continue.

The next piece of the puzzle is Q. It's a straight forward but long calculation (you see why they just teach the quadratic formula in high school and leave the cubic and quartic monsters for folks that are too curious for their own good.) That section of code looks like:

'Calculate q=2b^3/27a^3 + d/a - bc/3a^2
CMPN1(1,1)=B(1)
CMPN1(1,2)=B(2)
CMPN2(1,1)=V(1)
CMPN2(1,2)=V(2)
CMUL
W(1)=CMPN3(1,1)
W(2)=CMPN3(1,2)

W(1)=2*W(1)
W(2)=2*W(2)

CMPN1(1,1)=A(1)
CMPN1(1,2)=A(2)
CMPN2(1,1)=A(1)
CMPN2(1,2)=A(2)
CMUL
U(1)=CMPN3(1,1)
U(2)=CMPN3(1,2)

CMPN1(1,1)=U(1)
CMPN1(1,2)=U(2)
CMPN2(1,1)=A(1)
CMPN2(1,2)=A(2)
CMUL
V(1)=CMPN3(1,1)
V(2)=CMPN3(1,2)

V(1)=27*V(1)
V(2)=27*V(2)

CMPN1(1,1)=W(1)
CMPN1(1,2)=W(2)
CMPN2(1,1)=V(1)
CMPN2(1,2)=V(2)
CDIV
Q(1)=CMPN3(1,1)
Q(2)=CMPN3(1,2)

CMPN1(1,1)=D(1)
CMPN1(1,2)=D(2)
CMPN2(1,1)=A(1)
CMPN2(1,2)=A(2)
CDIV
U(1)=CMPN3(1,1)
U(2)=CMPN3(1,2)

Q(1)=Q(1)+U(1)
Q(2)=Q(2)+U(2)

CMPN1(1,1)=B(1)
CMPN1(1,2)=B(2)
CMPN2(1,1)=C(1)
CMPN2(1,2)=C(2)
CMUL
U(1)=CMPN3(1,1)
U(2)=CMPN3(1,2)

CMPN1(1,1)=A(1)
CMPN1(1,2)=A(2)
CMPN2(1,1)=A(1)
CMPN2(1,2)=A(2)
CMUL
V(1)=CMPN3(1,1)
V(2)=CMPN3(1,2)

V(1)=3*V(1)
V(2)=3*V(2)

CMPN1(1,1)=U(1)
CMPN1(1,2)=U(2)
CMPN2(1,1)=V(1)
CMPN2(1,2)=V(2)
CDIV
W(1)=CMPN3(1,1)
W(2)=CMPN3(1,2)

Q(1)=Q(1)-W(1)
Q(2)=Q(2)-W(2)

It's very repetitive and allows me to copy, paste,and modify most of it from earlier code. Then there is det:


'Calculate DET = q^2/4 + p^3/27

CMPN1(1,1)=Q(1)
CMPN1(1,2)=Q(2)
CMPN2(1,1)=Q(1)
CMPN2(1,2)=Q(2)
CMUL
U(1)=CMPN3(1,1)
U(2)=CMPN3(1,2)
U(1)=U(1)/4
U(2)=U(2)/4

CMPN1(1,1)=P(1)
CMPN1(1,2)=P(2)
CMPN2(1,1)=P(1)
CMPN2(1,2)=P(2)
CMUL
V(1)=CMPN3(1,1)
V(2)=CMPN3(1,2)

CMPN1(1,1)=P(1)
CMPN1(1,2)=P(2)
CMPN2(1,1)=V(1)
CMPN2(1,2)=V(2)
CMUL
W(1)=CMPN3(1,1)
W(2)=CMPN3(1,2)
W(1)=W(1)/27
W(2)=W(2)/27

DET(1)=U(1)+W(1)
DET(2)=U(2)+W(2)

Now, I need the square root of DET, and for that, I need the following subroutine that calculates the square root of the complex number stored in CMPN1.

SUB CSQRT()
'Determine the square root of CMPN1
'Save the result in CMPN3.
DIM R AS DOUBLE
R=SQR(CMPN1(1,1)*CMPN1(1,1)+CMPN1(1,2)*CMPN1(1,2))
CMPN3(1,1)=SQR((R+CMPN1(1,1))/2)
CMPN3(1,2)=SQR((R-CMPN1(1,1))/2)
IF CMPN1(1,2)<0 THEN CMPN3(1,2)=-CMPN3(1,2)
END SUB

The result is stored in CMPN3. SQR is the LibreOffice command to take the square root of the value in parentheses following it.

Now, the section of CompCube that calculates the square root of DET is:

'Calculate the square root of DET

CMPN1(1,1)=DET(1)
CMPN2(1,2)=DET(2)
CSQRT
SDET(1)=CMPN3(1,1)
SDET(2)=CMPN3(1,2)

V(1)=-Q(1)/2+SDET(1)
V(2)=-Q(2)/2+SDET(2)

If you look back up at the cubic formula, you will see where those last two statements came from.

So, I need the three cube roots of V and to get that, I need a subroutine to calculate the cube root of CMPN1 and store the results in CMPN2, CMPN3, and CMPN4, and that subroutine will need a function to determine the phase of CMPN1.

Here's the function CPHASE:

FUNCTION CPHASE(NUM,DENOM)
'Returns the phase of a complex number between -pi and pi
DIM PPI AS DOUBLE, CP AS DOUBLE
PPI=4*ATN(1)
IF ABS(DENOM)<1e-15 THEN
IF NUM<0 THEN
CPHASE=-PP1/2
ELSE
CPHASE=PPI/2
END IF
ELSE
CP=ATN(NUM/DENOM)
IF DENOM<0 THEN
CPHASE=CPHASE+PPI
ELSE
CPHASE=CP
END IF
END IF
END FUNCTION

Notice that the header says "FUNCTION" instead of "SUB". This code takes two arguments: NUM and DENOM, and returns a value. The value is determined by the "CPHASE=" statements. When you set a function's name equal to a value, you are outputing the value. To call the function, you set some variable equal to the function name (You'll see that below). ATN is a LibreOffice Basic function that calculates the arctangent of some number. ABS is the function that returns the absolute value of a number. This function is basically a decisiion tree that determines what value CPHASE will take.

If DENOM is less than 1E-15 (or 1 x 10-15), then if NUM is negative CPHASE is -pi/2, else CPHASE is pi/2. If DENOM is more than 1E-15, then if DENOM is negative, CPHASE is the arctangent of NUM/DENOM + pi, otherwise, it is just the arctangent of NUM/DENOM.

Here's the subroutine CCUBRT, which calculates the cube roots of a complex number (there will be three cube roots).

SUB CCUBRT()
'Calculates the three cube roots of CMPN1.
'The results are stored in CMPN2, CMPN3, and CMPN4
DIM PPI AS DOUBLE, R AS DOUBLE, T AS DOUBLE, TT AS DOUBLE

PPI=4*ATN(1)
R=SQR(CMPN1(1,1)*CMPN1(1,1)+CMPN1(1,2)*CMPN1(1,2))
R=R^(1/3)
T=CPHASE(CMPN1(1,2),CMPN1(1,1))
T=T/3
CMPN2(1,1)=R*COS(T)
CMPN2(1,2)=R*SIN(T)
TT=T-(2*PPI/3)
CMPN3(1,1)=R*COS(TT)
CMPN3(1,2)=R*SIN(TT)
TT=T+(2*PPI/3)
CMPN4(1,1)=R*COS(TT)
CMPN4(1,2)=R*SIN(TT)
END SUB

The statement that calls CPHASE is:

T=CPHASE(CMPN1(1,2),CMPN1(1,1))

It feeds the values stored in CMPN1 to the function and transfers the result to the variable T.

The part of CompCube that calculates the cubed roots of V is:

CMPN1(1,1)=V(1)
CMPN1(1,2)=V(2)
CCUBRT
U(1)=CMPN2(1,1)
U(2)=CMPN2(1,2)
U1(1)=CMPN3(1,1)
U1(2)=CMPN3(1,2)
U2(1)=CMPN4(1,1)
U2(2)=CMPN4(1,2)

U, U1, and U2 stores the resulting cubed roots.

And the part that calculates the cube roots of W is almost exactly the same except for the variables used:

W(1)=-Q(1)/2-SDET(1)
W(2)=-Q(2)/2-SDET(2)

CMPN1(1,1)=W(1)
CMPN1(1,2)=W(2)
CCUBRT
V(1)=CMPN2(1,1)
V(2)=CMPN2(1,2)
V1(1)=CMPN3(1,1)
V1(2)=CMPN3(1,2)
V2(1)=CMPN4(1,1)
V2(2)=CMPN4(1,2)

The next section collects all the possible roots of the equation. Only three will be appropriate, but the nine possible roots will all have to be tested.

'Find all possible solutions to the formula.

Z(1,1)=U(1)+V(1)
Z(1,2)=U(2)+V(2)
Z(2,1)=U(1)+V1(1)
Z(2,2)=U(2)+V1(2)
Z(3,1)=U(1)+V2(1)
Z(3,2)=U(2)+V2(2)
Z(4,1)=U1(1)+V(1)
Z(4,2)=U1(2)+V(2)
Z(5,1)=U1(1)+V1(1)
Z(5,2)=U1(2)+V1(2)
Z(6,1)=U1(1)+V2(1)
Z(6,2)=U1(2)+V2(2)
Z(7,1)=U2(1)+V(1)
Z(7,2)=U2(2)+V(2)
Z(8,1)=U2(1)+V1(1)
Z(8,2)=U2(2)+V1(2)
Z(9,1)=U2(1)+V2(1)
Z(9,2)=U2(2)+V2(2)

To test these values, I have to use them to evaluate the cubic equation. I use another subroutine for that, and that subroutine requires a function to return the absolute value of the complex number. I also realized that I needed to save the complex coefficients for this routine so I went back and did that in MatArray1, a global array I use to transfer matrices around.

'Load equation into MatArray1
REDIM MatArray1(4,2)
MatArray1(1,1)=A(1)
MatArray1(1,2)=A(2)
MatArray1(2,1)=B(1)
MatArray1(2,2)=B(2)
MatArray1(3,1)=C(1)
MatArray1(3,2)=C(2)
MatArray1(4,1)=D(1)
MatArray1(4,2)=D(1)

The sizes of dynamic arrays like the global MatArrays can be changed any time using the REDIM (redimension) command.

Here are the test routine and absolute value function:

FUNCTION CABS(ARG1, ARG2)
'This function returns the absolute value of a complex number
'ARG1+ARG2 i
CABS=SQR(ARG1*ARG1+AGR2*ARG2)
END FUNCTION

SUB CTEST()
'This subroutine tests a solution set of a cubic equation for appropriate
'values.
'The output is z1=a z^3 + b x^2 + c z + d
'In other words, it evaluates the original cubic equation.
'It gets the equation from MatArray1.
'The value of the complex variable is in CMPN4.
DIM U(2), V(2), W(2), Z1(2)

CMPN1(1,1)=MatArray1(1,1)
CMPN1(1,2)=MatArray1(1,2)
CMPN1(2,1)=CMPN4(1,1)
CMPN1(2,2)=CMPN4(1,2)
CMUL
U(1)=CMPN3(1,1)
U(2)=CMPN3(1,2)

CMPN1(1,1)=CMPN4(1,1)
CMPN1(1,2)=CMPN4(1,2)
CMPN1(2,1)=U(1)
CMPN1(2,2)=U(2)
CMUL
V(1)=CMPN3(1,1)
V(2)=CMPN3(1,2)

CMPN1(1,1)=CMPN4(1,1)
CMPN1(1,2)=CMPN4(1,2)
CMPN1(2,1)=V(1)
CMPN1(2,2)=V(2)
CMUL
W(1)=CMPN3(1,1)
W(2)=CMPN3(1,2)

Z1(1)=W(1)
Z1(2)=W(2)

CMPN1(1,1)=MatArray1(2,1)
CMPN1(1,2)=MatArray1(2,2)
CMPN1(2,1)=CMPN4(1,1)
CMPN1(2,2)=CMPN4(1,2)
CMUL
U(1)=CMPN3(1,1)
U(2)=CMPN3(1,2)

CMPN1(1,1)=U(1)
CMPN1(1,2)=U(2)
CMPN1(2,1)=CMPN4(1,1)
CMPN1(2,2)=CMPN4(1,2)
CMUL
V(1)=CMPN3(1,1)
V(2)=CMPN3(1,2)

Z1(1)=Z1(1)+V(1)
Z1(2)=Z1(2)+V(2)

CMPN1(1,1)=MatArray1(3,1)
CMPN1(1,2)=MatArray1(3,2)
CMPN1(2,1)=CMPN4(1,1)
CMPN1(2,2)=CMPN4(1,2)
CMUL
U(1)=CMPN3(1,1)
U(2)=CMPN3(1,2)

Z1(1)=Z1(1)+U(1)+MatArray1(4,1)
Z1(2)=Z1(2)+U(2)+MatArray1(4,2)

CMPN3(1)=Z1(1)
CMPN3(2)=Z1(2)

END SUB

After all that I had some small bugs (small bugs = disaster) and had to do some fine tuning. Debugging got rid of the bugs and my test subroutine was too sensitive for the tiny round off errors I produced so I had to change the test criterion:

IF X<0.001 THEN
to the more liberal:

IF X<10 THEN

Now, it works, and it work within input precision.

I'll be programming a lot more and it will be available at the Timeline. The basics of LibreOffice Basic are here and they should give you enough to figure out what I'm doing in future programs. I have a large, complex project coming up that will give me a chance to share some of my problem solving techniques with you.

--- Structuring programs ---

2016

My big project is a software package programmed into Calc, the LibreOffice spreadsheet component. The strength of this approach is that the statistics package already has all the utilities of a powerful spreadsheet. I call it DANSYS - the Data ANalysis System.

I'll be using DANSYS to talk about how to program in LibreOffice Basic. The manual, available at the LibreOffice website, is useful but there's a lot it doesn't explain how to do, and I've picked up a lot of tricks and workarounds over time. I'm working on an expanded version of DANSYS and I'll take you along on the journey.

I had to decide whether I wanted to just keep expanding DANSYS or make two versions: a basic version that does the most common statistical procedures and the expanded version that's much bigger and clunkier but will do many, many more cool things. I decided to go with the two version plan and both will be available on my other website (http://www.theriantimeline.com/excursions/labbooks) as I develop them. Currently, DANSYS and a statistics decision tree and glossary are available. I'm working on a user's manual for DANSYS and you'll see my progress on DANSYSX here.

Programming is a lot easier if you take a structured approach. Some languages (like Python) requires you to structure your programs. Others, like modern BASIC make it easy to structure programs but do not require it. Structured programming uses indentation to indicate levels of code (that will become much clearer as we go along). It also helps if you add notes to your code as you go along. This documentation serves two big functions: it reminds you what sections of code do if you need to go back and modify the code (which you often will), and it allows other people who use your code to understand what you've done.

I will admit that I sometimes slack off when it comes to documentation, but I will try to be responsible with the code in DANSYSX.

I try to maintain a five section structure for my LibreOffice Basic programs. The first section is the header. The first line of LibreOffice Baisc code names the program, tells whether the program is a subroutine or a function, and passes all the necessary information into the program.

The second section defines all the variables I'll be using in the program using DIM (DIMension) statements. I usually precede this section with a long comment explaining the program.

The third section initializes whatever variables need to start with some specific value.

The fourth section is where all the good stuff happens. It contains the works of the program.

The fifth and last section formats and outputs the data from the program.

Flow charts are useful to some people to help plan out complicated programs. I tend more to plot the way the program is supposed to work in pseudocode. Pseudocode describes the working of a program in descriptive English, line by line. For instance, if I want to add 1 to the variable bx over and over until it reaches 15, I might describe it with the following pseudocode:

bx=0
When bx reaches 15, jump out of the following loop
Add 1 to bx
Continue looping

More often, I type a scaffold of comments before I even start programming and then fill in the code. A comment in LibreOffice Basic looks like this:

'This is a comment. Notice that it begins with an apostrophe.
'LibreOffice Basic will ignore any statement beginning with an apostrophe.

[Note: If you've tried to use any of the in-text links to the Timeline, you will have found that they don't work anymore. That will be because I've moved it to a more secure site. I keep the links in the link section at the upper right of my webpages updated, and those do work.]

DANSYSX

DANSYSX Version 1.0 is finished and has been posted on the Timeline:

http://theriantimeline.com/DANSYSX.ods

This update includes extensive complex math and combinatorics functions, graphic routines that allow you to specify and generate graphic objects on the spreadsheet or programmatically, 2-way and 3-way crosstabulation routines, ntile conversion, and Monte Carlo generation of sampling statistics (a way to convert misbehaving statistics from non-normal distributions to more civilized values.)

There's also a programming language that allows you to code directly on a spreadsheet. It looks sorta like assembly language and is more a toy for me than anything else and, at present, doesn't work well enough to actually use for anything. The looping structure doesn't work yet, but, if you want to play around with it, the macros are accessible.

I'm working on a user guide to the basic DANSYS and I'll be working on one for this update of DANSYSX. When those are done, I'll post them. In the meantime, all the functions are documented on the DANSYS Functions sheet.

Have fun!