Ask Your Question

Get range containing all cells in array formula from basic macro

asked 2018-10-25 11:14:36 +0200

ecoten gravatar image

updated 2018-10-25 11:17:41 +0200

I am trying to write a macro that resizes an array formula or in other words a formula that I can run when the number of items returned by the array formula changes, to avoid the ulgy "#N/A" cells at the end of the array.

I have found one example but it doesn't seem to work as it requires a getCurrentArray function which doesn't seem to be defined, however the basic premise seems sound (i.e. delete the existing formula and re-insert a new one).

I have tried to just call the setArrayFormula method on a single cell in the array, but unsurprisingly that doesn't work (annoyingly, it just silently does nothing) because you need to be working with the whole array, similar to the way you can press CTRL+/ when doing it normally. If I expand the range to include the entire row (or a range which I know is larger than the array formula), then it all works as expected.

I tried recording a macro and pressing CTRL+/, and the result was:

dispatcher = createUnoService("")
dispatcher.executeDispatch(document, ".uno:SelectArrayFormula", "", 0, Array())

This sort of works, but:

  • it's a bit of a cludge
  • you have to have the spreadsheet window focussed (not the basic window, annoying when developing)
  • doesn't quite do what I want, it does select the correct cells but doesn't return them as a range, so then I'd have to go another step to find the current selection and get the range from that

So my question is if I have one cell which is part of an array formula how can I find the size/range for _all_ the cells that are part of the array formula to enable me to edit/delete it.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-10-25 13:57:03 +0200

Lupp gravatar image

updated 2018-10-25 13:58:19 +0200

Thanks. I didn't know that the slot machine has that slot. Highly appreciated.

However, as next to always you find a hint in the famous texts by Andrew Pitonyak.
There is a not too well known powerful service of interest here. It provides a method e.g.

Example code:

Function arrayFormulaRange(Optional pCell As Object)  REM 'Optional' only for the test.
doc0   = ThisComponent
If IsMissing(pCell) Then                              REM For the test.
  pCell  = doc0.Sheets(0).GetCellRangeByName("D3:F3")
End If
s      = pCell.Spreadsheet
sCC    = s.CreateCursorByRange(pCell)
REM I feel the "collapse" to be heavily misleading here.

arrayFormulaRange = sCC
REM I lack the experience to be able to make asuuranvces about the used methods.
REM As far as I can tell, ONLY  THE FIRST CELL of the range passed to the cursor is evaluated. 
End Function
edit flag offensive delete link more


Thanks, this is perfect, just what I was looking for. Unfortunately for me it doesn't quite do what I was hoping but it does answer the question. My problem is that the size of the array from the array formula has changed, so I was trying to expand or contract the array formula to match the size of the resulting array. But perhaps I'll make that a separate question I get any more time to play with this.

ecoten gravatar imageecoten ( 2018-10-25 15:59:31 +0200 )edit

The problem with changing size of the needed output range is one of the shortcomings of array-formulae in many cases, and probably a reason to try to avoid them.
In a few cases where I needed user code for specific functions anyway (main example: my XTEXTSPLIT() function) I introduced an optional parameter for a minimum output size, and left unused space empty.
Unfortunately this cannot easily be ported to standard functions.

Lupp gravatar imageLupp ( 2018-10-27 21:37:12 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-10-25 11:14:36 +0200

Seen: 116 times

Last updated: Oct 25 '18