Hello everyone. I have a quick questions I’m having trouble finding an answer for. Thank you in advance for your time/replies.
So, the calc spreadsheet I’m making has a somewhat large programmed macro and within that macro I have the following code line: If oDoc.currentSelection.supportsService(“com.sun.star.sheet.SheetCell”) Then do some other lines of code. Now, this used to work and has been tested already, however, I’ve changed the selected cell (i.e., a SheetCell) to a merged cell. How do I make the block of code contained within the If statement run if the currentSelection is a merged cell?
If a merged area is selected -and nothing else- the .CurrentSelection is the single-cell object covering by its expanded area the additional cells. Therefore the selection supports the .SheetCell service. The cell itself doesn’t “know” much about the merged range. If you want to get the merged area, you need to create a cursor object for it and to apply .collapseToMergedArea (where nobody must understand the “collapse”).
To tell if it is part of a merged area, each cell has the Boolean property .IsMerged
One thread, one question here. Please edit the subject and the text of your question to comply with the rule.
The merged cells are CellRange. A CellRange has some Cells.
You can distinguish between the two state by usage the value of the property ImplementationName, what is:
“ScCellRangeObj” - string, for currently selected merged cells
“ScCellObj” - string, for the currently selected single cell
I suggest you to download, install and use one of the excellent Object inspection tools: XrayTool or MRI. Then you will able to examine the existing properties, methods and others of the programming objects.
Please avoid use (and advertise) of ImplementationName. I don’t know who came with the idea to use a property that is not guaranteed to be stable. The implementation name is considered an implementation detail.
`If oDoc.currentSelection.supportsService(“com.sun.star.sheet.SheetCell”) Then ...`
You didn’t post the “block of code”, and you did only tell what not happened. Someone trying to help you would need to know what happened. Was there an error message casted by the Basic interpreter/IDE? If so, how was it worded? Was a unexpected observation in the sheet? Did you inspect the .CurrentSelection in the IDE watch? What object type did it show?..
The following Sub works exactly as expected for me.
Sub selTesting()
currSel = ThisComponent.CurrentSelection
If currSel.supportsService("com.sun.star.sheet.SheetCell") Then
sheet = currSel.Spreadsheet
MsgBox("Current selection supports single cell services")
MsgBox("It returns " & currSel.IsMerged & " asked for .IsMerged.")
sheetCursor = sheet.createCursorByRange(currSel)
sheetCursor.collapseToMergedArea()
If currSel.IsMerged Then
MsgBox("The merged range it belongs to is " & sheetCursor.AbsoluteName & " .")
EndIf
Else
MsgBox("The current selection is not a single cell.")
EndIf
End Sub
Thank you for the replies everyone. I believe I have this one figured out now. Basically, I didn’t know it at the time but I was looking for the currSel.IsMerged element (and, by continuation, the CellRange elements). I guess it makes sense why the If statement from my original post wouldn’t fire if a cell range was selected even though it would fire if a single cell was selected. If the API is going to have single cells separated out from cell ranges then it would also need a way to tell whether the object was a single cell or a cell range. Thanks again for your time everyone.
Actually, now that I’ve messed around with it for another 30 min (and also messed around with the errors inside the If block after adding the IsMerged to it), I believe I’ve found the real culprit.
The thread in the following link is relevant.
Basically, my previous comment(s) were wrong. What’s actually going on is that the SheetCell service is supported. However, it can’t do the check because when the selected cell is a merged cell, you apparently need to add in a getCellByPosition(0,0) first in order for it to actually find the cell to do the check.
To get the IF statement from my original post to fire when a merged cell is selected, it needs to be edited as follows:
If oDoc.currentSelection.getCellByPosition(0,0).supportsService(“com.sun.star.sheet.SheetCell”)
Then it’ll work. Note that the getCellByPosition(0,0) also needs to be added to any code lines attempting to edit/change/view the contents of a merged cell.
Hope this helps anyone coming across this thread in the future.
The objects we actually handle as the “values” of variables when programming based on the API mostly bundle more than one relevant supported service. I’m tempted to call that “merging”, and merging is a very doubtable idea in many cases.
In the current case I don’t know a way to get an object supporting the service com.sun.star.sheet.SheetCell, but not also supporting the service com.sun.star.sheet.SheetCellRange. And a “true CellRange” object might then be seen as one supporting the second service, but not the first one.
Anyway, the relevant distinctions should always be made asking if the service we actually need is supported.
There are, however, complications not vanishing this way. In specific I have problems to understand for what reasons once was decided, to present a cell object as identical to what you get for cell.Text.
Sub demo() REM Expects a single cell selected
cObj = ThisComponent.CurrentSelection
tObj = cObj.Text
Print EqualUNOObjects(cObj, tObj)
End Sub
