Calc Cell Selection Processing Using Macro
In this tutorial I will cover selection mechanism in LibreOffice Calc cells and via macro.
First we need to get hold of the Calc workbook that is open. To do that, you can use
ThisComponent and hold the return object in a variable.
To find out the selections done by user in a Calc sheet, we will use the
getCurrentSelection() method of Xcomponent interface. This method “provides read access on current selection on controller”. It returns the current selection on the current controller.
Dim oDoc, oSel oDoc = ThisComponent oSel = oDoc.getCurrentSelection()
If there are no selection, getCurrentSelection returns NULL. You can use
IsNullfunction to determine whether anything is selected or not.
By using only
oSel, it is not possible to determine the selections. We need to take help of the method
supportService method “tests whether the specified service is supported, i.e. implemented by the implementation”. If its supported, it returns TRUE, else it returns FALSE.
There are list of services available that can be tested using this function. For this tutorial we will use below services:
An entire list of service can be found here [LibreOffice Reference]:
One cell is selected
To check whether one cell is selected, use
com.sun.star.sheet.SheetCell service. You can also get the value of that selected cell by using
getString() method of selection object.
If oSel.supportsService("com.sun.star.sheet.SheetCell") Then MsgBox "One Cell selected and it contains: " & oSel.getString() End If
One range is selected
To identify a range of cells selection – say, C5:F7, use the service
If oSel.supportsService("com.sun.star.sheet.SheetCellrange") Then MsgBox "One Cell Range selected" End If
Multiple ranges are selected
To identify multiple ranges of cells selection, use the service
If oSel.supportsService("com.sun.star.sheet.SheetCellRanges") Then MsgBox "Multiple Cell Ranges selected. Total=" & oSel.getCount() End If
Running the Macro
Sub Main Dim oDoc, oSel oDoc = ThisComponent oSel = oDoc.getCurrentSelection() If oSel.supportsService("com.sun.star.sheet.SheetCell") Then MsgBox "One Cell selected and it contains: " & oSel.getString() Else If oSel.supportsService("com.sun.star.sheet.SheetCellRange") Then MsgBox "One Cell Range selected" Else If oSel.supportsService("com.sun.star.sheet.SheetCellRanges") Then Msgbox "Multiple Cell Ranges selected. Total=" & oSel.getCount() Else Print "Somethine else is selected." End If End If End If End Sub
Function References – Used in this article
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: