Dim oSheets
Dim oSheet
Dim oCell
oSheets = ThisComponent.getSheets()
For i = 0 To oSheets.getCount() - 1
    oSheet = oSheets.getByIndex(i)
    oCell = oSheet.getCellByPosition(0, 1) ' GetCell A2
    TheSum = TheSum + oCell.getValue()
Next
SumCellsAllSheets = TheSum

Use oSheet.getCellRangeByName("A2") to return a range of cells by name.

Cell = Sheet.getCellRangeByName("A1")
Cell = Sheet.getCellByPosition(c, r)