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.
 | 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.
- 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.
Select a macro and click Edit.
- Change the name of NumberFive to NumberFive_Implementation (see Listing 3).
Listing 3. Change the name of NumberFive to NumberFive_Implementation
Function NumberFive_Implementation()
NumberFive_Implementation() = 5
End Function
- 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.
- 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.
- 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.
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
- Save, close, and reopen the Calc document. This time, the NumberFive() function works.
