Get the Selection Cell and Range Address using Macro in LibreOffice

Advertisement

This tutorial will show how to get the human readable address of selected cells and ranges using basic macro.

Selections

In Calc, one can select a single cell Or multiple cells i.e. Ranges. Often it was required to know what is the current selection and its addresses.

Declare some variables to hold activeCell and conversion instances.

Dim oActiveCell
Dim oConv

Using the getCurrentSelection method, get a hold of the current selection of the controller.

oActiveCell = ThisComponent.getCurrentSelection()

To get the address of selected cell and selected range, two different services should be used – com.sun.star.table.CellRangeAddressConversion and com.sun.star.table.CellAddressConversion. Create an instance of these to services using createInstance method.

  oConv = ThisComponent.createInstance("com.sun.star.table.CellRangeAddressConversion")
  oConv = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")

Once this instance is created, the Address property can be filled up from the selected cell’s/range’s address by using below snippet:

  oConv.Address = oActiveCell.getRangeAddress
  oConv.Address = oActiveCell.getCellAddress

Now, two important methods of conversion object would return the human readable address of cell/range. They are UserInterfaceRepresentation and PersistentRepresentation

  msgbox  oConv.UserInterfaceRepresentation & _
          "  " & oConv.PersistentRepresentation

UserInterfaceRepresentation would return the cell column and row i.e. A1, B3 etc when a single cell is selected and for range it would return A1:C3, B1:E4 etc. PersistentRepresentation would return the same thing with the current worksheet name i.e. Sheet1.A1, Sheet1.B3, Sheet1.A1:Sheet1.C3, Sheet1.B1:Sheet1.E4 etc. All the values returned as string and can be processed accordingly.

Running the Macro

Two outputs with a cell selection and a range selection

getCellAddress Demo

getCellAddress Demo

getRangeAddress Demo

getRangeAddress Demo

Complete Macro

Cells

Sub get_cell_address

    oActiveCell = ThisComponent.getCurrentSelection()
    
    oConv = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
    oConv.Address = oActiveCell.getCellAddress
    
    msgbox  oConv.UserInterfaceRepresentation & _
      "  " & oConv.PersistentRepresentation
    
End Sub

Ranges

Sub get_range_address

    oActiveCell = ThisComponent.getCurrentSelection()
       
    oConv = ThisComponent.createInstance("com.sun.star.table.CellRangeAddressConversion")
    oConv.Address = oActiveCell.getRangeAddress
    
    msgbox  oConv.UserInterfaceRepresentation & _
      "  " & oConv.PersistentRepresentation

End Sub

Function References

 

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

  • Daniel Lopes

    Hello, after the execution arises amensagem below:

    BASIC run-time error.
    Property or method not found: getCellAddress

    How to solve?

    • Please post your entire code here so that we can help.

  • Adriel Shawn

    hi guys can i ask a question?

    how can you get the data of the highlighted cell? and search it into another sheet.

    is that possible?

    thank you in advance

    hope you help me guys

    newbie here.

    • You can get the selected cell value using below:

      Dim oDoc, oSel
      oDoc = ThisComponent
      oSel = oDoc.getCurrentSelection()
      oSel.getString()

      This is described in below article:
      http://www.debugpoint.com/2015/03/calc-cell-selection-processing-using-macro/

      • Adriel Shawn

        sorry i didnt specify my question sir Giri. i wanna get the data of the specific cell that i highlighted.
        example: i highlight a1 to a7. i wanna get the data of a7 and search it in sheet2 and deleting that row.
        sorry for wrong question. hope you help me thanks a lot.

      • Adriel Shawn

        and whats the code for search after you get the data.. thanks in advance Mr. Giri.

Translate »