Description: When using a construction such as {=MyUDF(INDIRECT(ADDRESS(2,COLUMN())))} the function MyUDF will receive a matrix as its parameter. Perhaps ADDRESS is passing along an array of identical addresses based on COLUMN() returning an array in the array context, causing INDIRECT to continue with the array. When this construction is used in a single cell array formula this behavior is peculiar at best, as the user would expect the text return from ADDRESS to "wash" the array nature of the COLUMN() function. Instead, it 'mysteriously breaks' for the user since ADDRESS is prodded by COLUMN() to return an array of identical addresses and BASIC will throw a generic "Object variable not set" when MyUDF is not prepared for an array. Any MyUDF with signature like MyUDF(Value As String) As String will fail. Steps to Reproduce: 1. Create a UDF like MyUDF(Value As String) As String 2. Use a construct like {=MyUDF(INDIRECT(ADDRESS(2,COLUMN())))} as an array formula in a single cell Actual Results: BASIC throws an "Object variable not set" error. Expected Results: MyUDF would perform as normal, receiving a String (or whatever) and not an array. Reproducible: Always User Profile Reset: No Additional Info: Thanks to erAck/AskLO for much clarification and even wording to describe the concern. Any errors in interpretation are entirely mine. Excel 2021 performs according to "Expected Results" in an identical setup. When 'unboxing' the Value parameter Range object Excel will not report it to be an array. I.e. Function MyUDF(Value As Variant) As String Rem Notice Variant parameter to be able to test for array MsgBox IsArray(Value) MyUDF = Value End Function will display 'False' and Function MyUDF(Value As String) As String Rem Notice String parameter which causes error in LO Calc MyUDF = Value End Function will function as "Expected Results". 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
Created attachment 188562 [details] Example scenarios that demonstrate proper and questioned behavior For testability the example uses Named Expression, thus allowing manipulation across all scenarios entered on the sheet. The concern is equally reproducible as direct formula entry.
(In reply to Joshua Coppersmith from comment #0) > Thanks to erAck/AskLO for much clarification and even wording to describe > the concern. Any errors in interpretation are entirely mine. FWIW, that is https://ask.libreoffice.org/t/string-udf-arrayformula-oddity/93882 > Excel 2021 performs according to "Expected Results" in an identical setup. @Joshua, IDK whether Eike (erAck) would need it, but perhaps it would be helpful for other users (e.g. to verify and compare) if you could also attach to this report bug 156467 such "equivalent" xls(x) file. It should be created in MS Excel (not exported from LO or from some other tool).
I don't need it.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/49b601937f5ba7739198a1b16ba6da2351897750 Resolves: tdf#156467 Let array ROW() and COLUMN() return a scalar value It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/c/core/+/154952 for 7-6
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-6": https://git.libreoffice.org/core/commit/74d1aad00b1dad10534ecd80a845ae182fbd08c3 Resolves: tdf#156467 Let array ROW() and COLUMN() return a scalar value It will be available in 7.6.1. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.