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

[gholk]

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 searched and get nothing about this topic.
I am very new to BASIC, maybe I used the wrong term.

[JohnSUN]

@gholk

Variable length arguments function

Perhaps you wanted to ask about “variable number of parameters”?

How to write a function with arbitrary number of arguments?

Using ParamArray compatibility option (requires option compatible) with a single function’s parameter, you pack all parameters passed to the function into a single array. option compatible function vararg(paramarray p) t = "" for i = LBound(p) to UBound(p) x = p(i) t = t & Format(x, "0") next i vararg = t end function sub test print vararg(1) print vararg(1,2,3) print vararg(1,3,5,6,7,8) print vararg(1,1,2,3,4,5,6,7,8,9,0,2,1,3,4,5,6,7,6,3,1,1,2,3,4,2,3,3,4,2,3) end su…

[Lupp]

@gholk

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

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.

OpenDocument-v1.3-cs01-part4-formula.pdf

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”) .
The texts are available from this page maintained by the author.
Unfortunately Andrew’s site doesn’t respond currently. Does somebody know a reliable mirror? Please post it.(Online again.)

[sokol92]

@Lupp

Does somebody know a reliable mirror

Andrew Pitonyak website "refused to connect"

Yes, http port 80 (https port 443 apparently never existed) currently appears to be broken on the pitonyak site. No, there’s nothing we here could do about it. Meanwhile you can try the WayBackMachine.

[erAck]

@Lupp

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.

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.

[gholk]

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.

[Lupp]

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.

[JohnSUN]

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

[Lupp]

@JohnSUN

… 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.

[erAck]

@gholk

[…cell cuboid…] this is the first time that I see its official name

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.

[KamilLanda]

@gholk

=CHOOSERANGE(1, sheet1.A1:sheet2.B2)

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.

[JohnSUN]

@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:

[KamilLanda]

@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.

[JohnSUN]

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)

[gholk]

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