0

I want the user to be able to select cells, then have my function operate on those cells.

How do I do that? "ByRef" and "as Object" seem to do the right thing, but then Excel/{Libre,Open}Office reject the function call because the user's input is not an object.

Is there a way to find out what cells were selected for the function to be run on?

Even if I had to do something like find the address of the calling cell, then work backward to find the cell-names typed into that cell, that might work. I am open to any solution that lets my users write this function like a normal function, and has this function be able to modify the selected cells. Here is what I'm talking about:

Function SIMVOL( rF, ByRef mX, mY, cR, cS ) ' or Function SIMVOL(...mX as Object...)
    mX.value() = 10
    SIMVOL = mX.value() * mY
End Function
Community
  • 1
  • 1
SaburoutaMishima
  • 269
  • 4
  • 15
  • don't know about {Libre,Open}Office , but in excel you can't use UDF to modify cells content (in normal way) – Dmitry Pavliv Mar 04 '14 at 16:47
  • I've been able to hardcore addresses and lookup cell references from the document object. But to get cells as parameters, my users have to type "A1", "B1", etc, and they can't select cells with the mouse - because those selections resolve to data instead of addresses. – SaburoutaMishima Mar 04 '14 at 20:21

1 Answers1

0

Here's an example adapted from Andrew Pitonyak's OpenOffice Macro document. It should set you in the right direction. 

Sub DivideSelectionByTen()
  REM Divide the current selection by 10
  Dim oData()
  Dim oRow()
  Dim i As Integer
  Dim j As Integer

  Dim oRange As object
  Dim dDivisor As Double

  oRange = ThisComponent.getCurrentController().getSelection()

  dDivisor = 10

  oData() = oRange.getDataArray()
  For i = LBound(oData()) To UBound(oData())
    oRow() = oData(i)
    For j = LBound(oRow()) To UBound(oRow())
      oRow(j) = oRow(j) / dDivisor
    Next
  Next
  oRange.setDataArray(oData())
End Sub
Dave
  • 875
  • 6
  • 15