Using a macro as a function
Using the newly created Calc document CalcTestMacros.ods, enter the formula =NumberFive(). Calc finds the macro and calls it.
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.
If you choose to disable macros, then when the document loads, Calc can no longer find the function.
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.
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.
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.
- Change the name of NumberFive to NumberFive_Implementation (see Listing 3). Listing 3. Change the name of NumberFive to NumberFive_Implementation
- 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.
- Save, close, and reopen the Calc document. This time, the NumberFive() function works.
Function NumberFive_Implementation()
NumberFive_Implementation() = 5
End Function
Function NumberFive()
If NOT BasicLibraries.isLibraryLoaded("AuthorsCalcMacros") Then
BasicLibraries.LoadLibrary("AuthorsCalcMacros")
End If
NumberFive = NumberFive_Implementation()
End Function
Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY). |