Ask Your Question
1

calc - loop through all cells in the current selection

asked 2020-02-10 06:31:42 +0100

stabiloboss gravatar image

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!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2020-12-24 12:26:50 +0100

Lupp gravatar image

updated 2020-12-24 12:41:16 +0100

(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
edit flag offensive delete link more

Comments

thank you!

stabiloboss gravatar imagestabiloboss ( 2020-12-25 04:37:05 +0100 )edit
1

answered 2020-12-23 14:47:24 +0100

igorlius gravatar image

updated 2020-12-24 09:45:35 +0100

Most likely to late, but

these might be help/usefull for you

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.

edit flag offensive delete link more

Comments

1

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

stabiloboss gravatar imagestabiloboss ( 2020-12-24 08:49:01 +0100 )edit

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.

stabiloboss gravatar imagestabiloboss ( 2020-12-25 04:36:54 +0100 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2020-02-10 06:31:42 +0100

Seen: 267 times

Last updated: Dec 24 '20