What do you want it to do, it creates an array of sheetnames as it should.

This might be a silly question but what were you wanting the function to do.
The function itself works just fine. How were you intending to use it? Were
you  intending to use this as a user defined function (use it as a fromula in
a spreadsheet cell)?

It works fine.  Here is how to do it with a new blank workbook:

First, you need to insert that code in a module:

1) Tools > Macro > VB Editor
2) In the VBE, make sure you can see the project explorer (View > Project
Explorer)
3) Find the project that looks like your book ("VBAProject (Book1)") and
right click on it
4) Insert Module
5) You will see a blank page on the right hand side.  Copy the text and put
it in there

Public Function AllSheetNames()
    Dim Arr() As String
    Dim I As Integer
    ReDim Arr(Sheets.Count - 1)
    For I = 0 To Sheets.Count - 1
        Arr(I) = Sheets(I + 1).Name
    Next I
    AllSheetNames = Arr      ' return a row array OR
    AllSheetNames = Application.WorksheetFunction.Transpose(Arr)            
 
End Function

6) Close the VB Editor and go back to Excel.

7) Select cells a1:a3 (for example)

8) WITH THEM STILL ALL SELECTED, type =AllSheetNames(), and press <ALT><ENTER>

You will see that excel writes them all for you in cells a1:a3.
HTH

please rate