Using a macro as a function

From Apache OpenOffice Wiki
< Documentation‎ | OOo3 User Guides‎ | Calc Guide
Revision as of 20:47, 15 July 2018 by Sancho (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search



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.
Tip.png Function names are not case sensitive. You can enter =NumberFive() and Calc clearly shows =NUMBERFIVE().


Save the Calc document, close it, and open it again. Depending on your settings in Tools > Options > OpenOffice.org > Security > Macro Security, Calc will display one of the warnings shown below. You will need to click Enable Macros, or Calc 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.
Warning if macros are disabled

If you choose to disable macros, then when the document loads, Calc can no longer find the function.

The function is gone.

When a document is created and saved, it automatically contains a library named Standard. The Standard library is automatically loaded when the document is opened. No other library is automatically loaded.

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 shown further down the page. Expand CalcTestMacros and find AuthorsCalcMacros. The icon for a loaded library is a different color from the icon for a library that is not loaded.

Click the expansion symbol (usually a plus or a triangle) next to AuthorsCalcMacros to load the library. The icon changes color to indicate that the library is now loaded. Click Close to close the dialog.

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.


Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Personal tools