Prevent 1-by-n VBA return from filling an array formula range

[joshua4]

It seems like if you return an n-by-n array from a User Defined Function used within an array formula, Calc nicely places each element of the returned array into a different cell as expected.

If there are more return elements than cells in the array formula range, then results are truncated as expected.

If there are fewer return elements than cells in the array formula range, then #N/A is entered into each cell of the array formula range that does not have a matching element in the returned array, as expected (although not pretty).

However, it seems like if a 1-by-n array is returned from the UDF, then that row of elements is repeated throughout the height of the array formula range, just as if a simple scalar had been returned. Other than to pad, say, an empty row under the single-row, is there a nice way to prevent this behavior, so that a single-element result just occupies the first row of the array formula range, rather than getting repeated throughout the entire range?

Example:

Function MyFunc(Value As String)
 Dim Result(1 To 1, 1 To 1)
 Result(1,1) = Value
 MyFunc = Result
End Function

If we enter MyFunc(“Hello”) as an array formula in A1 through A4 we get the result

Hello
Hello
Hello
Hello

However, if we had

Function MyFunc(Value As String)
 Dim Result(1 To 2, 1 To 1)
 Result(1,1) = Value
 Result(2,1) = "--End--"
 MyFunc = Result
End Function

we just get

Hello
–End–
<Empty>
<Empty>

I’m looking for the follow result with the first version of MyFunc:

Hello
<Empty>
<Empty>
<Empty>

[Lupp]

What you get is the very similar to the effect you get if you select an output range in advance, then enter edit-mode (F2), type =1 and hit Ctrl+Shift+Enter.
The array-formula now shown as {=1} is filling the complete preselected range, whether 1D or 2D with lots of 1es.

Did you study the defining documents to decide whether this is a bug (against specification) or as specified or left to the implementation - explicitly or tacitly.
You should have some fun with the documentation.

Anyway I am sure the issue isn’t specific to UDF, but more general. Testing with MUNIT() and MMULT() showed clear evidence.

For playing also:

Function testRowArray(p)
Dim res1(1 To 2, 1 To 3)
res1(1, 1) = 1 : res1(1, 2) = 12 : res1(1, 3) = 13
res1(2, 1) = 1 : res1(2, 2) = 22 : res1(2, 3) = 23
res2 = Array(51, 52, 53)
res = Array(res1, res2)
testRowArray = res(p-1)
End Function

To call for differently created output ranges with a reference to a cell where you enter 1 or 2 at will.

See
disask85577arrayOutptCaseOneDimensionCollapsed.ods (12.0 KB)

BTW: Please dont use “VBA” if you refer to LibreOffice Basic.

[joshua4]

Thank you for the ideas. Yes, the problem is consistent with selecting a range then entering ={1} as an array formula vs. entering ={1;2} as an array formula.

I didn’t get far looking for the defining docs. If you have a link to start out with, let me know. I didn’t see an obvious path via the developer’s guide for docs on the interoperation with the spreadsheet UI.

I don’t really see it as a bug either way, though. It seems like by choice 1x1 arrays collapse to scalars, and then the array formula behavior is as expected with a scalar. Annoying, perhaps, but improving it would be a change request, not a bug report.

If no one posts any quick tricks, I’ll just continue to add a blank row to single-row results by passing results going out to the spreadsheet through something like:

Function ToArrayFormula(Value As Variant) As Variant
	Dim Result As Variant
	Dim Index As Long
	Dim DimTest As Long
	
	If IsArray(Value) Then
		DimTest = -1
		On Error Resume Next
			DimTest = UBound(Value,2)
		On Error Goto 0
		If DimTest = -1 Then
			Rem 1D array
			If UBound(Value,1) - LBound(Value,1) = 0 Then
				Rem Will flood array formula range, so add a row
				ReDim Result(LBound(Value,1) To UBound(Vlaue,1) + 1)
				Result(LBound(Result)) = Value(LBound(Value))
			Else
				Rem Multi-row, so will behave
				Result = Value
			EndIf	
		Else
			Rem 2D array (assumed)
			If UBound(Value,1) - LBound(Value,1) = 0 Then
				Rem Will flood array formula range, so add a row
				ReDim Result(LBound(Value,1) To UBound(Value,1) + 1, LBound(Value,2) To UBound(Value,2))
				For Index = LBound(Value,2) To UBound(Value,2)
					Result(LBound(Result,1), Index) = Value(LBound(Value,1), Index)
				Next Index
			ElseIf UBound(Value,2) - LBound(Value,2) = 0 Then
				Rem Wil flood array formula range across, so add a column
				ReDim Result(LBound(Value,1) To UBound(Value,1), LBound(Value,2) To UBound(Value,2) + 1)
				For Index = LBound(Value,1) To UBound(Value,1)
					Result(Index, LBound(Result,2)) = Value(Index, LBound(Value,2))
				Next Index
			Else
				Result = Value
			EndIf
		EndIf
	Else
		Result = Value
	EndIf
	
	ToArrayFormula = Result
		
End Function

[erAck]

Repeating a vector formula result into a dimension it’s not defined for if such cell range was marked/selected is expected and a feature also found in Excel.

[Lupp]

@erAck

Repeating a vector formula result into a dimension it’s not defined for if such cell range was marked/selected is expected…

May be. But this wasn’t the actual topic of the question. That was - in different words - If arrays where one or both dimensions are collapsed to a single allowed index are accepted, and, if not, for what reasons this isn’t shown by an err:502 or many #N/A (expected by me) or in a similar way.
That he result for a single row/column/cell is calculated as expected, but then is delivered to the complete previously occupied range is not expected (by me nor by any supposed “mathematician”) whether Excel does it this way or not. All the cases where the blocked output range not is fully needed, but the respective dimension not collapsed to 1 indexvalue is signaled by the expected #N/A .