Passing arguments to callFunction

use only the UDF above, and in calc use:

=INDIRECT(SHEETNAME(SHEET()+3)&".D2")

the others of your UDFs cannot work in UDF-Context.

1 Like

Thanks for your response.
I note however that in post How to call LO Calc functions from a LO Basic function (macro)? Lupp gives as an example a basic function call nested in an other basic function call:

...
fa = createUnoService("com.sun.star.sheet.FunctionAccess")  
MsgBox(fa.callFunction("ISNUMBER", Array("NaN")))
MsgBox(fa.callFunction("ISNUMBER", Array(fa.callFunction("PI", Array()))
...

I can use your solution but it would make the formulas rather cumbersome, the aim being to average cell values from a set of adjacent sheets.

If you want to distribute your data across dozens of tables, you also have to deal with cumbersome formulas!

Not sure, but try =OFFSETTEDCELL(3, "A2")

I tried that but got the same exception. In any case, the expression nameofsheet & "." & cellname gives a well formed value, for example "Sheet4.A3".

Karolus, my final goal is to write a function which calculates the mean of values from a range of sheets. I doubt that doing that using only Calc functions is feasible. Even if it was possible, the maintenance of the formula would be a real puzzle.

Let me not believe you. I couldn’t get your Function mysheet() to return the correct index of the current sheet.

Since you refer to @Lupp, then I will refer too

1 Like

SheetNames.ods (21.1 KB)

@JohnSUN You are right, mysheet() returns 2 regardless of the sheet (?). I’m starting to think that callFunction is not a reliable method.
Thanks for the reference to cuboids, I did not know this feature.

@Villeroy I thank you for your example. However it is no more practical than what I have been doing so far.

callFunction() is reliable, however you should be aware that its execution is not bound to the current document nor current sheet, whatever that may be when calling an arbitrary macro from an arbitrary location, as it creates a temporary sheet in an interim spreadsheet document on which it operates. Any function that evaluates arguments passed as a positional reference, like SHEET() without argument does, is doomed to deliver unexpected results.

2 Likes

@erAck Thank you for the clarification.

OK, what about
myindirect = oService.callFunction("INDIRECT", Array(fullcellname,1))
I say about this
image

Thanks for the tip but I still have the same exception (IllegalArgumentException).

@Lucie, take @karolus 's advice. The thing is that UDFs (user-defined functions that are used in spreadsheet formulas) have many limitations.
For Excel this is described here, for Calc the situation is similar.

1 Like

If I needed data from several adjacent sheets, I would write something similar to this

Function dataFromOtherSheets(sRangeName As String,Optional nBaseSheet As Long, Optional nStartOffset As Long,  Optional nEndOffset As Long) As Variant 
Dim aResult As Variant, oSheets As Variant, oSheet As Variant, oRange As Variant, oDataArray As Variant
Dim nStartSheet As Long, nEndSheet As Long, nStep As Long, nSheet As Long, i As Long, j As Long, n As Long 
	oSheets = ThisComponent.getSheets()
	If IsMissing(nBaseSheet) Then	' Without base sheet, for whole spreadsheet
		If IsMissing(nStartOffset) Then
			nStartSheet = 0
		Else 
			nStartSheet = nStartOffset
		EndIf 
		If IsMissing(nEndOffset) Then
			nEndSheet = oSheets.getCount()-1
		Else 
			nStartSheet = nEndOffset
		EndIf
	Else 
		If IsMissing(nStartOffset) Then
			nStartSheet = nBaseSheet
		Else 
			nStartSheet = nBaseSheet+nStartOffset-1
		EndIf 
		If IsMissing(nEndOffset) Then
			nEndSheet = oSheets.getCount()-1
		Else 
			nEndSheet = nBaseSheet+nEndOffset-1
		EndIf
	EndIf
	If (nStartSheet < 0) Or (nStartSheet > oSheets.getCount()-1) Then 
		dataFromOtherSheets = "Wrong StartOffset=" & nStartSheet
		Exit Function 
	EndIf 
	If (nEndSheet < 0) Or (nEndSheet > oSheets.getCount()-1) Then 
		dataFromOtherSheets = "Wrong EndOffset=" & nEndSheet
		Exit Function 
	EndIf 
	If nEndSheet < nStartSheet Then 
		nStep = -1
	Else 
		nStep = 1
	EndIf 
	On Error GoTo WrongRangeName
	oSheet = oSheets.getByIndex(nStartSheet)
	oRange = oSheet.getCellRangeByName(sRangeName)
	On Error GoTo 0
	n = oRange.getRows().getCount() * oRange.getColumns().getCount() * (Abs(nEndSheet-nStartSheet)+1)
	ReDim aResult(1 To n)
	n = 0
	For nSheet = nStartSheet To nEndSheet Step nStep
		oSheet = oSheets.getByIndex(nSheet)
		oRange = oSheet.getCellRangeByName(sRangeName)
		oDataArray = oRange.getDataArray()
		For i = LBound(oDataArray) To UBound(oDataArray)
			For j = LBound(oDataArray(0)) To UBound(oDataArray(0))
				n = n + 1
				aResult(n) = oDataArray(i)(j)
			Next j
		Next i
	Next nSheet
	dataFromOtherSheets = aResult
	Exit Function 

WrongRangeName:
	dataFromOtherSheets = "Wrong RangeName=" & sRangeName
End Function

=DATAFROMOTHERSHEETS("A1:B2") - data from all sheets of the spreadsheet
=DATAFROMOTHERSHEETS("A1:B2";SHEET()) - data from next sheet and to the end of spreadsheet
=DATAFROMOTHERSHEETS("A1:B2";;2;4) - data sheets 2, 3 and 4
=DATAFROMOTHERSHEETS("B2";SHEET();-3;4) - this is an offset from the current sheet - start three sheets to the left and end 4 sheets to the right, etc.
And you can probably apply AVERAGE(), MAX() or TEXTJOIN() to this array

3 Likes

@sokol92 I will follow your advice and that of @karolus and abandon my initial idea. Thanks for the link to the Excel support document about user defined functions.

1 Like

Wow, this macro might just be the solution to my problem. I will test it over the weekend and if it is conclusive I will close the discussion. Thank you so much !

@JohnSUN Your macro works perfectly, except for negative indexes it seems which is not a problem for me. Thanks a lot again!

Of course. I didn’t test the code, just quickly sketched out an alternative to your code - of course there are bugs there.
Marking your comment as the accepted answer was… um… neat.

1 Like