Invariably, macros written for use within the Calc application will need to access the contents of the cells on a spreadsheet. This tip is an introduction to the various available methods.

The three methods we will look at are getCellByPositiongetCellRangeByPosition and getCellRangeByName

The function that is first encountered for most people is getCellByPosition. In the sample below, we access cell A1 (on Sheet1)


Sub getCellInfo
‘get the first sheet of the spreadsheet doc
xSheet = ThisComponent.Sheets(0)

‘Get value of Cell A1
A1_value = xSheet.getCellByPosition(0,0).value

print A1_value

End Sub

The second example shows the use of getCellRangeByName and may be easier to use – because the cells are referenced by the traditional column/row identifiers that are displayed along each axis. However, for applications requiring looping through an array of cells, getCellByPosition is easier to use.

Sub getCellInfo
‘get the first sheet of the spreadsheet doc
xSheet = ThisComponent.Sheets(0)

‘Get value of Cell A3
A3_value = xSheet.getCellRangeByName(“A3”).value

print A3_value

End Sub


The next example shows how getCellInfo grabs an array of cells – myTable. A subsequent call to getCellByPosition for the myTable object is relative to the origin of this array.

Sub getCellInfoByRange
Dim myTable as Object

‘get the first sheet of the spreadsheet doc
xSheet = ThisComponent.Sheets(0)

‘Grab array A3:A5
myTable = xSheet.getCellRangeByName(“A3:A5”)
A5_value = myTable.getCellByPosition(0,2).value
print A5_value

End Sub

The final method that needs discussion is getCellRangeByPosition and the example below illustrates it’s use. It is equivalent in functionality to the previous example.

Sub getCellInfoByRange
Dim myTable as Object

‘get the first sheet of the spreadsheet doc
xSheet = ThisComponent.Sheets(0)

‘Grab array A3:A5
myTable = xSheet.getCellRangeByPosition(0,2,0,4)
A5_value = myTable.getCellByPosition(0,2).value
print A5_value

End Sub