User Defined Functions in BASIC

[Payon]

Hey guys!
I’ve been trying to write some UDF’s, but due to my lack of experience with BASIC I’ve no clue what exactly went wrong.
The functions themselves work as I expect them to, but every time I open the document I use them in the following error message appears:

(The error occurs not only in line 11, but in line 18 as well)

After hitting the OK Button for eight times (I call the functions a total of seven times), the sheets appear and a part of the cells using these functions have the value “#REF!”.
To make them display the correct value again I have to edit a random cell manually and hit enter (probably triggering a refresh of all sheet calculations?).

Any help is greatly appreciated.

REM  *****  BASIC  *****

sub Main
	
end sub

function getLastSheetname()
	dim lastSheet as object
	dim lastIndex as integer

	lastIndex = ThisComponent.Sheets.Count
	lastSheet = ThisComponent.Sheets(lastIndex - 1)
	getLastSheetname = lastSheet.getName
end function


function getfirstSheetname()
	getfirstSheetname = ThisComponent.sheets(0).getName
end function

[karolus]

works here…

[elmau]

What SO and version of LibO?

[Payon]

Version: 7.2.5.2 (x64) / LibreOffice Community
What does SO stand for?

[jimk]

The document must be fully opened before accessing the Sheets property.

Related question with answers:

Missing property or method 'getSheets'

I have a Macros Module that i use in some calc documents. That Module have a function that returns the sheet name of a given sheet index: by @mariosv 'How do I access the current sheet name in formula (to use in INDIRECT) - #6 by mariosv Function SheetName(Optional nSheet) If IsMissing(nSheet) Then SheetName = ThisComponent.getCurrentController().getActiveSheet().getName() Else SheetName = ThisComponent.getSheets().getByIndex(nSheet - 1).getName() EndIf End Function The problem comes …

[Payon]

Thanks a lot I at least got rid of the error messages by using JohnSUN’s idea to disable the error handler. Would it be possible to load the calc document before the UDF’s so as that the error does not even occur?

[JohnSUN]

@Payon

possible to load the calc document before the UDF’s

No. I can only repeat again:

@JohnSUN

formulas start to be calculated immediately, as the document is loaded - long before the Sheets property is created for the current component. This may seem inconvenient to you, but in other situations it can be useful

it can be useful
in some situations

[Payon]

That’s quite unfortunate, but thanks for the help

[sokol92]

Not everything is so sad. You can in a special way make the function by @JohnSUN volatile:

Function SheetName(Optional nSheet, Optional Dummy) As String
  Dim oSheets
  If IsMissing(nSheet) Then nsheet=Empty
  SheetName = ""
  On Error GoTo ErrLabel
  oSheets = ThisComponent.getSheets()
  If IsEmpty(nSheet) Or nSheet="" Or nSheet=0 Then
    SheetName = ThisComponent.getCurrentController().getActiveSheet().getName()
  Else
    SheetName = oSheets.getByIndex(nSheet - 1).getName()
  End If
  Exit Function

ErrLabel:
End Function

Examples of getting the name of the active sheet and the 3rd sheet of the document:

=SHEETNAME(;TODAY())

=SHEETNAME(3;TODAY())

The Today() function does not affect the result of the calculation, but provides Volatile.
Try opening/closing the document, adding, deleting and renaming sheets.