String, UDF, ArrayFormula Oddity

When I reference a cell using an INDIRECT reference and pass that to a UDF that is entered as an array formula I get the BASIC error “BASIC runtime error. Object variable not set.” if the UDF has a parameter requiring a String. If the parameter is a Variant and BASIC is never asked to convert it to a String, then the UDF will not throw the error.

Please see the attached document.
UDFandArrayFormulaOddity.ods (13.3 KB)

EDIT: After testing with XRay on a hunch, I see that the input to the UDF in the array formula context that is not being accepted as a String is in fact an array. My hunch was based on arrays getting passed to UDF’s when they are given full-column/row references. [MyUDF(C:C) gets a column array even if BuiltIn(C:C) acts like it just gets C:C intersecting with its own row.] So I suspect that INDIRECT is passing a range when it is in an array formula context. Keeping only a Variant, the VV UDF keeps the chain alive right on through to assigning an array result back to the containing range, which is here just one cell.
If this is true, I’m not sure this falls under “expected behavior” for INDIRECT to upscale a single address reference for input because it is in an array formula context. It doesn’t “go ahead” and pass an array the size of the array formula region with the requested cell at the top left, for example.
So, in the attached sheet SS and SV cannot be saved, but VS can be saved as something like:

Function VS(Query As Variant) As String
	Dim Result As String
	If IsArray(Query) Then
		Rem Assume Query is a BASIC-style 1-by-1 2D array
		Result = CStr(Query(LBound(Query),LBound(Query))
	Else
		Result = Query
	EndIf
	VS = Result
End Function

You might think you could just reassign Query to a String, but that doesn’t seem to work:

Function VS(Query As Variant) As String
	Rem Does not work
	If IsArray(Query) Then
		Rem Assume Query is a BASIC-style 1-by-1 2D array
		Query = CStr(Query(LBound(Query),LBound(Query))
	EndIf
	VS = Query
End Function

Notes:
First, this is not “academic.” I’m led to this because of very real issues in a spreadsheet I use frequently for work.
Second, since I know the causing circumstances I may be able to rework the UDFs, but I do not know that is the case. It is, nevertheless, a stumbling block to be sure.

[Calc] Passing array values to/from a Basic function

Thanks. In my case I’m not needing to convert array elements, but rather the problem is that I am getting an array input to the UDF at all. This happens only when 1. the UDF is called from an array formula and 2. the parameter coming into the UDF as an array has been referenced via an INDIRECT() function, even though the reference is to a single cell.

So for signature MyUDF(TheText As String) As String, MyUDF($B$5) sees a single non-array value coming in as TheText. MyUDF(INDIRECT(ADDRESS(5,2)) sees the same single non-array value. But {MyUDF(INDIRECT(ADDRESS(5,2))} (actually, via Named Expressions) as an array formula throws an error because TheText is coming in as an array. Knowing this, yes, we can unbox the array element, but that means testing every time in any UDF that may ever be used under conditions 1. and 2. above.

Does this fall under expected behavior? It seems like it should not, since INDIRECT(ADDRESS(5,2)) is being used to request a single cell. This behavior would be expected when the parameter reference is INDIRECT(ADDRESS(5,2)&":"&ADDRESS(5,2)).

I don’t see that behaviour. The argument is passed as a single scalar value. However, if
Option VBASupport 1
was defined for the macro then a cell range object is passed. Nevertheless,

Option VBASupport 1
Function MyUDF(Query As String) As String
	MyUDF = Query
End Function

works just fine.

Nice to know about the VBASupport. I didn’t realize we could even get access to the cell range object. Do we have an Application.ThisCell equivalent, too?

But I have to ask, are you saying that the example spreadsheet does not throw errors on your installation? I’m using:

Version: 7.5.3.2 (X86_64) / LibreOffice Community
Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3
CPU threads: 20; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

I didn’t try the document, I literally used your comment’s example {=MyUDF(INDIRECT(ADDRESS(5,2))} because you focused on that single reference and for that not an array is passed but a scalar value.

The actual problem in your sample document is the use of COLUMN() in the ThisValueIndirect named expression, because in array mode COLUMN() always produces an array/matrix (which when entering a matrix formula in a several columns cell range is needed) that then further forces the results of ADDRESS() and thus INDIRECT() and then CONCATENATE() into a matrix.

Please submit a bug so that we can evaluate whether for COLUMN() and ROW() in array mode the special case of a single element matrix could be changed to return a scalar value instead, and report back here the bug number in the form tdf#123456. Thanks.

1 Like

Bug report with example and comparison with MS Excel 2021.

I listed this as bug and not feature request–hope that was the correct call.

https://bugs.documentfoundation.org/show_bug.cgi?id=156467

1 Like