Calc Macros Dev

Writing a Macro in LibreOffice Calc: Getting Started

by
Arindam
16.7k 21

Planning to automate stuff in LibreOffice? Start writing your first LibreOffice Calc macro using this guide.

LibreOffice provides a way to write your 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 in the Basic programming language. 

Write your first macro in LibreOffice Calc

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 macro’s name 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, colour, 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.

The createUnoService creates an instance of the DispatchHelper service. This service will help us to dispatch the tasks from the 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 the value contains the value of that property.

dim args1(0) as new com.sun.star.beans.PropertyValue

Our objective is to put ‘Hello World’ in the first Cell. To point to the first cell A1 and put a text, we would use two properties – ToPoint and StringName.

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: .uno:GoToCell, and .uno:EnterString.

dim args2(0) as new com.sun.star.beans.PropertyValueargs1(0).Name = "ToPoint" args1(0).Value = "$A$1" args2(0).Name = "StringName" args2(0).Value = "Hello World!"

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.

Running the Macro

  • It’s time to run the macro. To run the macro, press F5 or click Run Macro from the toolbar (see above).
  • After execution, the message box would pop up. If you go back and check the Calc spreadsheet, you should see ‘Hello World!’ 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 more LibreOffice macro tutorials Or wants to learn more about it, please follow the below link for a complete Macro Tutorials Index:

Macro tutorial index
Exit mobile version