One more time
Self portrait
wolfvanzandt
Okay. I didn't loose as much as I thought and my external hard disk just needed to be unplugged and plugged back in. What I did loose, though, was my graphing routines. They were a pain in the neck and I believe I will be quite content to use other programs for graphing.

I've noticed that LibreOffice is a lot quicker to open than OpenOffice with much less fanfare.

I've trimmed DANSYS down somewhat. The massive documentation is one thing that has been causing a lot of the bugginess. I took that out and am going to just keep a list of function and their syntax. I'll also be using DANSYS as a tool for my tutorials and will offer it on my website as soon as I can recode the basic functions. Then I will update it occasionally with more advanced functions.

Eeeeeeee!Krsh! Crash........burn
Self portrait
wolfvanzandt
Oh well. There goes another hobby.

I'm not very upset. It's not like I don't have other things going on.

Fact is, I knew that both OpenOffice and LibreOffice were unstable (at least, on a Windows OS) so I figured that the more complex these documents became, the more likely they were to disappear. DANSYS bit the dirt on December 16, 2015 (RIP).

It was pretty simple - LibreOffice just wouldn't open it - siad it was corrupted. I have a  backup from March but I've done a lot since March and I'm not about to go through all that again. I have more recent text backups of the macros and I'll probably use them in the future, but I have other statistics software, so I don't really need DANSYS.

What did I get from the exercise? Well, I became quite confident in my programming ability. I'm about to delve into Python. I will also be able to reuse the routines I've programmed for my tutorials (which get backed up everytime I update my website.

Next year, when I can see again (after glasses) I plan to start back to playing in earnest - real world playing. I'm ready to jump in to the world of the regional council (a topic I drew at random). That will involve a bus trip and some public meetings. After that, I'll draw another topic at random.

If you are having problems with macro programming for Libre- or Open-Office, drop me a line and I might be able to help you figure it out.

Is it?
Self portrait
wolfvanzandt
You can modify existing routines to do different jobs. The last program found all the prime factors in a number. This one tells you if a number is a prime.

Function IsPrime(n)
'REM Tests if a number (n) is a prime number.
'If it is, returns the Boolean value, True; otherwise, False.
Dim d as integer, i as integer, v as Boolean
v=True
'Test if multiple of 2
d=n-2*int(n/2)
if d=0 then
    goto IsPrime100
end if
'Test if multiple of 3
d=n-3*int(n/3)
if d=0 then
    goto IsPrime100
end if
'Test if multiple of 6i-1 and 6i+1 to sqr(n)+1
for i=6 to int(sqr(n))+1 step 6
    d=n-(i-1)*int(n/(i-1))
    if d=0 then
        goto IsPrime100
    end if
    d=n-(i+1)*int(n/(i+1))
    if d=0 then
        goto IsPrime100
    end if
next i  
IsPrime=v
Exit Function
IsPrime100: v=False
IsPrime=v
End Function

I write code like this t use as tools for other projects. There are a few "Is" functions in LibreOffice Basic but there are many times that I want to test a value for some characteristic or another (such as "Is it prime") and these macro languages are quite lacking in this respect, and LibreOffice and OpenOffice lacks even in comparison to Microsoft Office, so I have to plug the holes.

The main difference between this and the other program is that this one doesn't collect prime factors and all it returns is "True" or "False".

One of the aggravating things about these macro language is that the "True" string that a routine might reurn and the TRUE Boolean value that a spreadsheet function recognizes are not the same thing, so you still have to process the Boolean values returned by homegrown programs.

Help!
Self portrait
wolfvanzandt
Well, excuuuuuuuuuuse me!

There are help files for LibreOffice. You can find a download for them on the Home page for LibreOffice. They work just like the OpenOffice help files (They're called the Help Pack.)

OpenOffice vs. LibreOffice
Self portrait
wolfvanzandt
Not really. That topic, it seems, has been overdone and LibreOffice seems to come out on top every time.

Frankly, I'm pretty fed up with going to the OpenOffice help forums and seeing them abuse people.

I tried to update my OpenOffice and I couldn't get around the "close the quickstarter" warning, so I took the only other advice the forum gave and uninstalled the older version of OpenOffice, which only meant that I no longer had OpenOffice and the new version still wouldn't install. Might be the best thing that's happened to me on the computer in a long time.

I installed LibreOffice instead. It works pretty much the same as OpenOffice and my macros (that I've been working on over a year) still seem to work okay. Best I can tell from other articles on the Web, LibreOffice is more stable and better maintained and supported than OpenOffice. I'll probably have more to say about it as time goes on. The main drawback I've run into is that the help files are only online and they don't seem very detailed even though the examples seem to be more substantial.

But, from now on, I guess these programming pposts will be about LibreOffice.

Prime factors
Self portrait
wolfvanzandt
Here's a routine to finnd the prime factors of a number:

Function PrimeFact(n)
'REM Returns the prime factors of n. Also gives a message
'of how many factors there are in case you need to reselect
'the output range.
Dim OutMat(1,int(sqr(n)+1))
Dim d as double, c as integer, i as integer
c=1
'Test if multiple of 2
PF50:    d=n-2*int(n/2)
if d=0 then
    OutMat(1,c)=2
    c=c+1
    n=n/2
    goto PF50
end if
'Test if multiple of 3
PF100: d=n-3*Int(n/3)
if d=0 then
    OutMat(1,c)=3
    c=c+1
    n=n/3
    goto PF100
end if
'Test for divisors 6i-1 and 6i+1 up to sqr(n)
'Prime numbers are of the form 6i-1 or 6i+1
For i=6 to sqr(n)+1 step 6
PF150:    d=n-(i-1)*int(n/(i-1))
    if d=0 then
        OutMat(1,c)=i-1
        c=c+1
        n=n/(i-1)
        goto PF150
    end if  
PF200: d=n-(i+1)*int(n/(i+1))
    if d=0 then
        OutMat(1,c)=i+1
        c=c+1
        n=n/(i+1)
        goto PF200
    end if
next i
if n>1 then
        OutMat(1,c)=n
        c=c+1
end if
msgbox str(c-1)&" factors were found"
PrimeFact=OutMat
End Function

Many programmers will tell you that it's "inelegant" to use GoTo statments. It's sort of a moot point anymore. Back when your computer had 256K memory, you had to be very careful about how big your programs were, and speed was also an issue. With today's computers, speed isn't that much of an issue and memory is measured in millions and billions of bytes, So, frankly, I just want programs to work; therefore, I'm a rather inelegant programmer. I'm sure any of these routines can be improved, but I've been making OpenOffice do plenty that regulars on the forum were telling people could not or should not be done with a spreadsheet.......oooooh, I feel pretty good about my programming.

There are two big reasons I use GoTo statements. First, I don't have to strain my brain to figure out all the "elegant" looping structures. Second, following the tenant of programmers from the earliest history of programming, I don't "reinvent the wheel." If there's a program out there that can do the job, I have no problem with rewriting it to work on my spreadsheet. Much of the early Fortran and Basic programs were repleat with Goto statements.

The structure of Goto statements in OpenOffice is Goto linelabel and, at the place you want the programming to go to, you start the line with the line label. You don't have to follow the line label with anything (in some older languages, you did and the default was the Continue statement). Best I can tell, OpenOffice doesn't even have a Continue statement.

Note that in OpenOffice Basic, you can name your line labels anything but, on the line that they label, you have to end them with a colon.

With most languages today, there are a lot of statements to jump in and out of loops, got straight to the next cycle of a loop, and so forth. There isn't a lot of that in OpenOffice Basic, so the Goto statement comes in handy.

msgbox str(c-1)&" factors were found"

If you want your program to talk to you, there is the message box. It will display just about any result you want in a dialog box that you can then close so the program can get on with it's business. MSGBOX doesn't equal anything. You just type it and a space and follow it with what ever you want to display. I included it in this program so it could tell me how many prime factors it found; thereby saving  me the trouble of counting them. I can be abominably lazy sometimes.

Note that you can string strings together using the & operator. The STR function takes a number and turns it into a text string. A similar function, FORMAT, lets you change the way the resulting string appears. With STR, if the number is positive, it throws a blank onto the front of the string, whether you ant it or not. With FORMAT, you have to include instructions about how you want the result to look (and that can be involved - especially in tarcking down the right code), but you can be flexible. I didn't care about the leading blank here so I took the easy way. If the program finds seven factors, the message would be "7 factors were found."

I have several personal conventions. OutMat is one of them. Many of my routines gather results that will be displayed on the spreadsheet as a matrix. It's easy if you just load them all into a matrix called "OutMat" and make the last line in the program (name of routine)=OutMat. Arrays (AKA "matrices") in OpenOffice Basic can contain any kind of variable and can be mixed.I will often output arrays that have values and strings to tell what those values are.

Here, I dimension OutMat to be a row of numbers.

Dim OutMat(1,int(sqr(n)+1))

The first number in the parentheses is the number of rows, the second is the number of columns. I used int(sqr(n)+1) (which means the integer portion of one more than the square root of n) because there will be no more than that many prome factors in n.

Remember the quadratic function?
Self portrait
wolfvanzandt
Here it is in OopenOffice Basic.

Function Quadratic(a,b,c)
Dim OutMat(2), Disc as double, Coeff as double, Coeff2 as double
Dim StrBuild as String
'REM Quadratic takes three numbers, the coefficients of a quadratic equation
'and returns an array of two numbers (or strings if a result is complex)
'representing the roots of the equation. It uses the quadratic formula to
'calculate the result.
Disc =b^2-(4*a*c)
Coeff=-b/(2*a)
If Disc>=0 then
    OutMat(1)=Coeff+(Sqr(Disc)/(2*a))
    OutMat(2)=Coeff-(Sqr(Disc)/(2*a))
Else
    Coeff2=Sqr(Abs(Disc))/(2*a)
    StrBuild=Str(Coeff)+"+"+Coeff2+"i"
    OutMat(1)=StrBuild
    StrBuild=Str(Coeff)+"-"+Coeff2+"i"
    OutMat(2)=StrBuild
end if
Quadratic=OutMat
End Function

Nothing particularly unusual here, but note that, if there is an imaginary output, you have to distinguish between that and a real number and output a string. That's what the If....Then....Else branch does. Both OpenOffice and Office uses strings to represent complex numbers.

Openers
Self portrait
wolfvanzandt
There are several ways to open things from inside a OpenOffice document. The most accessible is the Hyperlink, but hyperlinks are limited. For instance, you can't open programs with them. Here are two short routines that allow you to open most other programs and other documents.

I wrote the GoToGrapgCalc program because I use it a lot from DANSYS and I wanted to be able to go into a menu and click on a command instead of having to go outside the spreadsheet. You can attach macros to menus, toolbars, drawing objects, or events, so you can open this kind of rouine from just about anywahere in an OpenOffice document.

The Shell command only requires a path to the program you want to open and that can easily be obtained by right clicking on a shortcut to the program and opening the properties. The path is already selected so all you have to do is press Ctrl+C to paste it into the Shell program. You can also do fancy things like attach switches to the path s you can automatically have the program do things on openning, or you can pull out responses from the program but you have to know something about the inner workings. Sometimes, that comes with the program in the help files or documentation, and soometimes you have to do a Web search to find out how to do what you want to do.. Shell works with most programs. I've had a few Java programs I couldn't open like this but I've been able to open all executable files (files with an .exe extension).

Sub GoToGraphCalc
Shell("C:\Program Files (x86)\GraphCalc\GrphCalc.exe")
End Sub

The next routine opens an OpenOffice file. It will open most other kinds of documents if you feed it a URL style file path. It's not intuitive, considering that you have to use the StarDesktop, but it's a simple procedure.

Sub GoToUtilities()

Dim Doc As Object
Dim Url As String
Dim Dummy() 'An (empty) array of PropertyValues

Url = "file:///C:/Users/Wolf/Desktop/Utility.ods"

Doc = StarDesktop.loadComponentFromURL(Url, "_blank", 0, Dummy)
End Sub

De-basing numbers
Self portrait
wolfvanzandt
So, if you want to go in the other direction and convert a decimal number to a number in a different base system, here's how you can do it.

Function Dec2Basen(arr1 as double, arr2 as integer)
'REM This function converts a base-10 number to a number in any base.
Dim I as integer, BigPower as integer, Pwr as integer
Dim Quo as double, Hl as integer, Remainder as double
Dim OutString as string, FinalStr as string


'REM First, determine the first power of the base larger than the
'base-10 number.
BigPower=0
Do until arr2^BigPower>arr1
    BigPower=BigPower+1
Loop
'Now, step powers back to -3. Start with BigPower-1 to find the first digit to
'the left.
If arr1>0 then
    Quo=Int(arr1)/(arr2^(BigPower-1))
    Hl=Int(Quo)
    Remainder=Quo-Int(Quo)
    Remainder=Remainder*(arr2^(BigPower-1))
else
    Quo=arr1/(arr2^(BigPower-1))
    Hl=Int(Quo)
    Remainder=Quo-Int(Quo)
end if
    if Hl>9 then
        OutString=ValueAlpha(Hl-9)
    else
        OutString=Str(Hl)
    End if
Pwr=BigPower-2
'Now, cycle until Pwr=-3 finding each digit in turn
do until Pwr=-3
    'Find
    If Pwr >-1 then
        Quo=Int(Remainder)/(arr2^Pwr)
        Hl=Int(Quo)
        Remainder=Quo-Int(Quo)
        Remainder=Remainder*Hl
    else
        If pwr=-1 then
            Quo=(arr1-Int(arr1))/(arr2^Pwr)
            Hl=Int(Quo)
            Remainder=(Quo-Int(Quo))/10
            OutString=OutString+"."
        Else
            Quo=Remainder/(arr2^Pwr)
            Hl=Int(Quo)
            Remainder=Quo-Int(Quo)          
        End if
    end if
    if Hl>9 then
        OutString=OutString+ValueAlpha(Hl-9)
    else
        OutString=OutString+Str(Hl)
    End if
    Pwr=Pwr-1
Loop
FinalStr=""
For I=1 to Len(OutString)
    If Mid(OutString,I,1)<>" " then
        FinalStr=FinalStr+Mid(OutString,I,1)
    End If
Next I
Dec2Basen=FinalStr
End Function

Again, if you work in another base, you pretty much have to play with strings because spreadsheet arithmetic is based on decimal math. You see most of the logic structures in this code - For...Next loops, If....Then.....Elseif......Else....End If branches, and also one we didn't use the last time, Do loops.

Do loops are more flexible than For....Next loops, and they are a little more confusing. The beginning statement is a Do statement; the end is a Loop statement. Either of them can have an Until or While provision and all four variations produce different outcomes.

"While" instructs the program to keep doing the code between the Do and Loop statements while a certain provision is true. If it senses that the provision is true on a Do statement, it will jump out of the loop before it runs through the process again. To make it run one more time, you can place the While condition on the Loop statement.

Using "Until", you keep the loop going until some provision is true. At that point, the looping stops. I will usually get the logic wrong the first time around and have to "adjust" which statement the provision is attached to during the debugging process. Let's look at one of the Do loops.

This is the part that figures out which digit to use. It goes until a power of -3 is reached (otherwise, on a repeating decimal like 0.3333.... or a nonrepeating, infinite decimal like pi, it would go on forever. Infinite loops are not your friend. Three decimal places are okay for government work, anyway.

do until Pwr=-3                                                So, this loop is going to repeat until the Pwr variable, the variable that senses
    'Find                                                            which decimal place the code is working on, reaches -3. The Pwr variable is
    If Pwr >-1 then                                            decremented every loop on the next to last statement below.
        Quo=Int(Remainder)/(arr2^Pwr)
        Hl=Int(Quo)                                              This loop contains two If.....Then structures inside it - two are nested.
        Remainder=Quo-Int(Quo)
        Remainder=Remainder*Hl
    else
        If pwr=-1 then
            Quo=(arr1-Int(arr1))/(arr2^Pwr)
            Hl=Int(Quo)
            Remainder=(Quo-Int(Quo))/10
            OutString=OutString+"."
        Else
            Quo=Remainder/(arr2^Pwr)
            Hl=Int(Quo)
            Remainder=Quo-Int(Quo)          
        End if
    end if
    if Hl>9 then
        OutString=OutString+ValueAlpha(Hl-9)
    else
        OutString=OutString+Str(Hl)
    End if
    Pwr=Pwr-1                                          This is where the Pwr variable is decremented and the Do statement senses whether it has
Loop                                                        reached -3 yet. When it does, the loop ends right there and processing jumps to the next
                                                                statement after the Loop statement.

OpenOffice's abbreviated Basic
Self portrait
wolfvanzandt
I like a lot of things about OpenOffice but the Basic language they use for their macros is a little embarrassing. For instance, I miss the Data....Read statements. In Basic (regular Basic, that is), if you want to load a bid array with constants, you use a Data statement and follow it up with a list of the values separated by commas. Then you set up a For....Next loop where a Read statement picks up each value from the Data statement in turn and loads it into the array. With OpenOffice Basic, you have to stack up statements like:

A(1,1)=2.5
A(1,2)=0.75
A(1,3)=0.13
etc.
etc.

Just imagine loading a 100 element array with constant values. I did it once and then I programmed a work-around. That's what I do when OpenOffice Basic lets me down. So here are my DATAIN and READDATA programs.

 SUB DATAIN(ARR AS STRING)
 'Stores a string of values into a Global array
  DIM I AS LONG, J AS LONG, N AS LONG, ST AS STRING, T AS LONG
  DATAPTR=1
 N=0
 'Count number of commas and add 1
 FOR I=1 TO LEN(ARR)
     IF MID(ARR,I,1)="," THEN N=N+1
 NEXT I
 N=N+1
REDIM DATAVEC(N-1,1-1)
'Store data in DataVec (string or number)
T=1
ST=""
FOR I=1 TO LEN(ARR)
    IF MID(ARR,I,1)="," THEN
        IF ASC(MID(ST,1,1))>=48 AND ASC(MID(ST,1,1))<=57 THEN
            DATAVEC(T,1)=VAL(ST)
        ELSE
            DATAVEC(T,1)=ST
        END IF
        ST=""
        T=T+1
        GOTO DT1
    END IF
    ST=ST+MID(ARR,I,1)
DT1:  
NEXT I  
IF ASC(MID(ST,1,1))>=48 AND ASC(MID(ST,1,1))<=57 THEN
    DATAVEC(UBOUND(DATAVEC,1),1)=VAL(ST)
ELSE
    DATAVEC(UBOUND(DATAVEC,1),1)=ST
END IF
END SUB

FUNCTION READDATA()
'Reads data from DataVec into a variable or array
'Read next value or string. Use DataPtr to keep place.

READDATA=DATAVEC(DATAPTR,1)
DATAPTR=DATAPTR+1
End FUNCTION

To use DATAIN, you type a DATAIN statement followed by a string of values in oarentheses and quotes. It looks like this:

DATAIN("2.5,0.75,0.13.............22")

The value string can be pretty long and you can't break it with and underscore to continue it on another line. Also, each DATAIN statement must be followed by the READDATA statement that reads it. Still, it's a heck of a lot better than the interminable A(...)=...
statements.

The DATAIN program is a subroutine so it can't be used as a spreadsheet function. The READDATA program, on the other hand, is a function.

Both of these programs use a global array called DATAVEC and a global variable called DATAPTR to save the values. READDATA goes into DATAVEC, one element at a time (DATAPTR keeps up with the next value to be read) and calls it into a variable. It looks like this:

variable=READDATA()

Nothing goes in the parentheses.

I guess I should say something about Global variables.

There are several kinds of variables in terms of the time they will hold onto values. Local variables will only hold values while the particular program is running. That's the usual variables you use in a program. When the program stops, the values are gone. Global variables are declared outside of a program and they will hold values as long as OpenOffice is open. The values go away when you close OpenOffice. I have a module where I store my global variables - I call it Structures. To declare a variable as a global variable you type one line. In the case of DATAVEC and DATAPTR:

Global DataVec()
Global DataPtr as integer

You declare them just like any other variable except yuo use a Global statement and you do it outside a program. Each time DATAVEC is used, it will store a different amount of information, so you can't dimesnion it when you declare it. You can redimension it each time in the program that uses it using a REDIM statement, which works just like a DIM statement. REDIM will clear all past values from the array. One of the weirdities of OpenOffice Basic is that, when you use a REDIM statement, it gives you a bonus. If the statement is REDIM DATAVEC(3,5) you will end up with an array with 4 rows and 6 columns. If you really want a 3x5 array, you have to dimesnion it with REDIM DATAVEC(3-1,5-1). If you want a 3x1 column vector, you have to use REDIM DATVEC(3-1,1-1). Weird, but, yeah.....

The help files cover variable types pretty well. You really need to know their characteristics. For instance, an Integer type will only work between -32768 and 32767. I have gone beyond these limis on a For...Next loop and...."Why won't it do what I want it to do?". Well, there is a Long type which will work with integer values from -2147483648 to 2147483647. What the help files don't tell you is how to get a variable to save a value even after OpenOffice has closed.

My DANSYS spreadsheet has hidden sheets that save all kind of information. If you place a value in a spreadsheet cell, it doesn't go away until you change it. Accessing a cell from a program is a little involved but we will get to that eventually. If you can't wait, drop me a line and I'll spill the beans.

?

Log in