Fill in OO Calc

On a spreadsheet I needed to enter a formula in one cell and then fill down with that formula over a range. My first thought was that, as in VBA, I would record a macro and get the commands. I sometimes do that when I just can’t remember and am too lazy to look everything up.

Calc, however, provided me with an option that was completely unlike what I would expect to write, using dispatch and uno commands. Basic makes that easier. Here’s the function:

Sub FillFormulas
	Dim Checkbook, CheckSheet, CR, Cell as Object
	
	Checkbook = StarDesktop.CurrentComponent
	CheckSheet = Checkbook.Sheets(0)
	CR = CheckSheet.getCellRangeByName("G3:G10")
	Cell = CR.getCellByPosition(0,0)
	Cell.Formula = "=IF(E3<>0;G2+E3-F3;IF(F3<>0;G2+E3-F3;0))"
	CR.fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM,1)
	
End Sub

Now this obviously won’t do anything particular for you, but it does show the simplest version. The documentation on this command is here and it tells you everything you need to know. You can replace TO_BOTTOM with TO_TOP, TO_LEFT, or TO_RIGHT as necessary, and you can do much more complex fills using the fillSeries command.

Don’t ask me to explain the formula I was using. I just left it there to have something in place with which to fill the cells.