7

Before I begin I want to say that I realize you should never depend on default properties and I won't, but this is curious. I've always read that value is the default property for the Range Object, hence why this works:

Range("A1") = 2

However, this page claims that item is the default property of Range.

Additionally, this sub I made would suggest that cells is the default property instead:

Sub defaultprop()

Dim c As Variant


For Each c In Range("A1:A2")

    Debug.Print c.value

Next c

For Each c In Range("A1:A2").value

    Debug.Print c

Next c


End Sub
apdm
  • 1,260
  • 1
  • 14
  • 33
  • 2
    I think that it is too strong to say that one "should never depend on default properties". If I want to reference Sheet2 I see no reason to write `Sheets.Item(2)` rather than `Sheets(2)`. – John Coleman Oct 07 '15 at 18:45
  • 3
    @JohnColeman I believe the OP's general message was about default properties that don't have parameters. The ones that do are perfectly fine and where shipped to .NET to live long and well. – GSerg Oct 07 '15 at 19:05

2 Answers2

9

The default member of the Range class is called _Default and is hidden. When you enable the "Show Hidden Members" feature in the Object Browser you can see it:

VBA Object Browser

It has the exact same signature as the .Item property, so one of them is arguably an alias for the other.(*)

In any case, Range also implements the collection interface. As such, it can be used in a For Each loop — and when you do that, the loop will call .Item with each iteration and assign the current item to the loop variable.

When used outside an enumeration, for example with Debug.Print, then .Value will be used, but I can't really explain why. Maybe someone else can come up with a hint.(*)

(*) As @GSerg points out in the comments, _Default() and _Item() are not exactly equal.

Community
  • 1
  • 1
Tomalak
  • 332,285
  • 67
  • 532
  • 628
  • Do you mean the `item` property instead of the `item` method? If I try to explicitly use the `item` property I get an `Argument not Optional` message since it requires at least one argument – apdm Oct 07 '15 at 16:22
  • 1
    Actually the distinction between *property* and *method* is blurrier than one might think. A *property* consists of two functions, a getter and a setter. Getting a property value means calling the property's getter function. Especially in this case, where the getter can handle two argumens: `.Item(row, col)`. If it was called `.GetItem(row, col)`, everyone would say that's a *method*, even if all that had changed was the name. So, yes, technically `.Item()` is a *property* of the `Range` class, but it's more a concept and less of a substantial difference. – Tomalak Oct 07 '15 at 17:41
  • 6
    The `_Default`'s signature is not exact same signature as the `.Item` property - the first parameter is optional in `_Default` and required in `Item`. The only explanation that I can come up with from the COM point of view is that `_Default` is the only true default property, and it calls either `Item` or `Value` depending on whether or not a parameter was supplied. Note that if a parameter was supplied, `_Default` will not call `Value`, even though `Value` accepts a parameter too. – GSerg Oct 07 '15 at 18:39
  • @GSerg Now that you say it, it makes absolute sense. I have overlooked the difference in the signatures. That's the explanation. That I can't say `RangeObject(1)` in VBA has to do with the fact that `_Default` is marked as hidden? – Tomalak Oct 07 '15 at 18:47
  • 1
    @Tomalak it's [not that hidden](http://i.stack.imgur.com/SV1tr.png) ;) and no, that's not because you can't say `Range(1)`. You can't say `Range(1)` because you're calling the `Application.Range` method which is not happy with this parameter. This is different to declaring a `Range` variable, where you certainly can say `r(1)` to return the first cell. – GSerg Oct 07 '15 at 18:52
  • I tried with `ActiveSheet.UsedRange(1)` and that doesn't work either. But where's the difference? – Tomalak Oct 07 '15 at 19:20
  • 2
    This has to do something with late binding (weird things may happen with late binding and default properties, there was a link somewhere but I can't find it). If you declare a `Worksheet` variable, then `w.UsedRange(1)` works fine. – GSerg Oct 07 '15 at 20:13
  • 1
    Which might actually be seen as a compiler bug to tell you the truth; VB should be able to resolve this at runtime no problem. What happens is that it tries to pass the `1` parameter to the `UsedRange` property *itself*, as opposed to the default property of the object returned by `UsedRange`. You can work around this by using the function-style syntax: `MsgBox ActiveSheet.UsedRange()(1)`, even though `UsedRange` is not a function. – GSerg Oct 07 '15 at 20:22
  • 2
    Well, technically it "is" a function. VB interprets the parameter as an argument for the getter, just as with `.Item(1)`. The ambiguity arieses when thr getter accepts arguments and the returned object's default member does the same. IMHO, properties are nothing but syntactic sugar for reducing the funtion count on an object, collapsing getters and setters into one. – Tomalak Oct 08 '15 at 02:35
  • @GSerg What's really strange is that `TypeName(sheet1.Cells.[_Default](1,2))` returns `Range`. – ThunderFrame Jul 22 '17 at 00:03
  • @ThunderFrame Why is that strange? With two parameters, `_Default` apparently [calls `Item`](https://stackoverflow.com/questions/32996772/is-value-actually-the-default-property-of-the-range-object/32997154?noredirect=1#comment53824568_32997154) which returns a range. – GSerg Jul 22 '17 at 06:45
  • @GSerg Well, it seems you *can* call the implicit default member all the way through the returned Ranges: `sht.Cells(1, 2)(1, 1)(1, 1)(1, 1)(1, 1)(1, 1)` and yet you can't explicitly use `[_Default]` beyond the first call, like this: `sht.Cells.[_Default](1,2).[_Default](1,1) 'Runtime error 438 - Object doesn't support this property or method`. See the RD [issue](https://github.com/rubberduck-vba/Rubberduck/issues/3153) – ThunderFrame Jul 22 '17 at 06:56
  • @GSerg I think this [article by Eric Lippert](https://blogs.msdn.microsoft.com/ericlippert/2005/08/30/vbscript-default-property-semantics/) is the article you couldn't find. It also links to further information in another blog [post](https://blogs.msdn.microsoft.com/ericlippert/2004/05/04/simplescript-part-seven-binder-skeleton/) – ThunderFrame Jul 25 '17 at 04:52
  • @ThunderFrame I don't remember seeing that; great reading though. I was thinking about a question here about weird things which I couldn't find. – GSerg Jul 25 '17 at 07:14
-1

Yes, it appears that Value is default property of Range.

I put a very large number in cell A1.

Sub Test()
    Debug.Print "Range default = " & Range("A1") & vbCrLf _
        & "Range Text = " & Range("A1").Text & vbCrLf _
        & "Range Value = " & Range("A1").Value & vbCrLf _
        & "Range Value2 = " & Range("A1").Value2
End Sub

Results in 

Range default = 3.24643541346456E+28
Range Text = 3.25E+28
Range Value = 3.24643541346456E+28
Range Value2 = 3.24643541346456E+28

Notice that Range("A1")'s result is identical to Range("A1").Value and Range("A1").Value2

zedfoxus
  • 35,121
  • 5
  • 64
  • 63