[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
TerryE
Super User

Joined: 16 Jul 2006
Posts: 550
Location: UK

Posted: Mon Jul 24, 2006 7:32 pm    Post subject: How To: paste a formula to a Column in Calc

Quite often I've wanted to add a formula to a column in Excel. This is easy once you have mastered the trick of RC notation. Even if you haven't you just type the formula into the first field, toggle into the debugger immediate pane (yup, the VBA IDE has one of those) and type ?ActiveCell.FormulaR1C1 and up it comes to paste into your code. So then you do something like
 Code: '***** VBA EXCEL Version **** nRows = vbaSheet.Cells.SpecialCells(xlCellTypeLastCell).Row    vbaSheet.Range(“D2).Resize(nRows-1).FormulaR1C1 = _     “=R[-4]C[-3]+R[-3]C[-3]+R[-2]C[-3]”
I know this looks yukky, but you don't have to compose it this form. The real advantage of R1C1 notation in that it is cell relative so the formula is exactly the same for the whole row.

Now in OOo Basic:
• We don't have a cell relative format so we have to apply the formula to the first cell
• We can then fill the entire range. Note that the Calc Fill function does automatically relocate formulae.
• If you are doing large ranges then it is well worth having enableAutomaticCalculation turned off and do a manual calculate
• Sometimes the first thing that you want to do after doing this is to anchor the fields by replacing the formula with the values. I realise that if you are going to do this then you could just as easily do this with a programmatic For ... Next loop, but my response to this is Time it!. Using the Calc calculation engine is faster and is easier to code.

So here is the OOo Basic Code
 Code: ***** OOB UNO Calc Version **** PastFormulaToColumn(sFormula, oSheet, nColumn, nStartRow, nEndRow, bPasteValues)
That's even easier than VBA! Well apart from this canned routine below that you will need to put in your code or library.
 Code: ***** OOB UNO Calc Version **** Function PastFormulaToColumn(sFormula, oSheet, nColumn, nStartRow, _                         Optional ByVal nEndRow, Optional ByVal bPasteValues) ' ' Routine to Paste Array into a specified column on a worksheet in thisComponent ' Dim oCursor, oRange, vData On Error Goto PasteHandler ' Apply default values for last two arguements If IsMissing(nEndRow) Then nEndRow = -1 If IsMissing(bPasteValues) Then bPasteValues = False ' If nEndRow is negative then -1 denote last row, -2 last but one etc. If nEndRow < 0 Then    oCursor = oSheet.CreateCursorByRange(oSheet.getCellByPosition(0, 0))    oCursor.GotoEndOfUsedArea(False)    nEndRow = oCursor.RangeAddress.endRow + 1 + nEndRow End If    oSheet.getCellByPosition(nColumn, 1).Formula = sFormula oRange = oSheet.getCellRangeByPosition(nColumn, nStartRow, _                                        nColumn, nEndRow) oRange.fillSeries(com.sun.star.sheet.FillDirection.TO_BOTTOM, _                  com.sun.star.sheet.FillMode.SIMPLE,0,0,0) ThisComponent.calculate() If bPasteValues Then    vData = oRange.DataArray   ' The intermediate variant is needed    oRange.DataArray = vData   ' Otherwise this is a No-Op !! End If PastFormulaToColumn = True Exit Function PasteHandler:    PastFormulaToColumn = False End Function

Hope that you fnd this is useful. Comments gratefully received and if you think that I am having a pop at Calc compared to Excel, then remember that Calc has some nice features like RegExp pattern matching, the ability to call Basic functions as worksheet functions and the fact its free //Terry
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
noranthon
Super User

Joined: 07 Jul 2005
Posts: 3318

Posted: Mon Jul 24, 2006 8:28 pm    Post subject:

Another member (Pitounet, I think it was) suggested the method .fillAuto The following is an example of a routine which inserts a formula into the top cell of a column range, copies it into the other cells of the range and, finally, converts the formulas to values:
 Code: 'Calculate move.  =IF((E2-E3)=0;"";MROUND(((E3-E2)/E2)*100;0.01)) oCell2 = oSheet2.getCellByPosition ( 6, 2 ) oTarget = oSheet2.getCellRangeByPosition( 6, 2, 6, lFoot ) sString = "=IF((E2-E3)=0;" & Chr\$( 34 ) & Chr\$( 34 ) & _    ";MROUND(((E3-E2)/E2)*100;0.01))" oCell2.setFormula( sString ) : oTarget.fillAuto( 0, 1 ) : oDoc.calculate() oTarget.setDataArray( oTarget.getDataArray )

Column E contains daily closing prices of stock trades, extracted from a .csv file. Column G is filled with a calculation of price movement from day to day.
_________________
search forum by month
TerryE
Super User

Joined: 16 Jul 2006
Posts: 550
Location: UK

Posted: Tue Jul 25, 2006 3:16 am    Post subject: ``

• fillAuto and fillSeries are alternative methods within the same service, with fillSeries just being specfic about what fill opoeration you want to do.
• Since I was giving a book example I used the correct constants, though I agree that hard coding then as (0,0,0,0,0) is briefer -- or in you case (1,0)
• As I said in my code the reason that I don't recommend oRange.DataArray = oRange.DataArray or the syntactically equivalent functional form that you oTarget.setDataArray( oTarget.getDataArray ) is that it doesn't work. At least in test on my OOo (V 2.0.3) it does the equivalent of a Paste and not a Paste Value, and this kind of defeats the purpose, eh?
• On a programming practice point of view I can't understand why people use & Chr(34 & when the language syntax gives an efficient alternative to use the repeated ". I would have written your formula:
 Code: . . . , "=IF((E2-E3)=0;"""";MROUND(((E3-E2)/E2)*100;0.01))" , . . .

My view is that like your example I would tend to go for brevity for an inline code fragment, but that for an out of line utility routine, I would always go for flexibility, clarity and run-time efficiency.

Since I had a comment about coding practice, I'll mention another pet hate of mine that I sometimes see in coding examples: coding constructs like
 Code: s= "Select a, b " s = s & "from someTable " s = s & "where c='user22'"
to enhance readability and avoid long constants that run over the editor window. I would alway use split line concatination (and lay it out so it is truly readible. For example
 Code: s= "SELECT a, b " & _    "FROM   someTable " & _    "WHERE  c='user22'"
as the second for is easier to read and since OOo Basic folds constants at complie time actually runs a lot faster.
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
noranthon
Super User

Joined: 07 Jul 2005
Posts: 3318

 Posted: Tue Jul 25, 2006 4:50 am    Post subject: I still have version 2.0.2 and Target.setDataArray(Source.getDataArray) enters the results of formulas, which appears to be according to the API. I must admit my use of Chr\$ was in imitation of those who preceded me along the path. If your usage is acceptable, it seems to be merely a matter of taste._________________ search forum by month
TerryE
Super User

Joined: 16 Jul 2006
Posts: 550
Location: UK

Posted: Tue Jul 25, 2006 6:13 am    Post subject:

Agreed largely about the taste issue, but there is a runtime speed hit as well because of this constant folding issue. The s = s + "next bit" or "bit" & var & "bit" format actualy runs about 10 times slower for three chunks -- not relevant set up code but maybe material in an inner loop.

As to the x.setDataArray(x.getDataArray) working in 2.0.2 thanks for confirming that. I looked at my test case again and realised that I'd missed setting the PasteValue flag = true for that case. Sorry. The simple test which demonstrates this "Feature by Design" as MS would say is
 Code: Sub Main rB2 = ThisComponent.Sheets(0).getCellByPosition(1, 1) rB2a = rB2 rB2b = ThisComponent.Sheets(0).getCellByPosition(1, 1) rB2.Formula = "=Row()" : rB2.DataArray = rB2.DataArray     : Print rB2.Formula rB2.Formula = "=Row()" : rB2a.DataArray = rB2.DataArray    : Print rB2.Formula rB2.Formula = "=Row()" : rB2b.DataArray = rB2.DataArray    : Print rB2.Formula rB2.Formula = "=Row()" : t=rB2.DataArray : rB2.DataArray=t : Print rB2.Formula rB2.Formula = "=Row()" : rB2.setDataArray(rB2.getDataArray): Print rB2.Formula

This prints ROW() for the first and second cases and 2 for the rest. I thought that it might have been the compiler optimising away a statement of the form x=x even if it's not a noop, but that wouldn't explain the second case. I think its a runtime issue. I'll post a bug report if it isn't already logged. //Terry

Good to have the feedback and debate. //Terry
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.
TerryE
Super User

Joined: 16 Jul 2006
Posts: 550
Location: UK

 Posted: Sun Aug 06, 2006 4:00 am    Post subject: My apologies to Noranthon about my snipe on using Chr\$(34) instead of "" inside a string to represent embedded double quotes. This is sencond nature to me because I come from the VBA world. I've only recently realised that this syntax has only been properly supported in OOo Basic since OOo version 2.0. It just didn't work in OOo 1. Given the very low key announcement of the improvements to Basic in OOo 2.0, I can well understand why experienced OOo Basic users haven't yet picked up this practice. For those interested, they can see a readable version of this announcement here ab02vba announcement_________________Terry WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development Also try the Official OOo Community Forum where I mainly post now.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum