A short program
I'll start with a short routine I wrote to call up a spreadsheet page.
One of the big advantages Microsoft Office has over OpenOffice is that the object language in Microsoft Visual Basic is much more fully integrated into the programming language. In OpenOffice, the Uno system which handles the office is practically a different programming environment with a myriad of services that must be called up to do fairly simple tasks. Unfortunately, Microsoft Offfice doesn't support past programming language. I learned that the hard way in my practice when I programmed a test scoring and reporting utility for Microsoft Excel only to have them discontinue that version of Visual Basic. I couldn't upgrade my computer because I would lose my main program and all that effort.
There's a lot of European influence in OpenOffice so the Basic language maintains the Base 0 convention, meaning that counting, matrix positions and such all start at zero. I'm used to Base 1 so I change that in all my modules.
Here's the first few lines in the BASIC module:
REM ***** BASIC *****
Option Base 1
Sub FunctionList
Dim aSheet as object
aSheet=thisComponent.getSheets.getByName("DANSYS functions")
Controller = thisComponent.getcurrentController
Controller.setActiveSheet(aSheet)
End Sub
Now, line by line:
REM ***** BASIC *****
When I set up the Basic module using the Macro Organizer, it automatically placed a header in the module. Now, when I go into the Tools>Macros>Organize macros dialog, the Basic module is listed in the Standard folder.
I'm pretty sloppy at documenting programs but, since I'm not explaining here how my routines work but what the statements mean and how to use them, I'll be annotating each line. But you can (and I would certainly recommend that you be a more responsible documenter than I am) place comments anywhere you want in a program. In OpenOffice REM will do it but, more common, a apostrophe (') beginning the line will do the trick.
It's pretty obvious that actual users have had a lot to do with designing OpenOffice. The most commonly used symbols are ones that do not require the shift key.
Something that's sorta hard to find in the help files is how to continue or string out statements in a line. That's useful information, so, here it is.
If a line of code is longer than you want, you can continueit to the next line by ending the first line with a space and an underscore ( _). If you have several very short statements that you want to place on the same line, you can separate them by colons. Therefore:
x=5:y=1:If x>y then z=0
is the same as:
x=5
y=1
if x>y then z=0.
Option Base 1
This is the magical statement that changes the base (starting point for counting, matrix positions, etc.) from 0 to 1. If you use it, it goes at the very first of the module before the first program.
The first program in this module is called FunctionList.
Sub FunctionList
The first line in a program introduces it by name and indicates whether it is a sub (subroutine) or a function. A function can be called from a spreadsheet just like a built-in spreadsheet program but, otherwise, it can't change the spreadsheet document. I wanted something tat would move me to a sheet I had that listed all the functions I had written for my statistics spreadsheet (which I call DANSYS). A sub name may or may not be followed by stuff in parentheses (that's how you pass values to a subroutine or a function), but a function name is always followed by parentheses, even though there may not be anything in the parentheses. We'll get into that later. This sub only needs a name since I'm not going to send it any information. When it's called, it's just going to send me to another sheet.
Dim aSheet as object
It's a good idea to declare each variable you use by type. You don't always have to but not declaring can lead to unexpected consequences. In this case, I'm dealing with a spreadsheet object - a sheet - and objects have to be declared. aSheet is an object; it's the sheet that I want to be redirected to when I call this subroutine. Sheets, cells, cell ranges, drawing objects, documents, services - they're all objects, and there are many other objects beside.
aSheet=thisComponent.getSheets.getByName("DANSYS functions")
I want to specify which sheet I will be redirected to. In this case, it will be a sheet named "DANSYS functions" (that's a sheet in my spreadsheet document and I gave it the name when I added it into the document as a new sheet). Objects are assigned values in OpenOffice just like mathematical variables. I specified that the sheet was part of the current document using the thisComponent object. Things that follow and object separated by periods are methods and properties. thisComponet has a method called getSheets which returns a specific sheet or collection of sheets. I wanted to call up a sheet by name so I used the getByName method to call the sheet named Dansys functions. Notice that the name is placed in quotes. You almost always have to place text strings in quotes when using OpenOffice Basic.
Controller = thisComponent.getcurrentController
the current controller is one of those services I mentioned above. This statement accesses the controller for the current document and it is going to switch me over to the sheet I want. getCurrentController is another method available to thisComponent.
To tell the controller to move my, I use:
Controller.setActiveSheet(aSheet)
This tells the controller to make aSheet the active sheet so that I'm suddenly looking at "DANSYS functions".
All programs end with either "End sub" or "End function". This is a subroutine so I use:
End Sub
This is an older function so I had not yet begun capitalizing code.
I keep all my code in a document of a program called KeyNote, which is like an electronic notebook (you can find it on SourceForge) in case something wipes DANSYS. Windows and complex OpenOffice documents don't seem to play well together and my OpenOffice often crashes when I try to save. If I do lose the program, I can use the copies of the code in KeyNote to rebuild it. I also save chunks of code that I use often there so I don't have to remember complex Uno statements. It saves me a lot of trouble and headaches.
One of the big advantages Microsoft Office has over OpenOffice is that the object language in Microsoft Visual Basic is much more fully integrated into the programming language. In OpenOffice, the Uno system which handles the office is practically a different programming environment with a myriad of services that must be called up to do fairly simple tasks. Unfortunately, Microsoft Offfice doesn't support past programming language. I learned that the hard way in my practice when I programmed a test scoring and reporting utility for Microsoft Excel only to have them discontinue that version of Visual Basic. I couldn't upgrade my computer because I would lose my main program and all that effort.
There's a lot of European influence in OpenOffice so the Basic language maintains the Base 0 convention, meaning that counting, matrix positions and such all start at zero. I'm used to Base 1 so I change that in all my modules.
Here's the first few lines in the BASIC module:
REM ***** BASIC *****
Option Base 1
Sub FunctionList
Dim aSheet as object
aSheet=thisComponent.getSheets.getByName("DANSYS functions")
Controller = thisComponent.getcurrentController
Controller.setActiveSheet(aSheet)
End Sub
Now, line by line:
REM ***** BASIC *****
When I set up the Basic module using the Macro Organizer, it automatically placed a header in the module. Now, when I go into the Tools>Macros>Organize macros dialog, the Basic module is listed in the Standard folder.
I'm pretty sloppy at documenting programs but, since I'm not explaining here how my routines work but what the statements mean and how to use them, I'll be annotating each line. But you can (and I would certainly recommend that you be a more responsible documenter than I am) place comments anywhere you want in a program. In OpenOffice REM will do it but, more common, a apostrophe (') beginning the line will do the trick.
It's pretty obvious that actual users have had a lot to do with designing OpenOffice. The most commonly used symbols are ones that do not require the shift key.
Something that's sorta hard to find in the help files is how to continue or string out statements in a line. That's useful information, so, here it is.
If a line of code is longer than you want, you can continueit to the next line by ending the first line with a space and an underscore ( _). If you have several very short statements that you want to place on the same line, you can separate them by colons. Therefore:
x=5:y=1:If x>y then z=0
is the same as:
x=5
y=1
if x>y then z=0.
Option Base 1
This is the magical statement that changes the base (starting point for counting, matrix positions, etc.) from 0 to 1. If you use it, it goes at the very first of the module before the first program.
The first program in this module is called FunctionList.
Sub FunctionList
The first line in a program introduces it by name and indicates whether it is a sub (subroutine) or a function. A function can be called from a spreadsheet just like a built-in spreadsheet program but, otherwise, it can't change the spreadsheet document. I wanted something tat would move me to a sheet I had that listed all the functions I had written for my statistics spreadsheet (which I call DANSYS). A sub name may or may not be followed by stuff in parentheses (that's how you pass values to a subroutine or a function), but a function name is always followed by parentheses, even though there may not be anything in the parentheses. We'll get into that later. This sub only needs a name since I'm not going to send it any information. When it's called, it's just going to send me to another sheet.
Dim aSheet as object
It's a good idea to declare each variable you use by type. You don't always have to but not declaring can lead to unexpected consequences. In this case, I'm dealing with a spreadsheet object - a sheet - and objects have to be declared. aSheet is an object; it's the sheet that I want to be redirected to when I call this subroutine. Sheets, cells, cell ranges, drawing objects, documents, services - they're all objects, and there are many other objects beside.
aSheet=thisComponent.getSheets.getByName("DANSYS functions")
I want to specify which sheet I will be redirected to. In this case, it will be a sheet named "DANSYS functions" (that's a sheet in my spreadsheet document and I gave it the name when I added it into the document as a new sheet). Objects are assigned values in OpenOffice just like mathematical variables. I specified that the sheet was part of the current document using the thisComponent object. Things that follow and object separated by periods are methods and properties. thisComponet has a method called getSheets which returns a specific sheet or collection of sheets. I wanted to call up a sheet by name so I used the getByName method to call the sheet named Dansys functions. Notice that the name is placed in quotes. You almost always have to place text strings in quotes when using OpenOffice Basic.
Controller = thisComponent.getcurrentController
the current controller is one of those services I mentioned above. This statement accesses the controller for the current document and it is going to switch me over to the sheet I want. getCurrentController is another method available to thisComponent.
To tell the controller to move my, I use:
Controller.setActiveSheet(aSheet)
This tells the controller to make aSheet the active sheet so that I'm suddenly looking at "DANSYS functions".
All programs end with either "End sub" or "End function". This is a subroutine so I use:
End Sub
This is an older function so I had not yet begun capitalizing code.
I keep all my code in a document of a program called KeyNote, which is like an electronic notebook (you can find it on SourceForge) in case something wipes DANSYS. Windows and complex OpenOffice documents don't seem to play well together and my OpenOffice often crashes when I try to save. If I do lose the program, I can use the copies of the code in KeyNote to rebuild it. I also save chunks of code that I use often there so I don't have to remember complex Uno statements. It saves me a lot of trouble and headaches.