Writing a Macro in LibreOffice Calc

LibreOffice 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 macro in LibreOffice with a ‘Hello World’ macro with basic.

Macro Objective

We are going to create a macro that 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.

- Go to the option from the menu: Tools ==> Macros ==> Organize Macros ==> LibreOffice Basic. Below ‘LibreOffice basic macros’ window will open.

- Give your desired name in the 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, the 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, it’s time to code the first macro. Let’s declare two variables of type objects.
```
dim document as object
dim dispatcher as object
```
  Let’s assign two values to the 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 dispatches 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 the 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, it’s 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 tell the frame what needs to be executed and also pass the entire property array with values.
  Now put a message box to notify when the execution is completed.

Run the Macro

- It’s time to run the macro. To run the macro, press F5 or click Run Macro from tool bar (see above).
- After execution, the message box would pop up. If you go back and check the Calc spreadsheet, you should see ‘Hello World!’ is written in the first Cell.

Complete Code

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 the below link for a complete Macro Tutorials Index:

LibreOffice Macro Tutorial Index

We bring the latest tech, software news and stuff that matters. Stay in touch via Telegram, Twitter, YouTube, and Facebook and never miss an update!