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

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.

edit retag close merge delete

Sort by » oldest newest most voted

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()
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)

more

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)

( 2018-10-05 18:19:48 +0200 )edit

( 2018-10-05 18:46:33 +0200 )edit

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


more