2

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?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Jay
  • 75
  • 1
  • 9
  • Thanks! I did not realize I needed to accept answers. I believe I have accepted all of them where appropriate – Jay Mar 22 '18 at 01:30

1 Answers1

2

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.

Jim K
  • 12,824
  • 2
  • 22
  • 51
  • Thank you. I figured I could write the above code but was trying to replicate the exercises in the book for LibreOffice. I am not trying to learn MS Office VBA. I was adapting a book written for Excel. But as I got further into the book the limitations of Calc got greater and greater. – Jay Mar 22 '18 at 13:49
  • "Limitations of Calc" - Compared with MS Office, LO is limited in many ways. Compared with LO, MS Office is limited in many ways. I think it's more accurate to say they are simply different, and problems come when trying to do what works with one suite in the other suite. – Jim K Mar 23 '18 at 15:09