Resize array formula
Posted: Sun Feb 09, 2014 12:26 am
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:
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.
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
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.