How to for each cell in range?

for each cell in .getCellRangeByName(“A1:Z1”)

Inadmissible value or data type.
Data type mismatch.

Need to read the text of each cell in the range (“A1:Z1”).

VBA is simple but trying to remove VBA support need.

for each cell in .Range(“A1:Z1”)
if cell.value = … then …
next

How to do in basic without VBA support?

You can do this trick if you process not the .table.XCellRange type range, but the .sheet.XSheetCellRanges type.

The developers of OpenOffice.org wrote about it this way:
Cell range collections are represented by the service com.sun.star.sheet.SheetCellRanges. They are returned by several methods, for instance the cell query methods of com.sun.star.sheet.SheetRangesQuery
MRI for Sheet methods says the same thing - .sheet.XSheetCellRanges return only queries, other methods are returned a .table.XCell or a .table.XCellRange

So,

Sub showText()
Dim oSheet As Variant
Dim oCellRangeByName As Variant
Dim oCellRangeForEach As Variant
Dim oCell As Variant
	oSheet = ThisComponent.getSheets().getByIndex(0)
	oCellRangeByName = oSheet.getCellRangeByName("A1:Z1")
	oCellRangeForEach = oCellRangeByName.queryContentCells(7)
	For Each oCell In oCellRangeForEach.getCells()
		Print oCell.getString()
	Next oCell
End Sub

Pay attention! This is suitable for the task that you described in the question - getting cell texts. But if you want to get EVERY cell in a range for some other task, you have to keep in mind that getCells() will skip some of the cells.
This is not described fully enough in the documentation and can be overlooked:
image
Should read “empty cells will be skipped

1 Like

I don’t think there is a reasonable (and somehow efficient) way to go through all the cells of a CellRange using the For-Each construct.
If you definitely prefer “For-Each” over different kinds of loops, you can do it for the DataArray of a CellRange as shown in

Sub demo4you1()
myFixSheet = ThisComponent.Sheets(0)
myHardCodedRange = myFixSheet.getCellRangeByName("A1:Z2")
r = 0
For Each stripe In myHardCodedRange.DataArray()
 r = r + 1
 c = 0
 For Each element In stripe
  c = c + 1
  If (TypeName(element)="String") AND (element<>"") Then Print r, c, element
 Next element
Next stripe
End Sub

and if you don’t need to regard which cell returned what string, you can simpliyfy this by omitting r and c.

I would rather suggest to create nested loops for r and c, and to create the single cells -if needed at all- based on these indices. (You will already know that such indices are 0-based in the LibO API.) Doing it this way, your Sub may look like

Sub demo4you2()
myFixSheet = ThisComponent.Sheets(0)
myHardCodedRange = myFixSheet.getCellRangeByName("A1:Z2")
With myHardCodedRange.RangeAddress
 For r = .StartRow To .EndRow
  For c = .StartColumn To .EndColumn
   rc_cell = myFixSheet.getCellByPosition(c, r)
   Print rc_cell.AbsoluteName, rc_cell.Type, IIf(rc_cell.String<>"", rc_cell.String, ":empty:")
  Next c
 Next r
End With
End Sub

Anyway: If sticking to LibreOffice, you shouldn’t “think the VBA way”. And if VBA is actually simple, is a different question. It surely isn’t “simply better”.