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

  • Adriel Shawn

    HI. im a newbie in libre basic calc. i have a program that will copy the highlighted row to another sheet. but my problem is how can i determine if the cell have data. because whats heppening here is my program is just replacing the 2nd data that i transfer.

    i would like to know the function for detecting if the cell have data if so go to next line. thanks in advance.

    #HelpMeSensei

  • Jan Rolih

    Hi, i am really new to basic. I am trying to make a macro, so when the dropdown menu looses focus, macro executs. The code is supposed to write chosen integer(id) into another temp table.
    I get this error
    BASIC runtime error.
    Sub-procedure or function procedure not defined.

    and the code:

    sub idVzacasno
    dim document as object
    dim scv as object
    dim macro as object
    document = ThisComponent.CurrentController.Frame
    svc = createUnoService(“com.sun.star.sheet.FunctionAccess”)
    makro(“INSERT INTO zacasna(id) SELECT id_Reverz FROM Reverz;”)

    end sub

    If someone has any idea what i am doing wrong please help.

    • Is this your entire macro? And at which line you are getting error?

  • Nicola Floris

    Is it possible to put a variable in the variable name?
    For example, i have
    Image1
    Image2
    Image3
    Image4

    Can i state something to change the number in the variable name as the result of another variable?

    • Yes it is possible but that depends what you want to achieve. Say you are creating multiple images at runtime with dynamic file name as you said – image1, image2 etc. Then you can do something like this:

      Dim i, c

      for i=1 to 10

      next

      • Nicola Floris

        Well basically i need to state a lot of variables to be able to call them later where needed

        FineURL1 = Foglio3.getCellbyPosition(12,2).String
        FineURL2 = Foglio3.getCellbyPosition(12,3).String
        FineURL3 = Foglio3.getCellbyPosition(12,4).String
        …….
        FineURL47 = Foglio3.getCellbyPosition(12,48).String
        FineURL48 = Foglio3.getCellbyPosition(12,49).String
        FineURL49 = Foglio3.getCellbyPosition(12,50).String

        my macro is very long just because i have a lot of variable to state, i know there could a shorter way but i still need to think about it xD

        • You should use array. Instead of declaring 49 variables, declare an array of size 49. Then you can refer them via subscript. Like this:

          Dim my_arr(49)

          for i=1 to 49

          my_arr(i) = “anything you want ” & i

          next

          after this execution, your array should contain value like this::

          my_arr(1) = anything you want 1

          my_arr(2) = anything you want 2

          my_arr(3) = anything you want 3

          my_arr(49) = anything you want 49

          Array concept: https://msdn.microsoft.com/en-us/library/wak0wfyt.aspx

          • Nicola Floris

            This is great!

            then i could just write this

            Dim img_arr(49)

            for i=1 to 49

            img_arr(i) = Foglio3.getCellbyPosition(12,i+1).String

            next

            and the job is done, to call a position on an array should i use

            img_arr(6) or img_arr6 ?

            • You can use img_arr(6) to refer any individual items inside an array.

              • Nicola Floris

                Amazing, i can save a lot of time and a lot of space with this 🙂
                I am also rewriting the whole macro because using the arrays i can use a smarter way to obtain things done 😉
                Thank you very much

  • surja

    Any book for beginners to learn macro programming for Calc?

    • There may be some books, but I didn’t find any. If you want to learn, follow the tutorials in this site/search Google/forums etc.

      • Vishal Jagani

        Hey ca you pls tell us that which language are use for Libreoffice macro , Is it VB script or any other language
        pls specify

        • You can use these languages to write macros in LibreOffice: Basic (similar to VB), Python

          • Vishal Jagani

            So these two language is fine to write macro in libreoffice right?

            • Yes.

              • Kesri_Lion

                Thnk you Mr. Giri,
                I thought libreoffice macro language is VB only
                Thnks for clarification that This is not VB but Its slimier to VB 🙂

  • João Alberto Garcia

    And what if instead of using a string, use the contents of a specific cell to be copied to cell A1?

    • João Alberto Garcia – There are many ways to do that. Say, you want to copy cell contents B1 to A1. You can try this:

      my_cell = ThisComponent.Sheets(0).getCellByPosition(1,0)
      target_cell = ThisComponent.Sheets(0).getCellByPosition(0,0)
      target_cell.Value = my_cell.Value

      Value should be used for numbers, String should be used for string. So you need to put a check on what type of value the cell contains. Have a look here: http://www.debugpoint.com/2014/09/libreoffice-workbook-worksheet-and-cell-processing-using-macro/

      • João Alberto Garcia

        Thank you, friend.

Translate »