Using built in Calc function when recording a macro

Question

I am working through Excel for Engineers and trying to adapt it for LibreOffice Calc. I have run into a problem. I know this is easier to do without using macros but humor me. One of the exercises is to start recording a macro, type:

=RAND()

hit enter and stop recording.

When I run the macro nothing happens. I try to use any other Calc built in function and the same thing happens. Looking at the macro basic file and sure enough nothing is happening.

Can I use built in functions when recording a macro? If so how?

Answer 1

Currently, the LibreOffice macro recorder does not record adding built-in functions. In fact, the recorder has many limitations and generally results in poor code (mostly using the dispatcher).

Instead, write API code based on documentation such as https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Function_Handling.

Sub CallSpreadsheetFunction
    funcAcc = createUnoService("com.sun.star.sheet.FunctionAccess")
    oSheet = ThisComponent.getSheets().getByIndex(0)
    oCell = oSheet.getCellRangeByName("A1")
    oCell.setValue(funcAcc.callFunction("RAND", Array()))
End Sub

A good place to start learning about LibreOffice macros is http://www.pitonyak.org/oo.php.

Note that learning LibreOffice Basic will not help very much to learn about MS Office VBA. The two languages and platforms are quite different.