How do I copy a cell value into a macro variable in Calc?

[davjam]

I am writing a macro to process values in a Calc spreadsheet. I need to copy the value in each cell (as selected) into a variable, process it and then paste the result back into either the same cell or a cell relative to the original cell (eg: to the right).

How do I do this?

In MS Office I would use something like a variable xstring = ActiveCell.Value and then ActiveCell.Value = xstring.

Your response probably works (not tested yet), but it is specific to row and col being known. I would like to be able to select the range of cells using my mouse (from 1 to many) and run the macro through the entire range. Your code suggests that row and col are hard coded.

[JohnSUN]

To work with the current selection, you can use the method .getCurrentSelection()

oCurrentSelection = ThisComponent.getCurrentSelection()

However, there is a slight difficulty. A method can return a single cell, a range of cells, or several ranges of cells. Additional analysis and branching of the algorithm will be required for different cases.
If you are only interested in the active cell (the one that is surrounded by a black frame), then you can use the following function:

Function ActiveCell(Optional iSheet As Long, Optional oDoc As Variant) As Variant
Dim arrayOfString()
Dim lRow&, lColumn&
Dim tmpString$
Dim oCurrentController
Dim oSheets As Variant
Dim oSheet As Variant
   ActiveCell = Nothing
   On Error GOTO unknownErr
   If IsMissing(oDoc) Then oDoc = ThisComponent 
   If NOT oDoc.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then Exit Function
   oCurrentController = oDoc.getCurrentController() 
   If IsNull(oCurrentController) Or IsEmpty(oCurrentController) Then Exit Function
   If IsMissing(iSheet) Then 
      oSheet = oCurrentController.getActiveSheet() 
      iSheet = oSheet.getRangeAddress().Sheet 
   Else 
      If (iSheet < 0) Then Exit Function 
      oSheets = oDoc.getSheets() 
      If (iSheet >= oSheets.getCount()) Then Exit Function 
      oSheet = oSheets.getByIndex(iSheet) 
   EndIf 
   tmpString = oCurrentController.getViewData() 
   arrayOfString() = Split(tmpString, ";") 
   If UBound(arrayOfString) < (3 + iSheet) Then Exit Function 
   tmpString = arrayOfString(3 + iSheet) 
   If InStr(tmpString,"+") > 0 Then 
      arrayOfString() = Split(tmpString, "+") 
   Else 
      arrayOfString() = Split(tmpString, "/") 
   EndIf 
   lColumn = CLng(arrayOfString(0)) 
   lRow = CLng(arrayOfString(1)) 
   On Error GOTO 0
   ActiveCell = oSheet.getCellByPosition(lColumn, lRow)
unknownErr:
End Function

Example of use:

oCell = ActiveCell()
sText = oCell.getString()
oCell.setString("Old value " & sText)

[davjam]

This works - or so it seems. Does it cycle through a selected range of cells? Presumably this would avoid having to do an ActiveCell.Offset(row,col).Select as in Excel?

Where do I find listings of object methods? eg: oSheet.getCellByPosition(lColumn, lRow)

[JohnSUN]

Try to ask Google Right now I ask “getcellbyposition calc” and get the second link

[Cat22]

Try this (I’m winging it here so YMMV):

Dim aCell As Object
Dim s as String
Dim fm as String
Dim f as float
    aCell = Sheet.getCellByPosition( col, row )
    s = aCell.String
    f = aCell.Value
    fm = aCell.Formula