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.knu...@gmail.com> Date: 2013/2/7 Subject: Re: [libreoffice-users] Macro to write a formula To: "T. R. Valentine" <trvalent...@gmail.com> 2013/2/7 T. R. Valentine <trvalent...@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+h...@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+h...@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