Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

OpenOffice Calc 3.x Guide
Previous Page Home Next Page

Using the newly created Calc document CalcTestMacros.ods, enter the formula =NumberFive(). Calc finds the macro and calls it.

Use the NumberFive() Macro as a Calc function.
Image:Tip.png Function names are not case sensitive. In the figure above, I entered =NumberFive() and Calc clearly shows =NUMBERFIVE().

Save the Calc document, close it, and open it again. By default, OOo will warn you when you open a document that contains a macro. Click Enable Macros, or OOo will not allow any macros to be run inside the document. If you do not expect a document to contain a macro, it is safer to click Disable Macros in case the macro is a virus.

OOo warns you that a document contains macros.

When the document loads, Calc can no longer find the function.

The function is gone.

OOo documents can contain macros. When the document is created and saved, it automatically contains a library named Standard. The Standard library is special in that it is automatically loaded when the document is opened. No other library is automatically opened.

Calc does not contain a function named NumberFive(), so it checks all opened and visible macro libraries for the function. Libraries in OpenOffice.org Macros, My Macros, and the Calc document are checked for an appropriately named function. The NumberFive() function is stored in the AuthorsCalcMacros library, which is not automatically loaded when the document is opened.

Use Tools > Macros > Organize Macros > OpenOffice.org Basic to open the OpenOffice.org Basic Macros dialog. Expand CalcTestMacros and find AuthorsCalcMacros. The icon for a loaded library is different than the icon for a library that is not loaded.

Unloaded macro library.

Click the plus (+) next to AuthorsCalcMacros to load the library. The icon changes to indicate that the library is now loaded. Click Close to close the dialog.

Loaded macro library uses a different icon.

Unfortunately, the cells containing =NumberFive() are in error. Calc does not recalculate cells in error unless you edit them or somehow change them. The usual solution is to store macros used as functions in the Standard library. If the macro is large or if there are many macros, a stub with the desired name is stored in the Standard library. The stub macro loads the library containing the implementation and then calls the implementation.

  1. Use Tools > Macros > Organize Macros > OpenOffice.org Basic to open the OpenOffice.org Basic Macros dialog. Select the NumberFive macro and click Edit to open the macro for editing.
  2. Select a macro and click Edit.
  3. Change the name of NumberFive to NumberFive_Implementation (see Listing 3).
  4. Listing 3. Change the name of NumberFive to NumberFive_Implementation

    Function NumberFive_Implementation()
      NumberFive_Implementation() = 5
    End Function 
    

  5. In the Basic IDE, hover the mouse cursor over the toolbar buttons to display the tool tips. Click the Select Macro button to open the OpenOffice.org Basic Macros dialog.
  6. Select the Standard library in the CalcTestMacros document and click New to create a new module. Enter a meaningful name such as CalcFunctions and click OK. OOo automatically creates a macro named Main and opens the module for editing.
  7. Create a macro in the Standard library that calls the implementation function (see Listing 4). The new macro loads the AuthorsCalcMacros library if it is not already loaded, and then calls the implementation function.
  8. Listing 4. Change the name of NumberFive to NumberFive_Implementation.

    Function NumberFive()
      If NOT BasicLibraries.isLibraryLoaded("AuthorsCalcMacros") Then
        BasicLibraries.LoadLibrary("AuthorsCalcMacros")
      End If
      NumberFive = NumberFive_Implementation()
    End Function 
    

  9. Save, close, and reopen the Calc document. This time, the NumberFive() function works.



OpenOffice Calc 3.x Guide
Previous Page Home Next Page

 
 
  Published under the terms of the Creative Commons License Design by Interspire