Hello, Wolfgang!
Yes, indeed, the problem is that the output array size for an array formula is calculated when the formula is entered and doesn’t change.
See bug tdf#127808 and @Regina 's comment 2 there.
Until this bug is fixed, a workaround can be suggested: use a macro to recalculate the array formulas, determining the new size.
An example of a macro that re-enters an array formula is below.
Create a new document and run macro TestSetArrayFormula several times.
As a next step, it might be possible to write a macro that finds all array formulas in a document and recalculates them using the macro below.
' lang:en
' Assigns an array formula to a cell (as with interactive input).
' The specified formula is entered into the cells of the range, the size of which corresponds to
' the size of the array returned by the formula.
' Parameters:
' oRange cell that specifies the upper left corner of the target range.
' arrayFormula array formula (without {...}.
'
' The function returns the resulting range of cells or Nothing.
Function SetArrayFormula(ByVal oRange As Object, ByVal arrayFormula As String) As Object
Dim oDoc as Object, oController As Object, oCur as Object, oDisp as Object
Dim props(0) as new com.sun.star.beans.PropertyValue
Dim selection_old, arr, oCell As Object, localFormula As String
oDoc=oRange.Spreadsheet.DrawPage.Forms.Parent
oController=oDoc.CurrentController
selection_old = oController.Selection
oCell=oRange.getCellByPosition(0,0)
If oCell.getArrayFormula()<>"" Then
oCur=oCell.SpreadSheet.CreateCursorByRange(oCell)
oCur.CollapseToCurrentArray()
oCur.setArrayFormula ""
End If
' get local formula
oCell.setFormula IIf(Left(arrayFormula, 1)="=", "", "=") & arrayformula
localFormula=oCell.FormulaLocal
oDisp=createUnoService("com.sun.star.frame.DispatchHelper")
props(0).Name = "ToPoint"
arr=Split(oCell.AbsoluteName, ".")
props(0).Value = arr(Ubound(arr))
oDisp.executeDispatch oController.Frame, ".uno:GoToCell", "", 0, props
props(0).Name = "Formula"
props(0).Value = localFormula
oDisp.executeDispatch oController.Frame, ".uno:InsertMatrix", "", 0, props
oCur=oCell.SpreadSheet.CreateCursorByRange(oCell)
oCur.CollapseToCurrentArray()
SetArrayFormula=oCur
On Error Resume Next
oController.Select selection_old
End Function
Sub TestSetArrayFormula()
Dim oCell As Object, oRange As Object, s As String, s2 As String, formula As String
oCell = ThisComponent.Sheets(0).GetCellByPosition(0, 0)
s = "Hello, World!!"
s2 = IIf(Instr(1, oCell.Formula, "!!") = 0, s, Left(s, Len(s) - 1))
formula="Mid(""" & s2 & """; Row(A1:A" & Len(s2) & ");1)"
oRange=SetArrayFormula(oCell, formula)
Msgbox "Array Formula Range: " & oRange.AbsoluteName
End Sub
