Write Your Own Functions

Apache OpenOffice 4.1 Calc Guide

Chapter 12 Calc Macros

< Previous Section

Next Section >

• Introduction
  • The OpenOffice Basic Language
• Using the Macro Recorder
• Write Your Own Functions
  • Using a Macro as a Function
  • Passing Arguments to a Macro
  • Arguments are Passed as Values
  • Writing Macros that Act Like Built-in Functions
• Accessing Cells Directly
• Sorting
• Conclusion

< Previous Page

Next Page >

Write Your Own Functions

Calc can consider and execute macros as Calc functions. Use the following steps to create a simple macro.

1. Create a new Calc document named CalcTestMacros.ods.
2. Use Tools → Macros → Organize Macros → OpenOffice Basic to open the OpenOffice Basic Macros dialog shown in Figure 314. The Macro from box lists available macro library containers, including currently open documents. My Macros contains macros that you write or add to OpenOffice. OpenOffice Macros contains macros that are included with OpenOffice and should not be changed.

Figure 314: OpenOffice Basic Macros dialog

3. Click  Organizer  to open the Basic Macro Organizer dialog (Figure 315). On the Libraries tab, select the document to contain the macro.

Figure 315: OpenOffice Basic Macro Organizer

4. Click  New  to open the New Library dialog (Figure 316).

Figure 316: New Library dialog

5. Enter a descriptive library name (such as AuthorsCalcMacros) and click  OK  to create the library. The new library name is shown in the library list, but the dialog may show only a portion of the name (Figure 317).

Figure 317: The library is shown in the organizer

6. Select AuthorsCalcMacros and click  Edit  to edit the library. Calc automatically creates a module named Module1 and a macro named Main (Figure 318).

Figure 318: Basic Integrated Development Environment (IDE)

7. Modify the code so that it is the same as that shown in Listing 2. The important addition is the creation of the NumberFive function, which returns the number five. The Option Explicit statement forces all variables to be declared before they are used. If Option Explicit is omitted, variables are automatically defined at first use as type Variant.
8. Save the modified Module1.

Listing 2. Function that returns five.

REM  *****  BASIC  *****
Option Explicit
 
Sub Main
End Sub
   
Function NumberFive()
  NumberFive = 5
End Function