Limitation on BASIC function names

[phumblet]

Version: 24.2.1.2 (X86_64) / LibreOffice Community
Build ID: db4def46b0453cc22e2d0305797cf981b68ef5ac
CPU threads: 8; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded

I wrote a BASIC function named TAX24. It compiled fine. When I use it in a cell =TAX24(C9)
Calc suggests replacing it with =TAX24*(C9)

What is happening is that “TAX24” is interpreted as a cell ID (like C9), although it is a function.
Is this a known problem? Is there a workaround (besides using names coming after XFD)?
It is troublesome because I am importing functions from another calculator that does not have this issue.
Thank you

[Zizi64]

Please upload an ODF type spreadsheet file here with the embedded macro code.

[karolus]

just give your “UDFs” names that are not valid cell addresses… Problem solved!

[phumblet]

Looking back I should have kept the file as .xls instead of saving it as .ods
And I like sokol92’s idea of using underscore in UDF function names to future proof them. That should be added in the documentation somewhere so that newcomers can see it before the problem hits them. I will do that from now on.

[mikekaganski]

@phumblet

I should have kept the file as .xls instead of saving it as .ods

That wouldn’t help at all, unless you also use MS Excel instead of Calc.

In MS Excel, when it opens XLS, it works with only 256 columns. It is natural, because XLS is their native format, and they decided to keep it in its legacy 256-column state (and they used the new XLSX to expand column count). So they accept names that would otherwise clash with 16K columns.

In Calc, when you open XLS, it imports into Calc’s document model, which is now unconditionally 16K-column. It was decided that Calc keeps using ODS with 16K columns; that has an advantage that these 16K-column files are openable in any earlier ODS file reader (at least its first columns). But at the same time, it makes all clashing names immediately conflicting.

Every decision has its pros and contras. For a commercial company like MS, making an incompatible new file format is an advantage, because it incentivizes customers’ transition to the newer version, thus boosting sales. For TDF, the danger of clashing names was considered smaller than benefit of interoperability with other ODF applications. Everything is a compromise. But better diagnostics is something that needs attention, of course.

[JohnSUN]

@phumblet

And I like sokol92’s idea of using underscore in UDF function names to future proof them.

Before you change the names of all your problematic functions, you may want to use a workaround. Since the problem arises precisely because the function name is similar to the column name, simply rename the columns while creating formulas. This can be done by switching Formula syntax to Excel R1C1 value

[phumblet]

Function tax24(x As Variant) As Variant
tax24 = 0.1 * x
End Function

[phumblet]

Thanks for the suggestion to use R1C1 notation.
At this point I have already added an underscore to all the troublesome function names in 60 sheets or so. It wasn’t too bad because I had only 2 prefixes followed by 4 possible digits, and Find and Replace can handle all sheets at once.

[phumblet]

Thanks for the feedback.
The only solution appears to be to lengthen the name.
I was using an old version with at most 256 columns, so I was not affected.
Next time the number of columns is doubled, 4-letter prefixes will be affected and improving the parsing might become very useful. I wonder why it wasn’t done earlier, recognizing the parenthesis (if only to improve the error message) does not look complicated.

[sokol92]

I don’t think there should be any more discrepancies in behavior between Calc and Excel.
One simple solution is to use underscores in your UDF function names. This will eliminate the risks associated with potentially increasing the number of columns and reduce the risks associated with name conflicts with future Calc built-in functions.

[mikekaganski]

It is possible to implement a rule, that for a function call syntax (a name followed by some list in parentheses), Calc engine would check user-defined functions, instead of checking if the name is a cell reference.
Note that we already do something similar for range names - because the expansion of column count already led to clashes with named ranges in existing documents. So possibly that would not be completely unreasonable.

But why introduce another complexity? E.g., MS Excel doesn’t allow that. What is that “another calculator that does not have this issue”?

[JohnSUN]

Perhaps @phumblet talking about OpenOffice 4.1.12 with its AMJ columns? Yes, TAX24 will not cause problems there.

[sokol92]

@mikekaganski

E.g., MS Excel doesn’t allow that.

Allows TAX24 for formats .xls, .xlt, …

[schiavinatto]

@JohnSUN

Perhaps @phumblet talking about OpenOffice 4.1.12 with its AMJ columns? Yes, TAX24 will not cause problems there.

When the spreadsheet is expanded to 16384 columns and 16 million rows, there will be a problem.