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

 

[offthelip]

I think you will find that "Names" is an invalid name for a udf because it is a reserved word. try another name for your udf. Also I think you will find that udf used on a worksheet can only return a single value, so I don't think you can do this with a function.

 

[Eric W]

Seems that "names" is a reserved word. Change the name of your UDF to "WSnames" or some such thing. Also, remove the "As String()" from the header. When you enter the formula on the worksheet, select a vertical range of cells, enter the formula, then confirm with Control+Shift+Enter.

 

[Rick Rothstein]

And the reason Names cannot be used is because it is the default property for the active workbook. To see this, type Names. (include the dot at the end) and you will see the same drop down list as when you type ThisWorkbook.Names. (again, include the dot at the end). So, since VBA automatically assumes Names is meant to refer to ThisWorkbook, you cannot use it for your procedure name.

 

[martingaleh]

No wonder. If only the ide was a legitimate one that highlighted reserved words different colors like any other ide. Alas. thanks

 

[RoryA]

You can use Names, but you'll have to call it using =module_name.names() for it to be accepted. NAMES is an XLM function and I don't think you can use those function names (so, for example, NOTE would also fail).

 

[RoryA]

And the reason Names cannot be used is because it is the default property for the active workbook.

I don't think that's true. The Workbook object (or ThisWorkbook) doesn't have a default property. The reason typing Names works is because it is treated as Application.Names (just like Range in a normal module).

 

[Rick Rothstein]

It is kind of hard to tell because you can get to Names by doing either this...

Application.ThisWorkbook.Names

or this...

ThisWorkbook.Application.Names

The reason I think Names is a default for ThisWorkbook is because if you execute this line of code in the Immediate Window...

? Names.Parent.Name

it will print out the name of the active workbook. On top of that, if you look up Workbook.Names Property in the help files, the Remarks section says "Using this property without an object qualifier is equivalent to using ActiveWorkbook.Names.

 

[RoryA]

None of that would make Names the default property of the Workbook object though.

The last part I suspect is slightly misleading. I think Names on its own (outside the ThisWorkbook module of a workbook) would be a shorthand for Application.Names, which is indeed "equivalent" to ActiveWorkbook.Names. Again, though, that would not make Names a default property of anything.

 

[Marcelo Branco]

I'm curious. How to know the default property of an object?
We "know" that Value is the default property of the object Range, but where we can find this information?
I tried the Object Browser but couldn't find anything.

M.