AUTHOR'S MAIN SITE   > > > > >   TABLE OF CONTENTS for Open Office database tutorials.
MACROS section, Open Office tutorials.    Delicious Bookmark on Delicious    Recommend to StumbleUpon

Open Office Tutorials
Where macros are stored

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-3/11.


You can do a great deal of useful work in Open Office without ever getting involved with macros. But if you master macro programming, you can do even more.

The page you are reading addresses just one tiny detail in regard to macros: Where they are stored. It also delves into questions about where ooBase macros are stored in the different versions of ooBase. (A significant change in the storage of macros took place between ooBase version 2 and ooBase version 3.0, and a further important change occurred between version 3.0 and 3.1 If you want to work with macros and ooBase, it would pay you to upgrade to Open Office 3.1... but there are ways to postpone the upgrade. Whether it matters to your macro use can be understood from material near the end of this essay.)

Where macros live.... in general....

An Open Office macro can reside in at least two places, and each has its pros and cons.

A macro can exist in a central repository on your machine. In this case, the macro will be available to any document you work on via Open Office, which might seem like a Good Idea.... but it will also mean that if you have some error in the macro, that macro will be present across the board. But of course if you fix it, you will only have to fix it once. But what if the "fix" (in one context) messes up something that worked "okay" in another?

In the light of what I hope you will infer from the previous paragraph, I prefer to store the macros a document needs in that document. The downside of this approach is that if I ever devise a macro that has broad usefulness, I will have to copy to each document I want to use it. I will have to forward updates when and as they arise. of course there is also an upside: If I move a document to a different computer, it brings with it any macros it needs... which doesn't happen if your macros are in your central Open Office repository.

Which approach is best for you will be influenced by your computing circumstances.

Where macros live.... details....

If, from any Open Office application menu you access Tools | Macros | Organize Macros, you will see several choices....

(The selection on your machine might be slightly different, but I would expect at least Basic to be present.

If you select any of the options, you get the same dialog, but what you see when you make use of that dialog will be filtered based on which macros you set out to "organize". We're going to be working with the Basic macros, but I believe that anyone working with different macro sets would have similar experiences to those I will be describing.

On a computer where not much has happened in respect of macros since a pre version 3.1 OpenOffice was installed, and on which only an ooBase Open Office database is currently open, you will see in the "Macro From" pane only "My Macros" and " Macros". In front of each there will be a small square containing a "+" or a "-", indicating, as usual, the existence of a hierarchical structure, like the structure of files in the folders on your backing store. If you are using a post version 3.0 version of Open Office, you will also see a line for the open database.

The two top level "containers" ("MyMacros" and " Macros") are, I believe, properly referred to as "Libraries".

The first ("My Macros") is the top level container for macros created which are available to all Open Office documents running on the computer you are currently using. You can add modules to it. Note that you do not need to create a new module for each macro you wish to create... a single module can contain multiple macros.

The second macro library, " Macros" is "read only"... you can use what's there, but not (directly) change it. (Although I suspect that at least the Open Office update process (which you may invoke), or add-ins (which you may install) may change what is present in this library.

Following those two, what you see will depend upon whether your Open Office is newer than version 3.0

For 3.0 and 3.1 installations, if you have an ooWriter document open, as yet unsaved, then in the dialog showing the libraries, you will have an additional entry, one titled "Untitled". Yes... that's your ooWriter document. Any macros you create within that container will be held within the ooWriter document. The system is quite clever about keeping all the names straight. For instance, if you create a macro before you have saved the document, and subsequently save it, the "Untitled" entry in the list of available libraries will have changed to fit the name you gave the document. Whew.

For an Open Office 3.1 installation, if you have a database open, then, as I said, there will be a line for that too. If you have a post 3.0 installation, then you don't need to fool around with having the ooWriter document. It was only there for people with pre-3.1 installations, to provide a place to put macros. Before 3.1, you couldn't store them in file holding all the other parts of your database.

What about macros for ooBase databases?....

I would like to save my macros with the document which uses them. And by "document", I mean the database when I am working in ooBase. (... the worksheet when I'm working in ooCalc, etc.)

Before version 3 of ooBase, there was a way save macros with the form they applied to. Nice... but not a perfect answer. In ooBase version 3.0.0, the old "save it with the form" option has been cleared away. The "save it with the database" option appeared, hurrah, with Open Office 3.1.

Fear not! For those who are not ready to upgrade to 3.1, there is an answer! Here's the work-around.....

Create an ooWriter document, and store it in the same folder as your database. Make certain to have that document open at any time you are working with the database. You can create and store macros in the ooWriter document. As long as it is open, those macros will be available to any ooBase database which is open at the same time. A little "messy", but not the end of the world.

Do upgrade to Open Office 3.1 when you can. There are always risks, and I probably hate "upgrades" more than most people, but I haven't (yet) had a bad experience through doing an Open Office upgrade. Each time I've done it, the system has even done a good job of automatically clearing away the old version. But do take the usual precautions.

That's (mostly) it!...

I hope the above was useful and will incline you to return to the main page of my Open Office tutorials, and access other tutorials... or even visit my freeware and shareware "store"!! And bring it to the attention of parents and schools, in particular.

But... P.S....

In the course of writing another tutorial, I did some more work on "Macros- where?" without remembering that I'd written the material above essay months previously! Sigh. There will be some overlap between what is above, and what is below. And I will someday try to come back and make an elegant merge of the two sections which exist here today. But for now, I'm just "dumping" the new work here as a sort of appendix. It was done in March of 2011, a Windows XP machine, with Open Office 3.0.0

Suppose you want a particular macro to "fire" each time you open a document? The answer to that is in what follows...

Start by creating the macro that you want. A simple pop up "Hello World" message box will do to test what follows. If you don't know how to create one, see my tutorial with introductory material on Open Office macros.

Now to put that macro to work...

If you haven't already done so, put a button on your worksheet. The "toggling icon" (on/off) for "edit file" on the main toolbar will be crucial in getting the following working. Apologies for not having all of the details worked out yet... but you should be able to muddle through, based on the following.

Once you have your button on the worksheet, when you have the "edit file" button in the right state ("down"), if you right click on the button, it becomes selected (drag marks in corners), and a pop-up menu arises, giving "control..." as one of your choices. Click it to open the control's properties. (A button is "a control").

Go to the "events" tab, and on that, click on the ellipsis ("...") to the right of the Mouse Button Pressed event.

That will open an "Assign Action" dialog, with "Mouse Button Pressed" selected. Click on "Macro..." (over at the right, below "Assign"). That will open the Macro Selector... and you should see your macros module, the one called "tkbUsrFuncs"... but it isn't always shown, when I look! If it isn't shown, cancel out until you can do Tools | Macros| Organize Macros... When you've done that, select "tkbUsrFuncs"... and then go back, go through the steps to get to the assignment of a macro to "Mouse Button Pressed" again. Drill down to your macros module ("tkbUsrFuncs"), highlight "tkbSayIt", click "OK"

Click the "edit file" button, to change it's state. You'll probably be asked if you want to save your work. (Do that.) And you'll get the warning about macros, if you have security set to "medium", but in due course you'll be looking at your worksheet. Try clicking your button. A message box should come up on the screen, saying "Hello World".

Fancier: "On startup" calling of procedures from within DLLs...

Well, fine... but let's be a little more ambitious. How about setting things up so that the message from inside the DLL arises whenever you open a particular worksheet.

A necessary, I think, evil creeps in. The code arranging for the calling of the macro can no longer exist solely within the document, I don't think. The document will be trying to do the "tkbSayIt" macro before it has loaded the documents internal macros, I think... if you assign it to "fire" during the opening of the document.

The way around this Catch-22 is to store the macro elsewhere. I elected to store it in the My Macros . Standard library, in a new macro module I created there and called tkbPCellie2011mar because the module is global in my Open Office in the computer I call pcEllie, and I created it in March of 2011. In that macro module, I entered and saved....

Declare sub SayHi lib "C:/dd50demo.dll"

sub tkbGlobalSayIt
end sub

The addition of "Global" to the name wasn't "necessary"... I could have had two macros with the same name, as they were in different places... but having two things with one name can lead to programmer confusion... never a good thing!

The file which holds your installation-global MyMacros seems to be in an XBA file with the name you gave the module. The path to this seems to start the same way as most of the paths designated within...

Options| OpenOffice. Org| Paths

... e.g.

C:\Documents and Settings\TKB\Application Data\\3\user\.....

From there, the path for the MyMacros seems to be...


Whether that path can be directly set is something I don't know, and wouldn't recommend fooling with! Which begs the question: How do you get two machines "in step" in respect of what they will do when you open a document that has an "Open Document" event handler defined....?

Once a way to invoke the DLL's "SayHi" existed outside the worksheet, I went back to the worksheet and executed "Tools | Macros| Organize Macros..." yet again.

I drilled down to tkbGlobalSayIt, and then clicked the "Assign" button. That opened a dialog with three tabs. If you aren't on the "Events" tab, go there. You should see a list....

The one you want is "Open Document". Click the "macro" button over at the right, under "Assign". Be sure that in the combo box at the bottom, your action assignment is going to be saved in TstUserCreatedFunction.ods. (So that this only happens when you open this document... it seems some things ARE loaded before the "Open Document" event fires.)

Drill down to tkbGlobalSayIt (yes, drill down again... seems daft you have to say "assign" twice, and have to drill down twice... but if you can find a faster way, more power to you. I couldn't!)

If when you try to assign the macro, the macro module you made, the one with TkbSayIt in it isn't in the list of available modules, use Tools| Macros| Macro Organizer. Drill down to your macros module. Then try again to assign TkbSayIt to the form "Standard" of your worksheet. I hope that you WILL, as I (usually!) do find TkbSayIt available on the second (or third) try. I believe Open Office is reliable and stable... when you figure out the "tricks". It's just that I'm still "missing something" about how it ACTUALLY behaves. It is so easy to assume that powerful software can read you mind and do what YOU THINK it "ought" to do. Actually, it tends to do what the programmers designed it to do. Sorry about that. Suck it up!

Say "OK", "OK", "Close" to tidy things up. Save your worksheet. Close it. Re-open it. The procedure inside the DLL should fire as the worksheet re-opens, greeting you with the "Hello World" message box.

Useful exactly like that? Not very. But I hope it shows you how you can arrange for procedures inside DLLs to fire when you are working in Open Office.

Other resources

I didn't understand everything I read in the following, but it did give me ideas of things to try, and might be useful to you too, concerning arranging a pop-up when a worksheet opens... which entails a Catch-22 in respect of where things are stored.

Editorial Philosophy

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.

Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

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.

Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at

One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .