Using a macro as a function

From Apache OpenOffice Wiki
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.

Template:Documentation/Tip

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.

File:CG3Ch12F13.png
OOo warns you that a document contains macros.

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

File:CG3Ch12F15.png
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.

File:CG3Ch12F16.png
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. File:CG3Ch12F17.png
    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