LO Calc restricts the size of an array function output based on the initial results

[agerber85]

I’m trying to utilize the MyUnique custom array function provided here The code of the function is

REM  *****  BASIC  *****

Option Explicit


Function MyUnique(InArray as Variant) as variant

 Dim Row_Nr, lCounter, i as long
 Dim DataType as integer
 Dim Actual_Value, Out_Value as Variant
 Dim OutArray() as Variant

 	For each Actual_Value in InArray
		lCounter = 0
		For each Out_Value in OutArray
			If Out_Value = Actual_value then lCounter = lCounter + 1
			If lCounter > 1 then exit For
		next Out_Value 
		if lCounter = 0 then
			Row_Nr = UBound(OutArray()) + 1
			ReDim Preserve OutArray(Row_Nr)
			OutArray(Row_Nr) = Actual_value
		end if				
	next Actual_Value
	
 Dim ResultArray(Row_Nr,0)
	For i = 0 to Row_Nr	
		ResultArray(i,0) = OutArray(i)
	next i
	
MyUnique = ResultArray()
End function

I’ve hit a snag in using it. If, for example, I enter{=MyUnique(A1:A10)} in B1:B10 and it only finds 5 unique values, the array formula only gets applied to B1:B5, which ends up truncating the list of results when a non-unique value in A1:A10 is changed to a new unique value. Is this something that can be overcome by tweaking the custom formula, or is the problem inherent to LO Calc?

[agerber85]

Accidentally found my own solution. If I have autocalculate disabled (Data>Calculate>Autocalculate) when I enter the array function, it applies the array formula to all selected cells. The unneeded cells all show #N/A.

[Lupp]

1. Does this actually depend on “autocalculate disabled”?
   I wouldn’t think so.
2. A user function with array output to a sheet calcuiates the needed size on its first run per formula, and locks the respective range which later can’t be changed easily.
   If the function knows a “maximum range to lock”, you simply need to Dim the output array repectively with As Variant for the elements. You may also first work with a probably smaller variant array, and resize it appropriately with Redim Preserve before assigning it to the functions name.

See attached example.
rawTest.ods (20.1 KB)

[agerber85]

Yes, it does depend on autocalculate disabled when it’s set up. I just tested it, and if I re-enable autocalculate after setting it up, it leaves the output size where it was set up during subsequent recalculations, leaving #N/A in all unneeded cells. IMO, the behavior I get when I set the formula up with autocalculate disabled should be the default behavior even without having to disable it. Then again, I’m dealing with a user-defined function that I didn’t create nor have I scrutinized, and this might be a rather simplistic example of handling array formulas with dynamic size outputs.

[Lupp]

@agerber85

It’s LO’s inherent behavior with array functions that’s causing this hiccup in my designs.

Do you know of or have heard about any spreadsheet software handling this (basically) in a different way?
I can hardly imagine one.
Recalculating the output range with every calculation would come with many inacceptable (imo) implications.
Locking of an output range must be done at design-time to avoid

• overwritiung of data
• reducing that range on the fly - and later have to regret it
• quarrels (“wars”) between formulas about the right to write to intersecting parts of their currently calculated output ranges.

A special tool like “Try to redimension locked output ranges and report conflicts before action” might look helpful at a first glance. I would suspect it to be too sophisticated.
The variant I exemplified may have a few advantages in special cases - but surely even more disadvantages in others (empty in place of N/A e.g.). Anyway it would also lock the output range after the first calculation.

@agerber85

Ah, well! I guess I can resort to pivot tables, which I probably would have had to do, anyways, to achieve the calculations I wanted to do.

If this is a realistic option, it should surely be your choice. For small pivot tables you can then even order a kind of AutoCalc based on a few lines of user code without endangering efficiency too much. Also the problem of “stubborn formatting” by the tool is manageable.

My above mentioned objections against “redimensioning on the fly” are ignored by the pivot table tool. To avoid inacceptable conequences PT are written each to an extra sheet by default.