-1

I understand Excel has a TEXTJOINfunction which allows one to display multiple values as a tuple.

I also understand Libre Office does - for whatever reason - not have them.

How do I write an auxiliary macro vec that produces the desired tuple representation for me?

E.g. =vec(A1) should produce ="("&A1&")",

=vec(A1:A3) should produce ="("&A1&","&A2&","&A3&")",

=vec(A1,X5:X99,Z3) should result in ="("&A1&","&"X5"&","&X6&...&x99&","&Z3&")"

etc, etc.

Easy enough a macro to implement in, say, bash, but I would like to just define it once then use it in calc, not constantly copy from console to spreadsheet.

How do I implement this in calc?

User1291
  • 7,664
  • 8
  • 51
  • 108
  • 1
    Possible duplicate of [Open Office Spreadsheet (Calc) - Concatenate text cells with delimiters](http://stackoverflow.com/questions/1825886/open-office-spreadsheet-calc-concatenate-text-cells-with-delimiters) – tohuwawohu Dec 13 '16 at 16:15
  • 1
    @tohuwawohu Nice, thank you. Will definitely take a closer look. However, this question differs from the suggested solution inasfar as I want to get rid of the requirement that the values need to be in a connected range – User1291 Dec 13 '16 at 16:43
  • Ooops - you're right, insofar your question in fact isn't a duplicate. – tohuwawohu Dec 13 '16 at 16:46

1 Answers1

0

According to https://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=67880, it is possible for a Basic function to use a variable number of arguments if it is declared with Option Compatible. This makes it behave more like MS Excel. The argument is declared as ParamArray pa().

The link that @tohuwawohu posted shows most of the implementation details needed.

To do it in a way that is more native to LibreOffice, write a Spreadsheet Add-In with a Java declaration that uses any[] as an argument. For information about add-in argument types, see https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/AddIn.html.

The actual function can also be implemented in Java. Or, it can probably be implemented in another language that accepts a variable number of arguments, such as Python *args.

Jim K
  • 12,824
  • 2
  • 22
  • 51