| View previous topic :: View next topic |
| 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. |
|
| Back to top |
|
 |
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 |
|
| Back to top |
|
|
TerryE
Super User
Joined: 16 Jul 2006
Posts: 550
Location: UK
|
| Posted: Tue Jul 25, 2006 3:16 am Post subject: `` |
|
|
Thanks for your comments, and a couple in reply- 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. |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
|
|
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
|
Powered by phpBB © 2001, 2005 phpBB Group
|
