Missing property or method 'getSheets'

[santox998]

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 when i open a calc document that uses that function.
In each call it throws the error “Missing property or method ‘getSheets’”.
After that, it works properly, so it seems to be related to some initialization issues… Like the “ThisComponent” object isnt initialized when opening the calc document.

Here there is another alternative function that do the same, but has the same issue when opening the document:

by @Lupp
'How do you get a sheet name to display in a cell? - #3 by Lupp
'Function sheetName(pSheetNum)
’ REM In Calc Sheets are numbered starting with 1
’ REM The API starts numbering with 0.
’ sheetName = ThisComponent.Sheets(pSheetNum - 1).Name
'End Function

[Lupp]

Lots of user functions can only run without throwing errors if a “full API equipment” is available after loading a document.
An ActiveSheet e.g. does not exist as long as not the CurrentController (a View) is initialized.

BTW: The code you quoted from a different thread is extremely simplified. The version I actually am using:

Function sheetName(pSheet) REM Eligible for ("iterative") array-evaluation!
'V1.0 2016-12-01; Wolfgang Jäger
'Version history lost. Very old. Not updated!
'You may try to add a volatile function like NOW() somewhere to the formulae 
'using this function where the value does not matter (NOW()*0 e.g. as an addend to some number). 
'This will not help in case of 'Undo Rename Sheet'.

sheetName = ":err:general:" REM Also returned when run during loading document.
  Dim theDoc as Object, theSheet As Object, nSheets As Long
  Dim j As Long, k As Long, aEval As Long
On Error Goto emergencyExit
aEval = 1
If NOT IsArray(pSheet) Then
  aEval = 0
    Dim h(1 To 1, 1 To 1)
  h(1, 1) = pSheet
  pSheet = h
End If
theDoc = ThisComponent
nSheets = theDoc.Sheets.Count()
For j = LBound(pSheet, 1) To UBound(pSheet, 1)
  For k = LBound(pSheet, 2) To UBound(pSheet, 2)
    If (pSheet(j, k) > 0) AND (pSheet(j, k) <= nSheets) Then
      pSheet(j, k) = theDoc.Sheets(pSheet(j, k)-1).Name
    Else
      pSheet(j, k) = ":Err:num:"
    End If
  Next k
Next j
If aEval=1 Then
  sheetName = pSheet
Else
  sheetName = pSheet(1, 1)
End If
emergencyExit:
End Function 'sheetName

If you are using UDF throwing the related errors, or escaping them, you need the general recalculation after completed load. (See @JohnSUN’s comment.) You may trigger it by a handler for onViewCreated. I personally do it manually if needed.

[eeigor]

Try this.
sheetname.ods (11.7 KB)

[JohnSUN]

Welcome!
You can try this workaround:

Function SheetName(Optional nSheet) As String 
Dim oSheets As Variant
	On Error Resume Next 
		oSheets = ThisComponent.getSheets()
	On Error GoTo 0
	If IsEmpty(oSheets) Then 
		SheetName = "Recalculate the formulas!"
	Else 
		If IsMissing(nSheet) Then
			SheetName = ThisComponent.getCurrentController().getActiveSheet().getName()
		Else
			SheetName = oSheets.getByIndex(nSheet - 1).getName()
		EndIf
	EndIf
End Function

Yes, you still have to press Shift+Ctrl+F9 after loading the sheets, but at least you’ll get rid of the annoying error message while loading the spreadsheet. Yes, 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 (for example, preventing a spreadsheet from opening in the presence of strangers)

[jimk]

GoTo 0—what strange syntax. I see from the docs that instead of going to line 0 or label 0, it disables the error handler. Seems deserving of an inline comment.

[eeigor]

@jimk

Seems deserving of an inline comment.

Yes, it disables it and passes it to a higher level if one exists in the call stack. And this has always been the case in VBA.
Worse is another thing. The Resume Next statement resets the error code (always 0), which is wrong.

[sokol92]

@jimk

GoTo 0—what strange syntax

This was back in Microsoft Visual Basic 1.0 for DOS.

[jimk]

Oh, I used Basic a lot in the 80’s, so that is too recent for me.

[sokol92]

And I started with IBM/360 in the 70s.

[JohnSUN]

@jimk Yes, you’re right, it’s a strange design, but there are a lot of strange things in this world that you just get used to. For example, to the letter “c” in the Pacific Ocean, where it occurs three times and denotes three different sounds; or to the ridiculous words EndIf or Wend - you just get used to it and use it without hesitation.

[sokol92]

@JohnSUN

ridiculous words EndIf or Wend

By the way, it is these two words that confuse the Markdown code interpreter and often “spoil” Basic program code in forum posts. I use End If and Do While True / Loop for this reason.