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
@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
OK, what about
myindirect = oService.callFunction("INDIRECT", Array(fullcellname,1))
I say about this
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