Writing a Macro in LibreOffice Calc – Getting Started

Advertisement

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.

Macro Objective

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.

LibreOffice_1

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

LibreOffice_2

    • 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.

LibreOffice_3

    • 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.

LibreOffice_4

    • 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.

      Now put a messagebox to notify when the execution is completed.
      LibreOffice_5

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.

LibreOffice_6

LibreOffice_7

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

LibreOffice Macro Tutorial Index

You may also like...

Translate »