This page is one of several trying to help you get the most out of the splendid Open Office. They are allied with a larger set concentrating on ooBase.
Remember that Open Office, including ooBase, is free! But don't let that fool you. And it's not new. Big organizations, government and civilian, are adopting it as their standard office suite... and saving million$, and still Getting The Job Done.
There's more about ooBase in the main index to this material.
This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is more fully explained, and there's another tip, at my Power Browsing page.)
Page contents © TK Boyd, Sheepdog Software ®, 2/06-4/10.
This page is "scraps" left over after a major re-write of some material... But also on the page you are reading is a whole new example macro, not duplicated elsewhere!.
The other pages which were given the best bits of what this once was are....
I have tried not to duplicate too much of the information that made its way into any of the above, but some overlap is inevitable. Check out at least the first two of the above before continuing with the page you are looking at now. If you have already read them, this page should be helpful, if only to consolidate your knowledge and skills.
OpenOffice is a wonderful software suite. There are many, many things that it does extremely well. But there comes a time when you "really need" something that Open Office has not provided a button for.
Fear not! All is not lost! A macro can often meet your need.
But be careful what you wish for. Macros are another "lunch" which is not free.
A macro is a recorded sequence of instructions. Anything you can do "by hand" (and more) can be put into a macro. If you have some tedious chore that you do over and over, you may want to consider creating a macro to simplify your life.
Bear with me, and pretend that to guard against data loss you maintain three versions of some file. Let's say you keep the most current version in a file called "GoodStuff". And let's say that your next older version is called "GoodStuffDad", and the oldest version you have is called "GoodStuffGrandad".
From time to time, you....
Under such a scheme, from time to time, you will briefly have identical contents in "GoodStuffDad" and GoodStuff", but you are always in a position to get back to a recent version of the file if something goes horribly wrong as you edit "GoodStuff".
(This is, by the way, a complex system. It is a good system, especially if the backup copies are held on different storage devices, but it would want automating if a human was expected to use it. Having said that, I do use a very similar system, by hand, for some of my files.)
If you wanted, you could do all of the above by hand... with all of the attendant chances for typos. Alternatively, you could create a macro, and turn the details of the chore over to your machine. Whatever we may say about the various shortcomings of computers, they are generally pretty good at narrowly defined chores.
A tutorial explaining how to do that file backup work by a macro is available as a separate page.
So much for a simple macro that merely automates something you could, if you had the patience, do by hand. Probably of more interest are the macros which do things which you cannot do "by hand." We will be making one of those.
Why is there always a "but..."?
One of the things which has kept me from embracing macros is that there is an overhead. You have to manage your macros. They have to be kept where they are accessible... but not so "accessible" that they are underfoot when not needed... or leave "scraps" in the wake of experiments.
I'm not entirely happy with my "solution" to the problem, but give it a try. It has merits which may not be immediately evident. I wrote my first computer program in 1968, and have developed some sense of what works, what doesn't. There are many possible solutions, each with pros and cons. What I propose in the following should keep your system manageable, hitting the "pros" and avoiding "cons".
My system is fully explained in the other essays that were listed a the start of this page. The "new" macro example set out below also uses the system I like.
By the way, this tutorial was written using ooBase version 3.0 on Windows XP, but things should work the same way under other post version 1 OpenOffice installations, and under other operating systems. Great news: From version 3.1, you can store macros in an ooBase database, something you couldn't do before. Open Office upgrades have almost always gone smoothly for me. If you are in a position to take the inevitable risks, I would recommend that you upgrade to version 3.1.
If you tried this tutorial before 29 Mar 2010 and it "wouldn't work" for you... try it again? I've gone through it again, and tidied some bits and pieces. I thought it was working previously, but at least one thing wasn't as clear as it might have been, and a new bit has been added which makes something work more easily, more transparently... if it worked at all before! (Oops. Sorry.)
A little preliminary matter: From the menu, invoke Tools | Options. Within the "OpenOffice.org" part of the options, on the "Security" page, you need to click the "Macro Security..." button.
That opens a dialog where you can set your security to "Medium". This will allow you to elect, on a document by document basis, to allow macros to run.
Right. That done? Onward.
Start a new OpenOffice Writer document. Yes- a simple text document. (We'll get to macros with ooBase later.)
Use the menu: View | Toolbars | Form Controls. If "Form Controls" doesn't have a tick in front of it, click on it. It may not be docked as below (but you can change that by simple dragging. By the way, don't let the name fool you: The toolbar doesn't let you control (verb) the form. It supplies controls (noun) that you may wish to place on a form you are designing.
You need the button that is second from the left on the Open Office 3.1 Form Controls bar... a set-square and pencil. It will be identified as "Design Mode On/ Off" if you hover the mouse pointer over it.
The "Design Mode On/ Off" item in the toolbar is a toggle... it can be "up" or "down". Clicking it changes your environment between the two possible states:
"Ordinary" ooWriter work is done in the "normal" (design mode off) state. You may, of course, undertake some "design" work during normal operations, e.g. changing the font of your text.... but this is not what OpenOffice is talking about when it speaks of "design mode".
When we are in OpenOffice's design mode, we can add things to the "document" which change it from a mere "piece of paper" with writing and graphics on it. We very rapidly arrive at something which is almost a Windows "program", or "application".
In this tutorial, we're going to create something quite simple. But getting to the point of being able to do that is not trivial. The good news is that once you can do what we're going to do in this tutorial, you will have the basic skills to make much more rapid progress with more significant projects.
Get yourself into "design mode". The "Design Mode On/ Off" icon will have a border and a background. Many other icons on the toolbar will become "more interesting"... have colors, etc, to show that they are no longer disabled.
Find the "Check Box" icon. (At one point, it was the fifth icon in the illustration above.) Click on it, so that it is "down". Then move your mouse pointer to over the "piece of paper" you have on the screen. Press your mouse button down, keep it down, move the pointer about 3 cm to the right and 1 cm down the page... you should see a box "drag out". Release your mouse button, and a checkbox labeled "Check Box" should be visible on your "piece of paper", which I will henceforth call your "form", the OO name for the "thing" we are working with. (That skill is called "dragging out" a new item on the form.)
Just before we proceed: A matter of terminology. I'm going to call the "check box" a "tick box", and the little mark a "tick", rather than a "check". The word "check" is used too often as a verb, and using it as a noun as well can lead to confusion.
Click on the "Design Mode On/ Off" icon to leave design mode... many of the icons in the Form Control toolbar will lose their colors. Try clicking on the checkbox on your form. A tick mark should appear and disappear when you click in the right place. Wow! (but that's a start!)
Save your "page" (form), just as you would if it merely had text on it. Put it in a folder of it's own... this is just a little practice exercise, so keep all the "scraps" from it together, so they can be discarded easily later. Call the document "fdb6macro1a" (FreeDataBase, tutorials section *6*, MACROs lesson 1, document "A"). (Often you can call things what you want to call them. Use my name for this one. You can call its folder whatever you wish.)
ARGHH!!! I can't believe how difficult something VERY SIMPLE is turning out to be!
ALL I wanted was a little something simple to change the label on that check box. NOT A LOT TO ASK. Well, after several hours, here it is!!! I hope you stick with this tutorial, because as hard as that is, it is nothing compared to what it took me to create it for you! Also.... I have found many times over the years that getting started in any computing task is by far the hardest hurdle. Once you can do what is in this tutorial, you will be "half way there" to LOTS of good stuff....
Here's the macro it took me so long to FINALLY get right. In a moment, you'll only need to copy/paste!
REM ***** BASIC ***** Sub Main Dim oThisDoc As Object Dim oForms as Object Dim oForm as Object Dim oCheckBox As Object oThisDoc = thisComponent.getDrawPage() oForms =oThisDoc.getForms() oForm = oForms.getByName("Standard") oCheckBox = oForm.getByName("CheckBox") if oCheckBox.state =1_ then oCheckBox.Label = "Ticked"_ else oCheckBox.Label = "Not Ticked" End Sub
We'll analyze what's there in due course.
Two little hassles first. If you are using OpenOffice 3.1, that should work. If you are using OpenOffice 3.2, change the last part of the....
oForm = oForms.getByName("Standard") oCheckBox = oForm.getByName("CheckBox")
oForm = oForms.getByName("Form") oCheckBox = oForm.getByName("Check Box 1")
There ARE spaces, and the "1", in what you need for the second line, under version 3.2. No spaces (or "1") in the version for 3.1. This all arises from a more general problem we will discuss later.
Don't fall to temptation to be clever and add....
if oCheckBox.Label = "Ticked"_ then oCheckBox.state =1_ else oCheckBox.state =0
(That part of the big picture is built in elsewhere, and if you add it here to make "Run BASIC" work, when invoked by hand, things will get all messed up later.)
Remember that I warned you earlier that there are several answers to most questions. What I am going to do here has pros and cons, but I'll spare you those details for now. Just don't worry if something conflicts with other things you know.
Go to the menu bar of the window with your ooWriter document. Click Tools | Macros | Organize Macros | OpenOffice.org Basic
On the left is a window "Macro from" listing several places macros can be. Click on the "+" in front of our document. (It will be listed as "fdb6macro1a" if you saved it with the name I told you to use.) That should reveal a folder called "Standard". Click on it.
Now click the "New" button, to create the shell for a new macro, and name it "fdb6checkbox".
You should get the following skeleton....
REM ***** BASIC ***** Sub Main End Sub
Use copy/paste to fill in the material between "Sub Main" and "End Sub", using the material presented earlier. (Yes, the "_" that you see at the ends of some lines should be there. Several logical "lines" have been split across several physical lines to make them easy to read, to save you having to scroll horizontally. The underscores "glue" the parts of each line together.)
Save what you have, before anything goes wrong. You can either use the "save" in the main window, or the macro editor window's menu's File | Save. (Using the latter spares you leaving the macro editor's window.)
(I had a report from someone using oo 3.2 that they got a "no such element" exception in a moment. If you are using 3.1 everything here should Just Work!! If you are using 3.2, the....
"oCheckBox = oForm.getByName("CheckBox")
....line may cause a problem. More on this in a moment.)
Don't have too high expectations yet! Things may not work as you may be assuming they will! Just hang in there, follow this through carefully.
Get your windows arranged so that you can see the macro, and the form you created earlier. Of that, you only need to be able to see the checkbox and the text next to it. Make certain you are not in the form design mode. (If you are in form design mode, clicking on the tick box will merely select the whole control, not change the tick mark.)
Just below the menu in the macro's window, there's a green triangle icon, tooltip "Run BASIC". Click it. If the caption disagreed with the state of the tick box before you clicked the "Run BASIC" button, you should have seen the caption on the checkbox change to "Ticked" or "Not Ticked" as appropriate.
(If you get the "no such element" exception, go back into the form design mode, right click on the tick box, choose "Control" from the pop up menu, go to the "General" tab, if you aren't already there. Look at the first line, "Name". Make it "CheckBox", if it isn't that already. (If it isn't, please send me an email, telling me what ooBase 3.2 made it by default? Or if the name already is "CheckBox", and you are getting the "no such element" exception anyway, please tell me... epecially if you know the cure??!!))
Change the state of the tick box, by hand, i.e. by clicking on the tick mark's box. THE CAPTION WILL NOT CHANGE (yet). Again by using the green triangle icon on the macro's window, run the macro. The caption SHOULD change now, to make the check box's state and the caption match. But the caption won't change again until you again, by hand, change the state of the tick mark. Then running the macro (by hand) will change the caption.
Whew! Not done yet, but believe it or not, we're a long way down the road.
Re-save the macro, just to be on the safe side. (That's really just the same thing as saving the document, even if you invoke the "save" from the macro editing window. The way we are doing this, (it is not the only way), the macro is "inside" the ooWriter document.)
The tick box caption and tick mark should change when you click on the "Check Box" ... and it will! You won't even need to have the macro editor window open.
Use View | Toolbars from the menu of your ooWriter document to make sure that the form design toolbar is switched visible.
The "Design Mode On/ Off" button on this toolbar is just a duplicate of the one you were using earlier; it isn't different. Make sure your form is in design mode, so that the icons are colored.
The form design toolbar has a "form navigator" icon. It is a window with a tiny compass. Fifth icon on the OO 3.1 toolbar. (Don't confuse it with the more general "Navigator" (just a compass) icon you may be familiar with.)
Click the Form Navigator icon to bring up a Form Navigator window which will show the elements of your form. You may need to click on "+" signs to open the lower branches of the tree. When you've made it visible, right-click on the "CheckBox" item, click on "Properties", and a quite busy window will open, with three tabs: General, Data, and Events. Click on the "Events" tab. (By the way... you can also access this window by right clicking on the check box and clicking "Control", if it is not already ticked. (If it IS ticked, the check box's properties window should already be showing somewhere on your screen... as long as you are in the Design mode. But the Form Navigator is useful for seeing the object hierarchies, and for getting at things you can't otherwise easily reach.))
Hopefully, you are now looking at the check box's Events... "When initiating...", etc.
Find the "Mouse button released" line. To the right of that is a text box, and to the right of that is an ellipsis ("...") on a button. Click that button.
In the window that ensues, click on the "Macro" button below the "Assign:" label, over in the upper right hand corner of the window.
Recognize the things in the "Library" panel? They are what you saw earlier when you created your macro. Click the "+" by "fdb6macro1a.odt", and then the "+" by the "Standard" which then appears, and your should see "fdb6checkbox" in the "Library" panel. Click on that, and you should see "Main" in the "Macro name" panel, and it should be selected. Click "OK" to say that you want to assign that macro to that EVENT, and click "OK" to close the parent window, too.
Save your document. (I have to admit: Mine, once, went a bit "nuts" around now... but eventually, without changing anything I've told you, things settled down. It just took a few cycles of closing the document, re-opening it, re-doing what I thought I'd done. And the next time I worked through this tutorial, from scratch, it didn't go nuts.)
And now.... I hope!!!.... your "document" (or is it a very, very simple "application"?) WORKS! I.e. you can tick or untick the check box, and each time you do, the caption changes to a description of the check box's state.
"Wow!" (I hear you say, derisively.)
Trust me... that simple little exercise, which I could do for you from what lies above in about 2 minutes, took HOURS to get RIGHT, and it took me years of using Open Office to get to where I could EVER get there, ever find the right bits at oooForum.org to figure out the MANY mistakes that I eliminated from my first attempt to create that pathetic little effort.
In the stuff that we've done so far, there are a number of things you need to notice
*** There are many places where I've chosen a name for something. In other places, I've allowed OpenOffice's choice to stand. Obviously, I chose the document's name, the macro "folder"'s name, and the macro's name. Less obviously, "Button" is the name of our PushButton. There are other things which have "frozen" names, e.g. "MsgBox" is "built into" the system, is reserved, and cannot be changed.
*** The language is case sensitive. Change "Button" to "button" in some places and the program stops working. There are some things you CAN type in any case, but if in doubt... use the "right" case... especially for names of things. Remember this when you come to use macros in connection with databases. If you want to do something with the table "Status", then you can't refer to it as "status".
*** Be grateful for the "syntax highlighting" that the macro editor provides. The colors help you see what the computer will make of something. Type REM and a space in front of one of the lines. See the color disappear? The REM has turned the line into a REMark... ignored by the system. If "similar" lines don't have similar colors, they are not as similar as you think they are. By the way: Another way to make something a remark: Put an apostrophe in front of it.
*** The lines....
if oCheckBox.state =0 _ then oCheckBox.Label = "Ticked"_ else oCheckBox.Label = "Not Ticked"
... gave me grief. See the underscore at the end of the first two lines? That is necessary to "glue" the screen lines (3) into a single "logical line". ooBasic is temperamental about where you start new lines. Note that there can be no space after the underscore, or it won't "count".
*** Objects and Methods
You are working in an object oriented environment. There is much more about this in the page I wrote introducing OOP and events.
Books have been written on the meaning of "working in an object oriented environment", but you will eventually build your grasp of the implications. The Wikipedia article is good, if you have no idea of OOP (object oriented programming).
In our checkbox macro, we have worked with objects.
Forget about computers for a moment, and think about a dog. A dog is an object. That "object" is made up of other objects: head, leg, tail, etc. Each of those are made up of objects... for instance, the hind leg has femur, tib/fib, and paw. The paw has toes and claws.
Objects have "properties". Sometimes a property can be set at a high level. The color of my favorite Labrador is black. I can say that the "dog" object is "black", and I am done. The leg, paw and toe objects "inherit" their color properties from the higher level objects. In another breed of dog, the colors of the different parts might be different.
We haven't done a lot with the properties of the objects in our macro yet, but their properties are half of the story of achieving our goals. We've changed the check box object's "label" property from "Ticked" to "Not Ticked" (and back). We did that on the basis of the "state" property.
The check box object was part of the form object called "Standard", which was part of the "Forms" object of the document object. The Form Navigator is a guide to the objects present in our project.
The other half of the story of OOP is methods. Methods are things that objects can do. To stretch the analogy a bit, a "dog" object has a "jump" method, a "run" method, a "sleep" method. I suppose you could say that the dog's throat object has a "bark" method.
"thisComponent" is an object that always exists when you work with ooWriter, indeed, I think, when you work with any Open Office module. We used the getDrawPage method when we said....
oThisDoc = thisComponent.getDrawPage()
Note the way that is written. The following is what that says....
Into the object we created called oThisDoc, put what comes back if you invoke the getDrawPage method of the object currently referenced by thisComponent.
In other words, you might write a program to call for a dog to bark as follows....
oAnimal="Fido" oPartToUse=oAnimal.getByName("Throat") oPartToUse.Bark="Welcome" oPartToUse.doBark
We put WHICH dog we wanted to use in oAnimal.
We put which part of that dog we wanted to use in oPartToUse. getByName is a "method" in my "animal programming" world.
We set the "bark" property to determine what sort of bark Fido would emit.
We called the doBark method to get the bark
Note that while oPartToUse.Bark refers to a property, oPartToUse.doBark invokes a method. This "ambiguity" isn't as hard to live with as you might think.
Look through the code for our macro "Main" again.
The Dim... lines declare variable we are going to use. The "As..." part allows the macro to set aside the necessary resources for a variable of the type we need.
The four lines from "oThisDoc = thisComponent.getDrawPage()" drill down into our document, to access the checkbox. They are a bit like moving from dog to leg to paw to claw. By the end of those lines, we have the checkbox "in" oCheckBox. We've "got ahold" of it.
The next three lines.....
if oCheckBox.state=0 _ then oCheckBox.Label = "Ticked"_ else oCheckBox.Label = "Not Ticked"
... as discussed before are "one" line, as far as ooBasic is concerned, thanks to the "_" at the ends of the first two lines. oCheckBox.state is a property of the check box oCheckBox refers to. It is 0 or 1, depending on whether a tick is present in the box. When we say "if oCheckBox.state=0", we are looking at the object, and asking what the property's current state is. If we were to say "oCheckBox.state=0" in a different context, e.g. without the "if", we could change the state of the check box, we would remove the tick.
(In a subsequent macro, I was able to split the if... then.. across several lines without the underscores. Not sure why. Even in that macro, if I wanted to split a long Dim statement with a carriage return I had to use the underscore.)
Still reading? Well done! That sort of stuff is very eye-glazing. You need to read it and similar again and again. The worse it gets, the closer you will be to the breakthrough. Remember learning to ride a bike? If you don't know how, it seems impossible. Try, try, and try again and suddenly you flip into the "I can do this. I will always be able to do it. Why couldn't I yesterday?" state.
So. All cheered up, and happy again. Good. You need to be.
In another essay I said that methods were half of OOP and properties were the other half? They are. But to work with macros, you have to master two things: OOP and event.
Happily, are easier to master than OOP.
Windows and Linux are both event driven operating systems. When you have a document on the screen, the computer isn't just waiting for you to press the next key on the keyboard, as it might have been in "the good old days".
For a start, you may not only have the document open, your email program may be running, and you may have it set up to check your mailbox from time to time, and notify you of new mail. Your computer's time-of-day clock is certainly ticking away. The computer (more specifically, the operating system) is "watching" both the keyboard and the mouse to see if you are pressing keys or moving or clicking the mouse.
Everything hinges on "events". Pressing a key gives rise to an event. Moving the mouse generates at least one event, possibly several.
The operating system (OS) is continually watching for events. When it sees one, it checks the state of sundry relevant properties, and invokes an event handler to respond to the event.
There are scores of events which you can forget about. We'll come to them briefly in a moment. But if you produce a macro, you will probably make it be the event handler for something. In the "Hello World" macro tutorial, the macro was the event handler for what should be done if the mouse button was clicked when the mouse pointer was over the button we put on our page. (See what I meant a moment ago when I said the OS "checks the state of relevant properties"?) The OS checked to see where the mouse was when it was clicked, and only invoked the message showing macro when the mouse was over our button.
What about those events we can forget about? They are in two categories....
** Events like the one that watches the "tick tock" deep within the OS and changes the time of day displayed on the screen once a minute
** Events which, for now, we are happy to have the OS deal with in the "ordinary" way. We may change their handler eventually, but for now "the ordinary" is sufficient.
One last thing to think about: the hierarchy of objects. Remember dog-leg-paw-claw? Just as properties (e.g. color) can apply at different levels, and be inherited downward, so to can event handlers. An "event" that the dog "OS" might be set up to handle is "poke", i.e. the dog might well have a response to being poked. Now, you might have a high level "poke" handler, and dog.poke would get a certain response. And that response could be the same, whether you poked the dog or poked his leg. However, the dog (and your Open Office document) can have different handlers at different level. While dog.poke and leg.poke might elicit the same response, eye.poke might have its own, special handler, the dog might respond differently if poked there.
I hope you've already inferred what I'm about to say, but just to be explicit: We "told" the operating system to use our macro "Main" when we accessed the events tab of the checkbox's "properties". (That window really ought to be called "Properties and Event Handlers"!) Don't worry about all the properties and events you see... you only need to understand a handful of them to do lots and lots of "good stuff".
Well.... I'm exhausted, and I suspect you are too. This started so innocently. And then, as computing things so often do, it took on a life of its own. Thank you for reading. I hope your effort will reap commensurate rewards. If you can just get past the "getting started" stuff, there is so much fun to be had!
When you've recovered from working your way through the above, "reward" yourself! If you are an ooBase user, discover how macros can be used with ooBase in my tutorial on that subject.
I was wrong to encourage you to use "GetByName". There are various problems with that. But it is a simple approach that is accessible to people with little experience of macro programming... like me. I believe there are fancy ways to get lists of what's in the document, and work directly with those computer-gathered lists. Maybe one day I'll master those skills. In the meantime, I am vulnerable to problems like having to include the text above about "use CheckBox if you are using OO 3.1, but Check Box 1 if you are using OO 3.2". Oh well, if it was easy, everyone would be doing it.
As a reward(?) for those who've diligently, doggedly read so far... here's a bit of weirdness. The following actually works... not that I recommend it!!
Dim oThingie as object Sub AnotherWay oThingie=thisComponent.getdrawpage()_ .getforms()_ .getbyname("Standard")_ .getbyname("CheckBox") oThingie.label="Bad idea" End Sub
A couple of serious points arise, even from such frivolity.
You must enter "Standard" and "CheckBox" (if those are the names you have for those entities in your document. They are case sensitive, "standard" or "CHECKBOX" won't do. But GetByName, getbyname or gETbYnAME do all seem to work.
Where the "_" character works to split a long program line across several screen lines will and won't work seems a little hard to determine. If in doubt... start with a long screen line, get that working, and then break it up as best you can. You can't put leading spaces on a line to indent it.
THERE! This tutorial done at last! Go take a break!
I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....
I am trying to present this material in a format which makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.
PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE
If you liked this ooBase tutorial, see the main index for information other help from the same author.
Editor's email address. Suggestions welcomed! - - - Want a site hosted, or email? I like 1&1's services.
Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org
. . . . . P a g e . . . E n d s . . . . .