Resize array formula

[Villeroy]

Array formulas do not resize automatically. When you select the current array for editing, append a space (dummy edit) and finish with Ctrl+Shift+Enter the array will fill out the selected area.

10 steps to resize a data array manually:
1) make sure that automatic calculation is enabled, otherwise the last step will fail.
2) select the current array. Otherwise nothing will be editable.
3) switch to edit mode (F2) and select the whole formula string (Ctrl+Shift+Home)
4) copy the formula string
5) hit escape to cancel edit mode
6) hit backspace to clear the whole array range
7) activate the former array's first cell or some other target cell
8) switch to edit mode (F2)
9) paste the formula string
10) Hit Ctrl+Shift+Enter

A set of Basic routines to overcome most of the difficulties:

Code: Select all

'calls getActiveCell, editArrayFormula
Sub resizeArrayOfActiveCell()
Dim oCell
oCell = getActiveCell(thisComponent.CurrentController)
'http://www.openoffice.org/issues/show_bug.cgi?id=61807
'print oCell.getArrayFormula 'due to a bug this formula is localized
' but it indicates if there is an array at all
If len(oCell.getArrayFormula) >0 then
	editArrayFormula(oCell)
endif
End Sub
'calls getCurrentArray, dispatch_InputMode
Sub editArrayFormula(oCell)
Dim oCtrl,oRange,oFrame,sViewData$,bAuto as Boolean,sFmlA$,sFmlB$,iLen%
	oRange = getCurrentArray(oCell)
	oCell = oRange.getCellByPosition(0,0)
	oCtrl = thisComponent.getCurrentController
	oFrame = oCtrl.Frame
	REM highlights single cell
	oCtrl.select(oCell)
	sViewData = oCtrl.getViewData
	REM removes highlightning
	oCtrl.restoreViewData(sViewData)
	bAuto = thisComponent.isAutomaticCalculationEnabled
	thisComponent.enableAutomaticCalculation(True)
	sFmlA = oCell.getFormula() '{=A1:B5}
	iLen = len(sFmlA)
	sFmlB = mid(sFmlA,2,iLen -2) '=A1:B5
	'print sFmlB
	oRange.clearContents(com.sun.star.sheet.CellFlags.FORMULA)
	oCell.setFormula(sFmlB)
	dispatch_InputMode
End Sub
Sub dispatch_InputMode()
Dim dispatcher,frame
	frame = thisComponent.getCurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	dispatcher.executeDispatch(frame, ".uno:SetInputMode", "", 0, Array())
end sub
Function getActiveCell(oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
	as1()  = Split(oView.ViewData, ";")
	lSheet = CLng(as1(1))
	sDum = as1(lSheet +3)
	as1() = Split(sDum, "/")
	on error goto errSlash
		lCol = CLng(as1(0))
		lRow = CLng(as1(1))
	on error goto 0
	getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
	if NOT(bErr) then
		bErr = True
		as1() = Split(sDum, "+")
		resume
	endif
End Function

USAGE:
1) click anywhere in the array.
2) call macro resizeArrayOfActiveCell. This will perform the above outlined steps 1 to 8.
3) edit the array formula. Append a space (dummy edit) if you just want to adjust the recalculated size without changing the formula string.
4) hit Ctrl+Shift+Enter

This simple Basic code enables automatic calculation. After execution it has no chance to reset this setting to manual. This would require a more sophisticated macro program I am not willing to write.

[B Marcelly]

A simpler manual way. Of course Automatic calculation is active.

Create the array formula

• Define a name for each cell zone that will be referred by your formula
• Select the first cell of the zone for your formula.
• type the formula, Ctrl+Shift+Enter. The formula extends to the whole zone.

Increase the size of the array formula

• Modify each named zone to the new size.
• Select the current zone of your formula.
• Increase the zone with the mouse by dragging the black square at bottom right.

[Villeroy]

Thank you. When I wrote that code I was not aware that dragging resizes the array. If the size depends on calculations you need another formula to indicate how far you've got to drag.
It's irritating that Edit>Fill>Down with a single cell on top copies the cell so you get many single-cell arrays.