[Solved] How to loop through the ranges in SheetCellRanges?

[ooGuillaume]

Hello,

I'm getting the last modified content in a spreadsheet through the Sheet Events, say with parameter ChangedContents.

Calling the functions below, I know whether I'm dealing with a single cell, a single range, or multiple ranges.

Code: Select all

Dim SelectionIsCell, SelectionIsUniqueCellRange, SelectionIsMultipleCellRanges As Boolean

SelectionIsCell = ChangedContents.supportsService("com.sun.star.sheet.SheetCell")
SelectionIsUniqueCellRange = ChangedContents.supportsService("com.sun.star.sheet.SheetCellRange")
SelectionIsMultipleCellRanges = ChangedContents.supportsService("com.sun.star.sheet.SheetCellRanges")

But I can't find the right functions to loop through the multiple ranges.
For instance, I'd like to be able to call getRangeAddress or getCellRangeByPosition on each range of ChangedContents, when it supports SheetCellRanges.

Thank you.

[Zizi64]

Do you use one of the object inspection tools (MRI or XrayTool) for your programming tasks?

You can check the existing properties, methods, interfaces (and others) by usage an object inspection tool.

[Lupp]

In principle you don't need to treat "single cell" in a specific way because any single cell also supports the SheetCellRange service. There may be some effect concerning the efficiency, of course.

As an example I post a piece of code I just wrote in a playful way induced by a question in a different forum:

Code: Select all

Sub doSomething(pEvent)
REM pEvent is supposed to pass all the cells having changed when a sheet event 'Content changed' was thrown.
rgs = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
If pEvent.supportsService("com.sun.star.sheet.SheetCellRange") Then
  REM Will also be executed if a single cell was changed.
  rgs.addRangeAddress(pEvent.RangeAddress, True)
Else 
  rgs = pEvent
End If
u = rgs.Count - 1
For  j = 0 To u
  rg = rgs(j)
  For c = 0 To rg.Columns.Count - 1
    For r = 0 To rg.Rows.Count - 1
      oneCell = rg.getCellByPosition(c, r)
      '... Something to do
      'Probably a conditional Exit For
    Next r
    'Probably a conditional Exit For
  Next c
Next j
'... Some statements
End Sub

The loops are forced into a common structure here. Even a single cell is treated via a SheetCellRanges object.
(I would prefer Calc to handle selections this way. It doesn't however. Draw to the contrary always returns a collection even if a single shape is selected.

[Villeroy]

1) XEnumerationAccess:
viewtopic.php?f=20&t=23685&p=107958&hil ... es#p107958

2) XIndexAccess:
For i = 0 to obj.getCount()-1
rg = obj.getByIndex(i)

3) if you want to work with the "coordinates" of ranges:
a() = obj.getRangeAddresses() returns an array of range address structs describing the locations of ranges.

[ooGuillaume]

Three useful answers in less than two hours, thank you very much!

I installed MRI but still need to read through the documentation to find out what it's all about...

[Villeroy]

[Tutorial] Introduction into object inspection with MRI