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

Open Office Tutorials- Macros...

Using them to create and access
user defined functions and subroutines
Intermediate level tutorial

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 ®, 11/11.



Introduction

This tutorial is written for an audience that is already reasonably comfortable with OpenOffice macro work. If you haven't done much with macros, you might want to visit some of my "getting started with macros" tutorials. The tutorial you are looking at now is at an intermediate level. If it isn't telling you new things, try my advanced tutorial on user defined functions, even accessing functions and subroutines in external DLLs.

Common introduction

If you have read my advanced "using functions and procedures from DLLs" tutorial, the next few paragraphs will look familiar... much of the material appears in both tutorials because I can't be sure that every reader will read these tutorials in the same order.

This tutorial is going to show you how user defined functions can be created and used in an Open Office document. In the tutorial, I am going to be using an ooCalc worksheet... but the principles apply to other Open Office documents, too. And you might even find that what we are doing here would work in Excel... but I make no promises, as ooCalc takes care of all my spreadsheet needs without the expense of supporting Microsoft!

The functions used for the examples aren't very exciting, but they should be enough to get you started, help you with creating functions you do want.



Creating and using a user defined function

A function is something that "returns" a value. Our first user created function is going to return "Hello World" any time we invoke the function. Don't worry... later functions will be more "useful".

Start up a new, empty, ooCalc worksheet. We're going to go rather slowly at first, just to cover some basics "gotchas", but don't go to sleep... the pace picks up before long.

I was using Open Office 3.0.0 on a Windows XP machine, by the way, but that shouldn't matter. Tell me, if something about your system makes what follows incorrect, please?

Save your worksheet as TstUserCreatedFunctionEasy.

(In this work, you will find ooCalc "doing things" to your capitalizations... just type what I suggest, but don't worry too much when ooCalc changes things on you! But remember that there ARE places in macro work where the case DOES matter, e.g. MyVariable isn't always accepted if MYVARIABLE is what is wanted. Avoid using ALL CAPS, or all-lower-case, but don't "fight" the machine.)

Once again... for a while... the material below will seem familiar if you've read my harder 'Open Office and DLLs' tutorial. Don't worry... this time we will go on to some more easy things, instead of going over the edge into the complex ones!

Invoke....

Tools | Macros| Organize Macros | OpenOffice.org Basic...

... which will open a dialog. In the left hand pane, drill down (you'll click on a little "+" sign at one point.. or double-click on a "folder" name) to TstUserCreatedFunctionEasy.ods/Standard. Click on the "New" button. This will let you create a new "module". Assign "tkbUsrFuncs" as its name. (If you save your macro modules inside the document, as we are going to do here, you can have different sets of macros for different situations, and the modules can even have the same name.). Don't go off there now and get distracted from the joys of using macros, and using functions and procedures from external DLLs, but when you're ready, I've done notes for you on the pros and cons of the ways to saving Open Office macros in different places.(That link will open in a new tab or window, if you want to get it loaded now for later reference.)) As soon as you've done that, a window will open. It will have...

REM  *****  BASIC  *****

Sub Main

End Sub

... already typed for you; that is a skeleton for a macro.

Rather than a macro, we're creating a user defined function. But we're going to do it "in" the "part" of ooCalc set up for doing macros. That's okay... just a little confusing, maybe.

Our first function isn't going to do very much... Just return the traditional "Hello World". (Something very like it has a valuable use: You can make a user defined function report the version ID for collections of functions you've put together.)

Replace EVERYTHING that the system "pre-typed" for you with....

function tkbHello()
tkbHello="Hello World"
end function

That will create a new function, available to this worksheet (not across all your ooCalc work... but while we're finding our feet, we don't want to "mess with" the underlying, global, installation of ooCalc, do we?) which will add two numbers.

In the "edit macro" window, where we've just created our user-defined function, find the "compile" button (first on my toolbar... three "sheets of paper" in a stack, with a blue arrow pointing down.)

Click it. You should see nothing. If you have typos, you may get an error message.

At this stage, I would be inclined to click File| Save... but you don't have to. Leave the "macro" editing window (which we are using to edit a function) open, go back to the main worksheet window.

Enter....

=tkbHello()

... in a cell, and press "Enter"... "Hello World" (without the quotation marks) should appear in the cell. Note the "()" after the "tkbHello". For this simple function, the parentheses are empty, but you still need them in the contents of the cell which calls the function. It is part of how ooCalc knows that "tkbHello" is a function. Later we are going to put things inside the parentheses.

Exit out of all you've done, saying "Save" if you get a "you've made changes, don't you want to save them?" warning.

Re-open the worksheet. You may well get a "This document contains macros.... Execution of macros is disabled..." message.

If you do, follow the instructions for enabling macros execution, re-close the worksheet, re-open it. (I wish I could show you how to permanently authorize macro execution at a document-by-document level. However, the "medium" security level seems good to me. It will be tedious to say "yes, okay" each time I open a worksheet with macros... but I don't use them much (yet), and it will create a tripwire if someone tries to inflict a malicious macro on me.)

Start made

Fancier User Defined Functions

So far, so good... but we're now going on to user defined functions which are a little harder to make and use... but which are more useful, once made!

Here's what's coming up....

We're going to create more user defined functions. They are going to be defined in the same macro container we used above

Our new user defined functions are going to be fancier, because we're going to pass numbers to them, and the answer we get back will change depending on our "input"

First "fancy" user defined function: tkbDoubleIt

Suppose that you were doing some spreadsheet work, and that it would be helpful to have a way to double any number.

Along the road to that, put the number 12 in cell A5 of the worksheet we were working with earlier, the one with your tkbHello() user defined function.

Put =A5*2 in cell B5, and you should see 24

There's already a way to see what twice something is. That's not the point. Our example may accomplish something "trivial"... but I don't want anything to distract from how we do what we're going to do.

Put =tkbDoubleIt(A5) in B5, but don't be alarmed by whatever error message you get. (You'll probably see "#NAME"? in the cell after you complete entering the formula.) OpenOffice's spreadsheet doesn't have a "tkbDoubleIt" function... yet.

Once again use Tools| Macros| Organize Macros| OpenOffice.org Basic (and some drilling down) to reopen the macro module you created earlier, tkbUsrFuncs. You should find that...

function tkbHello()
tkbHello="Hello World"
end function

.. is still there.

Now add the following....

function tkbDoubleIt(a)
tkbDoubleIt=a*2
end function

Click on the "compile" button. (This is probably optional, if you can't find it easily. It is a quick way to catch some boo-boos, if you can find the "compile" button.)

Now click again on the worksheet. No changes are apparent. But! Click on B5, the cell holding =TKBDOUBLEIT(A5). Don't worry about the annoying fact that ooCalc put everything into upper case letters. Press F2, to enter "edit mode". Press backspace, which should get rid of the ")" at the right hand end of TKBDOUBLEIT(A5). Press the ")" key to put the parenthesis back again. Press the enter key, to finish "editing" the contents of A5. (It was a pretty feeble "edit"... you started to make a change, put things back to how they were before, and left the edit mode. But that's enough to "trick" ooCalc into re-calculating the contents of the cell... and you should now see 24! Hurrah! tkbDoubleIt() is working!

Our user defined function "takes an argument", and "returns a value". Change what you have in cell A5. If you make it, say, 22, the contents of B5 should change too. (To 44, if you put 22 in A5, right?)

We're done with tkbDoubleIt(), probably. Let me ramble on just a moment more about "arguments".

Think about the standard ooCalc function "SQRT", which returns the square root of whatever argument you pass to it.

Put =SQRT(36) in cell E5, and you should get 6.

Put 36 in cell D5, and =SQRT(D5) in cell E5, and you should still get 6 in E5. Change what's in D5, and what's in E5 should change.

This is just the normal operation of an ooCalc function (SQRT()). When you use your user defined tkbDoubleIt(), it is working just like the built in functions.

Second "fancy" user defined function: tkbWithTax

The function tkbHello() has the parentheses after it to help the system realize that it is a function, but you never put anything between them.

The functions SQRT() and tkbDoubleIt() both need a number inside the parentheses.... one number. And there are many other functions like them.

The function TRIM() needs a string between the parentheses. TRIM() gets rid of any leading or training spaces. Again, it is a single argument function, but one that requires (and returns) a string, rather than requiring or returning a number.

The function LEN() is passed a string, and returns a number. (It reports the LENgth of the string.)

And so on.

There are also functions to which you pass more than one argument, and we're going to make a user defined example of one of those.

Put tkbWithTax(200;3) in cell A6. As before, because ooCalc doesn't yet know about the tkbWithTax, which we are just about to create.

A "little detail"... the sort that regularly costs computer users hours of frustration: The two numbers inside the parentheses are separated by a semi-colon, no the colon you might expect.

tkbWithTax is going to return 206 if you pass it 200;3. It will tell you the cost of something with tax if you pass it the cost without tax, and the tax rate.

Go back into macro module tkbUsrFuncs.

Add to it...

function tkbWithTax(without,rate)
tkbWithTax=without+(without*rate/100)
end function

Go back, as before, and "nudge" the contents of A6. You should get 206, the price, with a 3% tax, of something which cost $200, without tax.

If after you've added the function definition to the macros module, and nudged the contents of A6, you get "#NAME?", double check your typing. (Using copy/ paste is usually best, but be sure to study what you've copy/pasted!). No mistakes?

Look at what you put in A6. Did you put...

=tkbWithTax(200;3)

... or did you put....

=tkbWithTax(200,3)

??? See the difference? It's the ante-penultimate character... Right: semicolon. Wrong: comma. I actually, really, truly made exactly that mistake while doing this exercise alongside writing this tutorial. It is an easy way to go wrong.

You don't have to "hardcode" the arguments of a call to tkbWithTax() any more than you have to hardcode the argument to a call of tkbDoubleIt() or Sqrt(). Here's an example that may be interesting...

Put the phrase "Tax Rate" in A10.

Put 3 in B10. It will be the tax rate for some calculations to come.

In A11, A12, A13, respectively, put....

200
2000
500
543

And now in B11 put...

=tkbWithTax(A11;$B$10)

.. and you should immediately get 206 in B11.

Now for the clever bit... and the bit that was why we put the dollar signs in $B$10....

Make sure you aren't still editing the contents of B11... Pressing the enter key should take care of that.

Right-click on B11, and click on "Copy" in the pop-up menu.

Drag across cells B12, B13 and B14, which should leave them both selected. Right-click inside one or the other, and this time click "Paste" from the pop-up menu.

Presto! You should now see....

Tax Rate     3
--------------
200        206
2000      2060
500        515
543        559.29

Ta da! The prices of a bunch of things, before and after tax. Change JUST the tax rate... and see what happens to the four "with tax" figures.

It isn't really a part of user defined functions, but let me just mention in passing what made that little bit of magic work...

When I pasted =tkbWithTax(A11;$B$10), which I copied from B11, into B12, B13, B14, I got the following....

=tkbWithTax(A12;$B$10)
=tkbWithTax(A13;$B$10)
=tkbWithTax(A14;$B$10)

The first term is changed during the "paste" process to compensate for the new location. The second term, because of the dollar signs, is NOT changed during the paste process... so all of the calculations use the "Tax Rate" figure from B10, even though the without-tax figure it is being applied to comes from different cells in each instance.

Enough $ diversion!

One last time: Between the arguments passed with a function: semicolon, not commas.

That's all, folks!

That just about does it, I hope? I nearly threw in a little bit about putting a macro in the macro module. Macros "do things" whereas functions, as you learned above, return values. What sort of things go macros do? They can cause little messages to pop up, they can go through a worksheet changing things. Etc, etc. But I realized just in time that there is a separate and complete tutorial all about using macros, so I didn't repeat that here. Whew.




If you are like Oliver, and want "More...."

I hope that was useful? If you are ready to go even further, try my advanced tutorialon using functions embedded in external DLLs!

But take a break, first... You've earned it!





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 validator.w3.org


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

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