Passing arguments to callFunction

[Lucie]

I need to access in a formula of a given sheet the same cell in several distinct sheets. In order to do that I wrote my first basic program which creates a new user function, named OFFSETTEDCELL. This function takes as arguments the relative distance of the foreign sheet (an integer) and the name of the cell (a string), and returns the value of that cell in the foreign sheet. For example, =OFFSETTEDCELL(3, A2) should return the value of the cell A2 of the 3rd sheet located to the right of the current sheet. A negative offset would designate a sheet located to the left of the current sheet.
Here is the macro:

function sheetname(index)
sheetname = ThisComponent.Sheets(index - 1).Name
End function

function mysheet()
   Dim oService As Object
   Set oService = CreateUnoService("com.sun.star.sheet.FunctionAccess")
   mysheet = oService.callFunction("SHEET", Array())
end function

function myindirect(fullcellname as String)
   Dim oService As Object
   Set oService = CreateUnoService("com.sun.star.sheet.FunctionAccess")
   myindirect = oService.callFunction("INDIRECT", Array(fullcellname))
end function

function offsettedcell(sheetoffset, cellname)
   thissheet = mysheet()
   nameofsheet = sheetname(thissheet + sheetoffset)
   offsettedcell = myindirect(nameofsheet & "." & cellname)
end function

The call of the function oService.callFunction("INDIRECT", Array(fullcellname)) raises an exception of type: com.sun.star.lang.IllegalArgumentException.
The debugger shows that the variable nameofsheet and the parameter cellname have the expected values (for example “Sheet4” and “A3”) but it seems that they are not passed to the function myindirect (or are passed in an invalid way).
Any help would be greatly appreciated.

[karolus]

@Lucie

function sheetname(index)
sheetname = ThisComponent.Sheets(index - 1).Name
End function

use only the UDF above, and in calc use:

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

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

[Lucie]

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.

[karolus]

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

[JohnSUN]

@Lucie

=OFFSETTEDCELL(3, A2)

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

[Lucie]

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

[Lucie]

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.

[JohnSUN]

@Lucie

In any case, the expression nameofsheet & "." & cellname gives a well formed value, for example "Sheet4.A3".

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

Multisheet column reference doesn't work for SUMIF or SUMPRODUCT

What you try to reference (like sheetA.B1:sheetZ.B10) spanning more than one sheet is called a cuboid in the specifying documents (‘OpenFormula’). Only very few functions accept cuboids passed to them by a reference in a parameter position. All these functions are specified to convert the entirety of values contained in the cuboid into a list. The functions doing so are all “accumulating”: SUM, PRODUCT, AVERAGE (2 accumulators - count and sum - in the last case). What you obviously expect is a …

[Villeroy]

SheetNames.ods (21.1 KB)

[Lucie]

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

[Lucie]

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

[erAck]

@Lucie

I’m starting to think that callFunction is not a reliable method.

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.

[Lucie]

@erAck Thank you for the clarification.

[JohnSUN]

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

[Lucie]

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

[sokol92]

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

[JohnSUN]

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

[Lucie]

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

[Lucie]

@JohnSUN

Function dataFromOtherSheets(sRangeName As String,Optional nBaseSheet As Long, Optional nStartOffset As Long,  Optional nEndOffset As Long) As Variant
...

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 !

[Lucie]

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