Fwd: [libreoffice-users] Macro to write a formula

Yet again, I seem to have sent my reply directly to the original
poster, sorry again (and again and again…).

Here's my reply that I wrote yesterday (2013-02-07), except that I'll
do some minor corrections before sending it this time:

---------- Forwarded message ----------
From: Johnny Rosenberg <gurus.knugum@gmail.com>
Date: 2013/2/7
Subject: Re: [libreoffice-users] Macro to write a formula
To: "T. R. Valentine" <trvalentine@gmail.com>


2013/2/7 T. R. Valentine <trvalentine@gmail.com>:
> Is it possible?
>
> If so, coiuld I get a simple example, something like =TODAY() to go
> into the (currently) selected cell? I figure I can probably get it
> from there.

If you have a cell object (for instance
oCell=ThisComponent.getSheets().getByIndex(0).getCellByPosition(0,0)
or something like that), there is a method called setFormula().
This should work, I think:
oCell.setFormula("=TODAY()")

Of course there is a getFormula() method to get the current formula:
sMyFormula=oCell.getFormula()


If you didn't install xray, I'll suggest that you do. It's the best
tool that I know of to investigate variables, methods and more of an
object.
Using it, I just found that those methods mentioned are available for
cell selections too:

REM ***** BASIC *****

Option Explicit

Sub Main
    Dim oSelection As Object

    oSelection=ThisComponent.CurrentSelection
    oSelection.setFormula("=TODAY()")
End Sub


You can also create an array of arrays with many formulas and then
copy them to the spreadsheet all at once. This is much faster than
doing them cell by cell. The method for copying an array of arrays to
a cell range is called setFormulaArray().

It's, of course, important to get rows and columns right. Arrays of
arrays can be a bit tricky to work with. Here is a small function that
I use to transpose an array, which I use surprisingly often:
Function TransposeArray(A() As Variant) As Variant
    Dim oCalcFunction As Object

    oCalcFunction=createUnoService("com.sun.star.sheet.FunctionAccess")
    TransposeArray=oCalcFunction.callFunction("TRANSPOSE",Array(A()))
End Function


For instance, when you read a column with the getFormulaArray() method
(or similar, like getDataArray() for reading data from a cell range),
you always end up with an array of arrays that is hard to get data
from:

A=oSelection.getFormula()

As a result:
A(0)(0)=formula of A1
A(1)(0)=formula of A2
A(2)(0)=formula of A3
and so on.
But you can't do things like:
B=A(2)(0)
or
B=A(2,0)

If you transpose the array, however, you will have this:
A(0)(0)=formula of A1
A(0)(1)=formula of A2
A(0)(2)=formula of A3

Then you can just do:
A()=A(0)
and you will end up with this:
A(0)=formula of A1
A(1)=formula of A2
A(2)=formula of A3
which is something you can actually use…

After manipulating the array, you can then copy the result back to the
cell range again:
A()=Array(A())
TransposeArray
(A())
oSelection.setFormulaArray(A())

Well, something like that, anyway. I just typed that right now without
testing it.


Johnny Rosenberg
> TIA.
>
>
>
> --
> T. R. Valentine
> Your friends will argue with you. Your enemies don't care.
> 'When I get a little money I buy books; and if any is left I buy food
> and clothes.' -- Erasmus
>
> --
> For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be deleted

-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted