Creating a Macro

For this series of tutorials and examples we'll use the Calc program to demonstrate (this is where we needed macros, and their use here is clear and quick to see).

Since creating is one of the places the manual actually gives some information this will be terse here.

1. Activate Tools | Macro.
2. Select Macro from | Untitled1 | Standard
3. Activate New.
4. Enter MyModule.

Then a new window appears with some text Sub Main. The next time you go to create a macro when you press New in the above procedure it will not ask for the module name and will create a shell for a new macro automatically, with a name other than Sub. It is easier however to activate Edit and enter the macro yourself.

Go past the End Sub that you see and enter the following text:

Function MyValue( ) 
	MyValue = 56
End Function

Go back to the spreadsheet and in one of the cells enter the formula =MyValue(). The value 56 should appear in the cell. Notice to return a value from a macro you assign a value to the function name.

If you see "#REF!" you most likely have another cell in your spreadsheet with the value "MyValue" in it. If you do, then delete that cell and retest the Macro. There appears to be a bug in the feature known as "Automatically find column and row labels" which causes this, you can disable this feature in Tools | Options | Spreadsheet | Calculate

Passing Values

This example shows you how to create a macro that takes some parameters and performs a simple computation on them. Here we will calculate the volume of a box.

Function BoxVolume( Length, Width, Height )
	BoxVolume = Length * Width * Height
End Function

In your spreadsheet you can use this like a normal function now. Create the following data in your sheet to try it out:

     A       B
1 Length:  57
2 Height:  45.3
3 Width:   19.2
4 Volume:  =BOXVOLUME(B1;B2;B3)

Then with any luck B4 should show 49576.32

Specifying Types

In many cases not specifying a type for the variable is fine, but usually it is not. The rules for automatic converstion aren't always clear, and sometimes untyped variables cause macros to fail (sometimes rightfully so, sometimes not).

The previous example is modified below to add type information.

Function BoxVolume( Length As Double, Width As Double, Height As Double ) As Double
	BoxVolume = Length * Width * Height
End Function

This specifies that each parameter is a Double and the return value is also a Double. When calling this macro now with invalid data, such as non-numeric strings, they will be converted to double, or default to the value 0.

Calling Internal Functions

It is possible to call internal spreadsheet functions, such as Sum. You will need to access the FunctionAccess and pass it an array of the arguments you wish to give it.

Here is a simple example calling the Trunc function -- which truncates the number of decimal places in a number. This sample both displays the result and provides it as a return value.

Function Sample( )
	Dim args( 1 to 2 ) As Variant
	args(1) = 10.1234
	args(2) = 2
	
	oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
	Sample = oFunctionAccess.callFunction( "Trunc", args() ) 
	Print Sample
End Function

If you need to pass arrays to a function, such as Sum please also refer to the Arrays Section as well.