Calc - loop through all cells in the current selection

[stabiloboss]

Hi,

I need a macro (in basic) to loop through all the cells in the current selection. This selection could have been made manually or with code.

This sounds really basic, but I haven’t been able to find the answer. So how do I do this?

I also would like to know where I should have found the answer myself, but I will ask this question in a different post.

Thanks!

[igorlius]

Most likely to late, but

these might be help/usefull for you

• LibreOffice: Namespace List
• www.pitonyak.org/oo.php
• https://www.pitonyak.org/OOME_4_0.odt

The odt contains extensive examples for looping through cell ranges.

Update, since this still seems to be of interesst, here is the macro:

Sub LoopSelection
   Set oRange = ThisComponent.CurrentSelection
    For i = 0 To oRange.Rows.getCount() - 1             
        For j = 0 To oRange.Columns.getCount() - 1
            Set oCell = oRange.getCellByPosition( j, i )
            Print oCell.String
        Next
    Next 
end Sub

If you need any further help, just ask in the comment.

Hope it helps.

[stabiloboss]

thank you very much, for the macro and for the links!

[stabiloboss]

Hmm, I’m not sure what the proper course of action is on this forum, but I think I should accept Lupp’s answer as it covers a wider range of situations. i hope I’m not offending.

[Lupp]

(In addition to what @igorlius altready posted:)

The CurrentSelection of a spreadsheet document is’nt always an object of the same type (supporting the same services). If nothing explicitly is selected, the cell having the keyboard focus is returned. You also get a single cell returned if one cell is selected (shown highlighted). The focus isn’t evaluated now. In both these cases the service com.sun.star.sheet.SheetCellRange is also supported by the selection, and the code suggested by @igorlius can work with only the value 0 for i and for j as well. If a single range of more than one cell is selected: No problem, that’s the standard case.

If more than one single range is selected, the mentioned service will not be supported. The relevant service you need to rely on now is com.sun.star.sheet.SheetCellRanges (Regard the plural-s.)

If you want code also working with such a selection, you can use:

Sub loopThroughSingleCellsOfTheCurrentSelection()
REM The CurrentSelection will even contain the stacked ranges
REM of additional sheets IF MORE THAN ONE SHEET IS SELECTED.
sel =ThisComponent.CurrentSelection
If sel.supportsService("com.sun.star.sheet.SheetCellRanges") Then
  rgs = sel
Else
  If NOT sel.supportsService("com.sun.star.sheet.SheetCellRange") Then Exit Sub
  rgs = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
  rgs.addRangeAddress(sel.RangeAddress, False)
End If
For Each rg In rgs
  uR = rg.Rows.Count - 1
  uC = rg.Columns.Count - 1
  For r = 0 To uR
    For c = 0 To uC
      rcCell = rg.getCellByPosition(c, r)
      REM Do here whatever you want to do with the single cell. Example:
      Print rcCell.AbsoluteName
    Next c
  Next r
Next rg
End Sub

[stabiloboss]

thank you!