I am using OpenOffice Calc spreadsheet formulas with psuedo-random numbers to generate arrays of arithmetic problems which I can easily update to creating new worksheets (I'm a teacher)
Problems are output as formula mark-ups in string form. OOo Math formulas use these string commands typed into the editor to display nicely formatted maths expressions.
I can do this next step manually:
1) go to source cell and copy string mark-up to clipboard
2) select target cell and clear existing contents and objects
3) create new Math object anchored to target cell
4) open Math editor window and paste in mark-up string
5) exit Math editor window and return cursor to source cell
Result: a nice maths expression of given arithmetic problem.
I need to be able to do this for entire columns of source cells on various sheets. ...even better, to then add a listener to dynamically update as sources are updated.
I found code here: Cell content inside formula that achieves this for a fixed pair of cells, but despite all my best efforts, I have had to admit defeat - generalising this code is simply beyond my expertise!
The absolute ideal would be a macro function that I could call like a spreadsheet function; with input arguments (sourceCell, targetCell, listenerON/OFF) that could run the above algorithm and dynamically update if required.
Can anybody help me? A solution like this, or any kind of workaround would be immensely helpful.
UPDATE 2016/10/27
Thank you Jim K, that did work, but use of the dispacher comes with a whole host of difficulties I hadn't foreseen. I just found Charlie Young's post in the OpenOffice forum which makes use of the API. I have included my adaptation of his code below.
Can anybody help me to integrate it into a function in a similar way as I've described? I don't know how to solve placement of the Math object in to the target cell.
The API code is great as it will create a new Math object each time the code is updated. Existing ones do need to be deleted though.
I think the limitation of not being able to delete existing objects from within a function is going to persist. Would this be the case even if done by a subroutine called by the function?
function InsertFormula(paraFromCell, paraToCell)
Dim oDoc As Object
Dim oSheet As Object
Dim oShape As Object
oDoc = ThisComponent
oSheet = oDoc.Sheets(0)
oShape = oDoc.createInstance("com.sun.star.drawing.OLE2Shape")
oShape.CLSID = "078B7ABA-54FC-457F-8551-6147e776a997"
oSheet.Drawpage.Add(oShape)
oShape.Model.Formula = paraFromCell
oShape.setSize(oShape.OriginalSize)
end function
NEXT UPDATE
I've been managing to solve my own problems quite quickly now...
I've decided to go with a sub, not a function, so I can access the sheet to delete existing objects. Code is attached - Source cells are in Column C and target cells in matching rows of Column A. So far I am only able to send objects to $A$1.
How do I anchor each new object to a specific cell?
REM ***** BASIC *****
Sub InsertThisFormula
Dim oDoc As Object
Dim oSheet As Object
Dim oShape As Object
Dim sourceCell As Object
Dim targetCell As Object
oDoc = ThisComponent
oSheet = oDoc.Sheets(1)
Dim n As Integer
n = 1 'number of rows of formulas
for i = 0 To n-1
rem loop through cells
sourceCell = oSheet.getCellByPosition(2, i)
targetCell = oSheet.getCellByPosition(0, i)
rem clear target cell object/s
targetCell.ClearContents(128)
oShape = oDoc.createInstance("com.sun.star.drawing.OLE2Shape")
oShape.CLSID = "078B7ABA-54FC-457F-8551-6147e776a997"
oSheet.Drawpage.Add(oShape)
oShape.Model.Formula = sourceCell.string
oShape.setSize(oShape.OriginalSize)
Next i
End Sub
Starting from Mifeet's example, add this to
My Macros:To run it, put this formula in cell C5:
Now when the values get updated, it creates another formula.
Note: I could not get the
.uno:Deletesection of Mifeet's code to work, perhaps because functions are not supposed to access other cells. This may require manually deleting the formulas before creating new ones.