OpenOffice's abbreviated Basic
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.
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.