Resize array formula

Creating Extension - Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This forum is not for asking questions about writing your own macros.

Resize array formula

Postby Villeroy » 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:
Code: Select all   Expand viewCollapse view
'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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24652
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Resize array formula

Postby B Marcelly » Sun Feb 09, 2014 9:59 am

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.
Bernard

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
B Marcelly
Volunteer
 
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Resize array formula

Postby Villeroy » Sun Feb 09, 2014 12:41 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24652
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 5 guests