Ask Your Question
1

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

asked 2018-10-02 16:28:49 +0200

davjam gravatar image

updated 2018-10-02 16:51:47 +0200

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
2

answered 2018-10-02 18:03:31 +0200

JohnSUN gravatar image

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)
edit flag offensive delete link more

Comments

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)

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

Try to ask Google :-) Right now I ask "getcellbyposition calc" and get the second link

JohnSUN gravatar imageJohnSUN ( 2018-10-05 18:46:33 +0200 )edit
0

answered 2018-10-02 16:45:40 +0200

calcUser gravatar image

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
    

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-10-02 16:28:49 +0200

Seen: 286 times

Last updated: Oct 02 '18