Writing a Macro in LibreOffice Calc – Getting Started
LibreOfice provides a way to write your own macro to automate various repetitive tasks in your office application. You can use Python or basic for your macro development. This tutorial focuses on writing a basic ‘Hello World’ macro using basic in LibreOffice calc.
We are going to create a macro which would put the string ‘Hello World’ in the first cell of LibreOffice calc i.e. the cell of row 1 and col A.
Creating the Macro
- Open LibreOffice Calc from
Applications => Office => LibreOffice Calc.
If you are using Unity, search “calc” from dash search bar.
- Go to the option from menu:
Tools ==> Macros ==> Organize Macros ==> LibreOffice Basic. Below ‘LibreOffice basic macros’ window will open.
- Give your desired name in macro name box and click New. You can use any name you want. For this tutorial I have used hello_world.
- Once you have clicked the New button, below macro editor will open. Here are some things to note in this window. This is the place where you should be writing your code, debugging your code etc. You can see the name of the macro became the function name of your basic macro.
- Now, its time to code the first macro. Lets declare two variable of type objects.
dim document as object dim dispatcher as object
Lets assign two values to above variables.
document = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
ThisComponent refers to the current document. In LibreOffice, everything you do, e.g type, color, insert, is “watched” by a controller. The controller then dispatch the changes to the document frame i.e. the main window area of the Calc. So the document variable refers to the main area of Calc.
createUnoService creates an instance of the DispatchHelper service. This service will help us to dispatch the tasks from macro to the frame. Almost all LibreOffice macro tasks can be executed using dispatcher.
Now we will declare an array of properties. Properties are always in a name/value pair. Thus the name contains the property name and value contains the value of that property.
dim args1(0) as new com.sun.star.beans.PropertyValue dim args2(0) as new com.sun.star.beans.PropertyValue
Our objective is to put 'Hello World' in the first Cell. To point the first cell A1 and put a text, we would use two properties - 'ToPoint' and 'StringName'.
args1(0).Name = "ToPoint" args1(0).Value = "$A$1" args2(0).Name = "StringName" args2(0).Value = "Hello World!"
Once we set the properties, its time to call the dispatch to send these to the document. So call the executeDispatch event of the dispatcher using two commands – a) “.uno:GoToCell” b) “.uno:EnterString”. These commands tells the frame what needs to be executed and also pass the entire property array with values.
Run the Macro
- Its time to run the macro. To run the macro, press F5 or click Run Macro from tool bar (see above).
- After execution, the messagebox would popup. If you go back and check the Calc spreadsheet, you should see ‘Hello World!’ is written in first Cell.
REM ***** BASIC ***** sub hello_world dim document as object dim dispatcher as object document = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") dim args1(0) as new com.sun.star.beans.PropertyValue dim args2(0) as new com.sun.star.beans.PropertyValue args1(0).Name = "ToPoint" args1(0).Value = "$A$1" dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1()) args2(0).Name = "StringName" args2(0).Value = "Hello World!" dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2()) msgbox "Completed!" end sub
Looking for Something Else?
If you are looking for something else in LibreOffice macro tutorials, Or, wants to learn more about it, please follow below link for complete Macro Tutorials Index: