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- Macros...

Using them to access functions and subroutines
in external DLLs- advanced help

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 ®, 3/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 reading now jumps into some relatively advanced topics fairly quickly... I have an intermediate level tutorial about using DLLs which you may want to look at too, especially if the following, at first, is too difficult. The other tutorial should help you get ready for what follows, if you aren't ready now.

Common introduction

If you have read my intermediate "using functions and procedures in DLLs" tutorial, the next few paragraphs will look familiar... the same text 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 functions and procedures within an external DLL can be accessed from 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 and procedures used for the examples aren't very exciting, but there are things which can be delivered via DLL that are worth doing that way, for a variety of reasons.

How do you write your own DLL? That is a much bigger question. I have written a guide to writing a DLL with Delphi. There are general points in that which may help people working in other languages.

But you don't have to write your own DLL to work through this tutorial on using a DLL from within an Open Office document. You just download the zip file that goes with my "Writing DLLs" tutorial, and extract from that just the file called "dd50demo.dll". Store it in the root of your C: drive. (It shouldn't be necessary to be so crude... but I haven't cracked doing something more elegant yet!)

Actually... on second thoughts... I've just created another archive with a DLL which might be better. In it you will find the dd50demo.dll, and a file called DD88.exe. Copy both of them to the same folder, the root of your C; drive might be "best" (sorry). The .exe is just a little "test" program you can use... if you wish... to see the DLL is working. You don't need to run it if you don't want to. (The other files in the .zip are of interest only to Delphi programmers, although you can open DD88.pas with any text editor to see the code at the heart of DD88.exe.) Nota Bene: You need to put a second copy of dd50demo.dll in the root of your C: drive for the Open Office work we are about to embark upon exactly as presented. By all means try to master "the tricks" which allow you to put the DLL in a more sensible location, but that is a detail I leave as an exercise for the student!



Getting started with using a function from a DLL

I lied. You should already be "started". If you haven't put a copy of "dd50demo.dll" in the root of your C: drive yet, (explained above), do it now.

Now start up ooCalc, and open a new 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.

Create a little OpenOffice Calc worksheet. I was using Open Office 3.0.0 on a Windows XP machine.

Save it as TstUserCreatedFunction.

(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 read my other 'Open Office and DLLs' tutorial. Don't worry... things will get harder soon enough!

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 TstUserCreatedFunction.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

Okay so far, I hope? If you are already struggling, maybe you should work through my intermediate level tutorial on user defined functions, and then return to the one you are reading now? (That page will open in a new window or tab, so you can get back to this easily, if you wish to.) But if all you want to know is how to access things inside an external DLL, then that won't give you any direct help. The good news is that you'll be able to skim the start of the intermediate level tutorial, because it is very similar to what you've read so far. Similar, but not the same... do look out for the differences.

Yes, I realize that we haven't used the function and procedure in the DLL yet. Be careful what you wish for.

In the same "macro module" where you defined "tkbHello()", enter the following, near the top....

Declare function pcDLLVersion lib "C:/dd50demo.dll" as string

That gives you some access to the function "pcDLLVersion", which is inside the DLL. (The name arises as follows:

*** "pc" for "pchar", the Delphi data-type that the function returns, when you are talking in Delphi terms... which you don't need to know about... I just didn't want you wondering about or ms-interpreting the "pc"

*** DLL: for "dynamic link library"

*** Version: for "this function will return a string telling you the version, the "id" of the particular DLL that you are accessing. (What comes out of the DLL when you call pcDLLVersion is hard-coded within the DLL. The DLL programmer should remember to alter the version information when he/ she alters the DLL. At the moment, the DLL responds with "1.0.0"

------------------

I said "that gives you some access. You don't yet have enough access to be able to get the string from the DLL with anything you can (yet) put in a worksheet. So now we move on to the other step in the process....

In the same "macro module" where you defined "tkbHello()", enter the following, somewhere below the declaration you just inserted....

function VerInfo()
VerInfo="Version, from DLL:"+pcDLLVersion
end function

Now you can put something in a cell of the worksheet....

=VerInfo()

... and when you do, from your macro, you get "Version, from DLL:", and from inside the DLL you get "1.0.0", resulting in....

Version, from DLL: 1.0.0

... appearing in the cell that has "=VerInfo()" driving it. Cool! You wouldn't believe how long it took me to get "all the bits" of that collected up and marshaled for this essay! The "as sting" bit was the last ingredient. (Without it, you can get gibberish, and are unlikely to get anything close to "1.0.0".)

Now for invoking a procedure inside a DLL

Great! We can get "stuff" out of a DLL, massaged by code inside it. (There are ways to pass things into the DLL... but I'm still getting the last details of that right, sorry)

What about invoking a procedure inside a DLL? Our demo DLL has inside it the code necessary to cause a little message box to pop up on the screen, saying "Hello World". How do we make that happen from an Open Office document, say a worksheet managed by ooCalc?

What I called a procedure a moment ago would usually be called a macro, in an Open Office context. Macros "do things" whereas functions return values, as you learned above, or in my introduction to user defined functions for Open Office tutorial. 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.

But I didn't, in that tutorial, talk about using procedures (macros) inside a DLL, so here we go...

First, we have to tell ooCalc about the procedure contained within the DLL, and give it a way to invoke it.

To your macro module "tkbUsrFuncs", add...

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

... and....

sub tkbSayIt
SayHi
end sub

That's all that's needed to prepare the way. Note we've used "declare sub" and "sub tkbSayIt...", rather than the "declare function" and "function ver()..." that we needed previously. A function returns a value (or values). If we are invoking some code to "do something", in Open Office terms we are creating a macro, and use "sub", taken from "subroutine".

Now to put that macro/ subroutine/ procedure to work...

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...

Suppose you want a particular macro to "fire" each time you open a document? How you go about that is addresses in my tutorial about where macros are stored, in the "appendix" at the bottom, at the moment.

Other resources

I didn't understand everything I read in the following two days ago when I started on this quest... but it did give me ideas of things to try, and might be useful to you too, concerning calling DLL functions from a spreadsheet document.

http://msdn.microsoft.com/en-us/library/bb687915.aspx



A "little mystery" I haven't cracked yet....

Suppose I wanted the tkbSayHi macro to fire, and cause the procedure inside the DLL to execute when, say, the contents of cell D4 was equal to 42. How would I achieve that? Answers on a postcard, or, if you prefer, contact me by my response form or email. Please?




"And so, to bed...

While I haven't, I'm sure, got all of the above "perfect", or answered every question, I hope it has brought you closer to where you want to be?





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 . . . . .