This tutorial will show how to get the human readable address of selected cells and ranges using basic macro.
Table of Contents
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
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:
