udf returns array somehow invalid

[martingaleh]

I just want an array function that returns the worksheet names in an array. It says the function is invalid when I type =names() in any cell. When I just test it in a test sub, it works fine.

Function names() As String()
    Dim n As Integer
    Dim k As Integer
    n = ThisWorkbook.Worksheets.Count
    Dim x As Variant
    Dim ws As Worksheet
    Dim zcount As Integer
    Dim j() As String
    ReDim j(1 To n - 1, 1 To 1)
    zcount = 1
    For Each ws In ThisWorkbook.Worksheets
        If (ws.Name <> "Output") Then
            j(zcount, 1) = ws.Name
            zcount = zcount + 1
        End If
    Next
    names = j
    k = 1
End Function

 

[Marcelo Branco]

Show hidden members then you'll see the _Default property, which has the same signature as Value

I see this
Property _Default([RowIndex], [ColumnIndex])
Default Member of Excel.Range

M.