Get a worksheet name using Excel VBA

This works for me.

worksheetName = ActiveSheet.Name  

Function MySheet()

  ' uncomment the below line to make it Volatile
  'Application.Volatile
   MySheet = Application.Caller.Worksheet.Name

End Function

This should be the function you are looking for

Sub FnGetSheetsName()

    Dim mainworkBook As Workbook

    Set mainworkBook = ActiveWorkbook

    For i = 1 To mainworkBook.Sheets.Count

    'Either we can put all names in an array , here we are printing all the names in Sheet 2

    mainworkBook.Sheets("Sheet2").Range("A" & i) = mainworkBook.Sheets(i).Name

    Next i

End Sub