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.