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

 

[Rick Rothstein]

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.

I see what you are saying when you say "equivalent", and I'll be the first to admit that I am not all that conversant with Object "stuff", but I was always under the impression that if you used a property by itself and it worked the same as using it with a specific object reference, that that property was a default property of that object. Hence, when Names.Parent.Name printed the active workbook name, I figured Names was the default property of the workbook object.

 

[RoryA]

I was always under the impression that if you used a property by itself and it worked the same as using it with a specific object reference, that that property was a default property of that object

Wrong way round. If you can use the object without specifying the property - like Range("A1") instead of Range("A1").Value - then the property is the default.

 

[Marcelo Branco]

If you can use the object without specifying the property - like Range("A1") instead of Range("A1").Value - then the property is the default.

Is this the only way to find out the default property of an object?
See my post 10

M.

 

[RoryA]

It's flagged in the object browser with a blue circle. Unfortunately in most cases the default is a hidden _Default property!

 

[Rick Rothstein]

Wrong way round. If you can use the object without specifying the property - like Range("A1") instead of Range("A1").Value - then the property is the default.

Ohhhh, now I get it. See, I told you I was not "all that conversant with this Object 'stuff'".

 

[Marcelo Branco]

It's flagged in the object browser with a blue circle. Unfortunately in most cases the default is a hidden _Default property!

hmm...for sure i'm missing something obvious but using the Object Browser
Class
...
...
Range
...

Looking at Members of Range i can't see a blue circle or anything similar near Value

M.

 

[RoryA]

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

 

[Marcelo Branco]

Ah! but selecting Value i can see
Property Value([RangeValueDataType])
Member of Excel.Range

It seems it's the only property with this text (bold).
Does it indicate the default property?

M.

 

[RoryA]

No, that's just an optional argument.

 

[Marcelo Branco]

No, that's just an optional argument.