Why can't print all worksheet names in a LibreOffice Calc file using VBA (Macros)?

[luofeiyu1]

Here is my Macros to print all worksheet names in a Calc:

Sub ListSheetNames()
    Dim oDoc As Object
    Dim oSheets As Object
    Dim i As Integer
    Dim sSheetName As String

    oDoc = ThisComponent
    oSheets = oDoc.Sheets

    For i = 0 To oSheets.Count - 1
        sSheetName = oSheets.getByIndex(i).Name
        Debug.Print sSheetName
    Next i
End Sub

How to fix it?

[Villeroy]

To be entered as array function =SHEETNAMES() with [Ctrl+Shift+Enter]

Function SHEETNAMES()
  SHEETNAMES = ThisComponent.Sheets.getElementNames()
End Function

For a vertical list: =TRANSPOSE(SHEETNAMES())

[karolus]

@Villeroy .getNames() ⇒ .getElementNames()

[Lupp]

Just for curiousity:
Do you (@luofeiyu1) actually want to get the sheetnames -which you see in the tabs line anyway- one by one in messge boxes?

[Lupp]

@Villeroy:
The suggested array function would lock an output range when running the first time.
For satisfying functionality also later inserted sheets should be shown.

[karolus]

@luofeiyu1

How to fix it?

Sub ListSheetNames()
    names = ThisComponent.Sheets.ElementNames        
    msgbox join(names, chr(10))    
End Sub

[Lupp]

This is NOT about VBA.

Avoid array formulas if you aren’t sure the locked output range meets youre needs.
See attached example:
disask126027_SheetNames.ods (17.7 KB)

It contains alternative ways to use the included macro.

Two of the demonstrated example formulas use the SEQUENCE() function only available in not too old versions.