# Calc macro functions which accept cross-sheet range and variable length arguments

Two questions, can user-defined functions in libreoffice calc accept cross-sheet range as arguments?
and how to define function accept variable length arguments like `CHOOSE(i, n1, n2, ...)`?

cross-sheet range function

I define a single argument function:

``````function CHOOSERANGE(n as long, range)
dim r
r = "#ERR:502"
dim i
i = 1
for each x in range
if i = n then
r = x
exit for
end if
i = i+1
next

' ReDim range(0 To size-1)
CHOOSERANGE = r
end function
``````

Then I call it as `=CHOOSERANGE(1, sheet1.A1:sheet2.B2)`,
but it return a 504 error code (Function parameter is not valid) .

Some function like SUM can accept cross-sheet range,
can we define these kind of functions with macro?

Variable length arguments function

I am very new to BASIC, maybe I used the wrong term.

1 Like

What you introduce as â€ścross-sheet-rangeâ€ť supposedly is what the specifications call a CUBOID.
There are very few standard functions accepting cuboid parameters, and I donâ€™t know any way (neither by native Basic nor using VBA options) to pass a cuboid or the respective structure of values to a UserDefinedFunction. You would need to use the concept @KamilLanda sketched.
Concerning the â€śvariable-length-argumentsâ€ť @JohnSUN already told you about the ParamArray construct only available under `Option VBAsupport 1` (or similar).

My main suggestion:
If it is about writing user code in Basic for LibreOffice (in this case for `Calc`), begin with the beginning. Thereâ€™s a lot to learn before you actually can understand the implications of convoluted tasks like those you are asking for. This includes to study the basics about the LibreOffice API and -related to `Calc`- some glances on the specifying paper concerning formulas. This would also help you to learn proper terminology.

For many years now I also recommended (mainly) the famous texts by Andrew Pitonyak:
â€śThe Bookâ€ť `OpenOffice.org Macros Explained`
The collection of hints and examples: Useful Macro Information (aka â€śEnglish Macro Documentâ€ť) .
Unfortunately Andrewâ€™s site doesnâ€™t respond currently. Does somebody know a reliable mirror? Please post it.(Online again.)

1 Like

Right, 3D cuboids are not implemented for the BASIC-API. That maybe could be enhanced to pass a 3D Array and with VBAsupport an `uno::Sequence<css::table::XCellRange>`, but thatâ€™s just an unverified thought.

I know the usage of cuboid from the TIPS dialog while launching libreoffice,
and search itâ€™s usage multiple times, but this is the first time that I see its official name.
The Tradition Chinese translation does not show the term cuboid to user.

I have had a odt copy of the OpenOffice.org Macros Explained v4.0. I look up it everytime when I write libreoffice basic. It does not mention cuboid and ParamArray. I read it and found nothing so I asked here. I think I just use the wrong keywords while search on forum.

You wonâ€™t be astonished to read that I donâ€™t know a single thing about Traditional Chinese. As a German living in Germany I even get often beyond my abilties concerning English.

The famous texts by Andrew Pitonyak donâ€™t mention `Cuboid`(s) because they donâ€™t occur in `OpenOffice Basic` and never are passed to `UserDefinedFunction`(s).

These texts also donâ€™t mention the `ParamArray` construct because it isnâ€™t native to `OpenOffice Basic`, but only available under specific options introduced at different times and without an exactly defined range of actually implemented compatibilty with Excel VBA. Using these options may in addition even change the behaviour of command lines (statements) of native Basic: Use the options carefully therefore.

Concerning the â€śofficial terminologyâ€ť you would always need to refer to the OASIS documents which, on the other hand, donâ€™t contain anything about Basic. The word â€śbasicâ€ť there only is used with its everday meaning.

An example how to (functionally) pass a cuboid to an UDF you find here:
VBAbasedCuboidHandling.ods (29.4 KB)
The contained comments concerning number conversion are for connoisseurs.

1 Like

@Lupp Funny approach
Too bad it wonâ€™t work without `Option VBAsupport 1`, but what can we do!

In fact I think LibreOffice `Basic` could implement reference passing to UDF without breaking (much of?) running code. That depends on whether there are still capable developers who are interested in improving Basic and are not otherwise overloaded. Thereâ€™s a lot to do, and the enemy isnâ€™t sleeping. MS or Google can easily hire a hundred engineers to create new incompatibilies, and this way absorbing/neutralising the capabilities of free competitors.

1 Like

Itâ€™s the term used in the ODF standard formula specification, not an official name in the LibreOffice documentation (maybe we should use it?). The colloquial term is more 3D reference (though I doubt youâ€™d find that either). But effectively itâ€™s a cuboid because any cell range reference (in ODF and Calc A1 notation) specifies `fronttopleft:rearbottomright` (e.g. `Sheet1.B3:Sheet2.C5`) and omitting sheets itâ€™s still `topleft:bottomright`.

If there is error on cross-sheet range: sheet1.A1:sheet2.B2, then use string as parameter `=CHOOSERANGE(1, "sheet1.A1:sheet2.B2")` and then â€śseparateâ€ť the ranges in macro from string.

1 Like

@KamilLanda Letâ€™s take a closer look at this option. Okay, weâ€™ll get a string â€ś\$Sheet1.\$A\$1:\$Sheet2.\$B\$2â€ť. Using this string, using ThisComponent.getSheets().getCellRangesByName(sRange) we get an array of one element com.sun.star.table.XCellRange with .AbsoluteName = â€ś\$Sheet1.\$A\$1:\$Sheet2.\$B\$2â€ťâ€¦
Yes, this came as a surprise to me too, I was expecting two ranges, one for each of the sheets mentioned in the parameter.
How to proceed further, how to iterate over each cell of this?
By the way, .getRangeAddress() for this object only knows the first of the sheets:

@JohnSUN my idea was there have to be â€śpairsâ€ť of variables like:

``````oSheet1=oDoc.Sheets.getByName(s1)
oSheet2=oDoc.Sheets.getByName(s2)
oRange1=oSheet1.getCellRangeByName(ss1)
oRange2=oSheet2.getCellRangeByName(ss2)
``````

and s1, ss1 â€¦ are the parts separated from string address.

If we still have to split the parameter received by the function, then it seems to me easier to use the trick that @gholk hints at in the second part of his question and @Lupp in his comment. Something like that

``````Option VbaSupport 1

Function ChooseNCellInRanges(n As Long, ParamArray ranges) As String
Dim i As Long
Dim nextRange As range
Dim oCell As range
i = 1
For Each nextRange In ranges
For Each oCell In nextRange
If i = n Then
ChooseNCellInRanges = oCell.CellRange.AbsoluteName
Exit Function
EndIf
i = i+1
Next oCell
Next nextRange
ChooseNCellInRanges =  "#ERR:502"
End Sub
``````

and call it like

``````=CHOOSENCELLINRANGES(ROW();Sheet1.\$A\$1:\$B\$2;Sheet2.\$A\$14:\$C\$22;Sheet3.\$B\$10:\$C\$12)
``````

It seem that BASIC macro function only work for cell, strip and rectangle ranges.
The ranges with reference operators do not work.

reference operators:
https://books.libreoffice.org/en/CG71/CG7107-FormulasAndFunctions.html#toc16