Cells property, early and late binding
Cells property, early and late binding
Cells property, early and late binding
Cells property, early and late binding
Cells property, early and late binding
Cells property, early and late binding Cells property, early and late binding Cells property, early and late binding Cells property, early and late binding Cells property, early and late binding Cells property, early and late binding Cells property, early and late binding Cells property, early and late binding
Cells property, early and late binding Cells property, early and late binding
Cells property, early and late binding
Go Back  Xtreme Visual Basic Talk > > > Cells property, early and late binding


Reply
 
Thread Tools Display Modes
  #1  
Old 03-01-2010, 02:43 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default Cells property, early and late binding


Hi,

I've been aware for some time that the Cells() property is early bound but the Cells.Item() property is late bound. In VBA this all works seamlessly apart from the fact that we lose intellisense in the VBA IDE in the latter case:
Code:
'VBA code
    Cells.      '<---- we get intellisense
    Cells(1).   '<---- we do not get intellisense
However, in VB .Net with Option Strict On this means that we have an extra consideration in that we have to explicitly convert to an Excel.Range if we want to set the range reference to a range variable:
Code:
'VB .Net code
Option Strict On

Imports Excel = Microsoft.Office.Interop.Excel

'
'
'
        Dim oApp As New Excel.Application
        Dim oWB As Excel.Workbook = oApp.Workbooks.Add()
        Dim oWS As Excel.Worksheet = CType(oWB.Worksheets(1), Excel.Worksheet)

        Dim oRng1 As Excel.Range = oWS.Cells                        'OK
        Dim oRng2 As Excel.Range = oWS.Cells(1)                     'implicit conversion fails
        Dim orng3 As Excel.Range = CType(oWS.Cells(1), Excel.Range) 'explicitly convert to an Excel.Range instead
I'm comfortable enough with this because I'm aware of it and it is trivial to handle by explicitly converting to an Excel.Range. We also had a pretty good discussion about the Cells property a little while back in the VBA board which looked at some of its inner workings. My question is to dig a little bit deeper into the way that this has been implemented. I'm trying to get a better understanding about the reasoning behind the Cells.Item() property returning an Object rather than a Excel.Range? Any suggestions?
Reply With Quote
  #2  
Old 03-01-2010, 04:48 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey Colin,

I now think that stumbling on the Range.Cells/Range.Cells.Item oddity is a right of passage when getting involved with .NET. It is odd enough with VBA, but it's even stranger when you start using .NET.

In the Daily Dose of Excel article you referenced, the comment by James Cane basically gets it right.

In this thread on XVBT between myself and Nate Oliver back in 2005 we struggled with this terribly. The first 6 posts are meandering until I finally realized in the middle of writing the 7th post that the .Cells and .Cells.Item property are basically not doing anything. It's a hidden _Default property that is really doing all the work.

So when you call 'Range.Cells' or 'Worksheet.Cells', the Cells property simply returns a Range, no more no less, and the Cells property does not take any arguments, neither optional nor otherwise (despite how it looks!). In fact, calling Range.Cells is really redundant, as the Cells property here merely returns the Range itself. (It's not redundant when calling Worksheet.Cells).

The Range.Item(RowItem, [ColumnItem]) property does take two arguments, the second of which being optional. This property is actually read-write, that is, it has a property let that allows the caller to assign a value to it, as in:

Code:
' VBA
Dim myRange As Excel.Range
myRange = Application.Range("A1:C3")
myRange.Item(1,1) = "Hello"
The above allows the .Item property to receive a value and assign it to the value of the indexed cell, without explicitly requiring the use of the Value property, as in:

Code:
' VBA
myRange.Item(1,1).Value = "Hello"
This, therefore, also allows you to use the same in VB.NET:
Code:
' VB.NET
myRange.Item(1,1) = "Hello"
The above looks impossible in .NET because omitting the default .Value property is not allowed. But, here, .Item(1,1) is not returning a cell, it is receiving a value of "Hello" and assigning it to the cell indexed by position (1,1). That is, it is the property let accessor for the Item property that is being called here (which is property set, from .NET's point of view, as there is not let vs. set in .NET) and is not the property get accessor.

When you call Range.Cells([RowItem], [ColumnItem]) a few things are / are not happening:

(a) You are *not* passing arguments into the Range.Cells property, as the Range.Cells property does not have any parameters!

(b) You are *not* calling the Range.Cells.Item(RowItem, [ColumnItem]) property! As much as it might look this way, the Item property is not the default property for the Excel.Range class.

(c) What you are really calling is Range.Cells._Default([RowItem], [ColumnItem]), which is exactly the same as calling Range._Default([RowItem], [ColumnItem]) or Range([RowItem], [ColumnItem]). The _Default property is hidden, as indicated by the leading underscore ("_") in its name, so it cannot actually be referenced by the name "_Default". This property is the default indexed property however, so it *can* be referenced without explicitly using "_Default", as in 'Range.Cells([RowItem], [ColumnItem])' or 'Range([RowItem], [ColumnItem])'.

So, while it *looks* like the default indexed property for the Range object is the Item(RowItem, [ColumnItem]) property, it is not. The very similar looking _Default([RowItem], [ColumnItem]) property is instead.

Ok, so what's the difference, why didn't they just use the Item property here? The answer is that I'm not 100% sure. There's something subtle here, but I'm sure they had a reason. The only difference in the signature for the Range.Item property vs. the Range._Default property is in the signature:

Code:
(1) Range.Item(RowItem, [ColumnItem])
(2) Range._Default([RowItem], [ColumnItem])
The only difference is that the Item property requires that at least one index be provided, which makes sense. The _Default property, however, does not require any parameters at all. In this case, it seems to return either the Range.Value result or the Range itself depending on whether the _Default property is called against the Range itself, or against the Range.Cells.

Code:
'VBA
Dim myRange As Excel.Range
set myRange = Application.Range("A1")
MsgBox TypeName(myRange())  ' Calls Range._Default() and returns the Range.Value
MsgBox TypeName(myRange.Cells())  ' Calls Range._Default() and returns the the Range
I really have no idea what they are doing here. I don't know why they would want to allow the default parametrized property to actually be called without any parameters, and I certainly don't know why they want the result returned from Range() to differ from Range.Cells().

So the _Default property is a read-write property and has two optional parameters. When read, the _Default returns the cell being indexed, exactly as the Item property does. When *writing* to the _Default property or to the Item property, however, one can assign a value to the Range exactly as one can in VBA without explicitly calling the .Value property. As a result, all of the following are valid in VBA:
Code:
' VBA
Dim myRange As Excel.Range
myRange = Application.Range("A1:C3")

myRange.Value = 5
myRange = 5
myRange(1,1) = 5  ' Calls Range._Default(1,1) = 5'
Or, using the Item property:
Code:
myRange(1,1) = 5  ' Calls Range.Item(1,1) = 5'
So the _Default property and the Item property facilitate the ability to assign a value to an indexed cell without explicitly having to use the Value property (e.g., "myRange(1,1) = 5" or "myRange.Item(1,1) = 5").

Note that if the Range.Item and the Range._Default properties were read-only, then "myRange(1,1) = 5" or "myRange.Item(1,1) = 5"would not be possible, and the code would have to explicitly utilize the Value property, as in:
Code:
myRange(1,1).Value = 5
' - or -
myRange.Item(1,1).Value = 5
In that other thread between myself and Nate Oliver back in 2005, Nate hypothesizes that the default properties could "daisy chain" thereby allowing a call such as "myRange(1,1) = 5" to be legal if Item property were read-only. In this hypothesized situation, the Item property would return a Range which is then assigned the value of 5 via the default Range.Value property. But it would *not* work this way: the compiler would see "myRange(1,1) = 5" as an attempt to assign a value of 5 to the default indexed property. Therefore, if the Range._Default property were read-only, this assignment would fail.

Ok, so we've established why the hidden Range._Default([RowItem], [ColumnItem]) property is the default property instead of the Range.Item(RowItem, [ColumnItem]) property. But, other than a subtlety between the optional parameters, there is not a significant difference between the two. When reading from the property, they both return the cell being indexed; when writing to the property, they both accept a value or range, in which case the property assigns the value (or value held by the range passed in) to the indexed cell.

Ok, so the *real* question that you are posing is: why are the Range._Default and Range.Item properties typed to return an 'Object' instead of being typed to return a strong-typed 'Range'?

The reason for this is that while the Range._Default property can only return a Range object, it can receive a value of any basic type, including string, boolean, double, currency, datetime, CVErr, or other basic types that implicitly convertible into these, such as integer or long. In fact, it can even receive a range object, in which case the value held by the range passed in is assigned to the cell within the range being indexed.

But properties in VBA/VB6 (and I'll assume for COM in general, but I'm not an expert on that) are required to have the property let, set, and/or get accessors all agree on the return type. So if the property get returns a Range then the property let would have to accept a Range. But since the property let needs to be able to accept a value type, while the property get needs to return a reference type (specifically, a Range), then the only way to have the let and get accessors to agree on type is to go as wide as possible, and define the property as reading and writing a Variant, which comes through as 'System.Object' in .NET.

Ok, well sorry for the absurdly long explanation. Maybe on my next attempt I'll be able to explain this succinctly. Anyway, I hope this makes some sense...
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 03-02-2010 at 02:19 PM.
Reply With Quote
  #3  
Old 03-02-2010, 01:02 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Hey Mike,
Quote:
Anyway, I hope this makes some sense...
That's a fantastic explanation and things are much clearer to me now. I now understand that _Default is the default property and that it is read-write (it can return an Range object but can also write a literal value) which necessitates a Variant return type. Very clever. It's funny how I must have known this implicitly from using Excel for all these years without actually realising it!

Quote:
In this thread on XVBT between myself and Nate Oliver back in 2005 we struggled with this terribly
That's a great link. I'm still unclear on the Item() property though.
On this thread it's been established that Item() is read-only and that, in conjuction with the explanation about _Default, makes sense to me. But on that thread, the commentary conflicts this:
Quote:
Originally Posted by Mike
This was wrong. I had not realized that .Item() is actually Read-Write (who knew?)
Quote:
Originally Posted by Mike
This compiles 100% fine? Odd, you'd think that .Item() is read-only. I guess that it is not... And, well, if you look in the Object browser you can see that it is read-write.

Note that it's not read-write in the sense that you can Set a Range to it (there is no Property Set), so it's not symmetrical... I'm not sure why Microsoft did it this way; they certainly didn't feel compelled to do it for the Worksheet.Range() property, which is read-only and handles the paremeterless _Default() call (which in turn calls .Value) just fine.
So this still hasn't quite clicked into place for me.

If I call it explicitly like thus:
Code:
'VB .Net code
Dim oRng2 As Excel.Range = oWS.Cells.Item(1)                     'implicit conversion fails
Now, in this case I have explicitly called .Item() so, as far as I'm concerned, this has completely removed ._Default() from the equation: _Default() is not called here.

Yet, this code in VB .Net fails because the return type of Item() is an Object (or Variant) and not a Range. Since it is read-only, my question is why isn't the return type for Item() a Range?

Cheers,
Colin
Reply With Quote
  #4  
Old 03-02-2010, 02:30 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by Colin Legg View Post
That's a fantastic explanation and things are much clearer to me now.
Well, I appreciate the compliment, but I actually had to revamp it significantly because of your astute comments below...

Quote:
I now understand that _Default is the default property and that it is read-write (it can return an Range object but can also write a literal value) which necessitates a Variant return type. Very clever. It's funny how I must have known this implicitly from using Excel for all these years without actually realising it!
Yep, without at doubt. This is why we lose IntelliSense in VBA and why we have to explicitly cast when using it in .NET.

Quote:
That's a great link. I'm still unclear on the Item() property though. On this thread it's been established that Item() is read-only ... But on that thread, the commentary conflicts this:

"I had not realized that .Item() is actually Read-Write (who knew?)"

and

"Odd, you'd think that .Item() is read-only. I guess that it is not... And, well, if you look in the Object browser you can see that it is read-write."

So this still hasn't quite clicked into place for me.
Good pickup. I was correct in that original quote, although, I have no idea how I could see this in the Object browser, I can't seem to see this now. In any case, some testing in VBA shows that the Item property has both a property get and a property let accessor, but not a property set. (.NET can't tell the difference between a let and a set accessor anyway.) So it is definitely read-write.

Due to this correction, I've had to extensively re-write my original note above. I don't know that it's worth re-reading, but I had to change about 40%+ of it because of this! So thanks for picking up the discrepancy.

Quote:
If I call it explicitly like thus:
Code:
'VB .Net code
Dim oRng2 As Excel.Range = oWS.Cells.Item(1)                     'implicit conversion fails
Now, in this case I have explicitly called .Item() so, as far as I'm concerned, this has completely removed ._Default() from the equation: _Default() is not called here.
Yes, again, I was wrong. the Range.Item property is read-write and is *almost* identical to the Range._Default property. In fact, they are so similar, it's pretty unclear why they didn't simply make the Range.Item property the default parametrized property. In my updated discussion in the first post, I speculate a bit, but I don't really know.

So, the bottom line is that the Item property is read-write, just like the Range._Default property. And, because it can receive a value type, but returns a reference type (a Range), it must have a Variant return type, which comes through as System.Object in .NET. Therefore, in VB.NET, the following assignment is allowed:

Code:
Dim oRng2 As Excel.Range = oWS.Cells.Item(1)
In this case, the property get accessor of the Range.Item property is being called. But, since the Range.Item property is forced to return a Variant (i.e., a System.Object), the above call must be explicitly cast to an Excel.Range if using Option Strict On:

Code:
Dim oRng2 As Excel.Range = Ctype(oWS.Cells.Item(1), Excel.Range)
So, sorry for the incorrect gibberish in my original posting, but thanks for pointing it out so I could correct it!

Thanks Col ,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #5  
Old 03-03-2010, 01:02 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Hi Mike,

I'll have a careful re-read through and try to digest it.

One thing I spotted is what you said here in post #1:
Quote:
Code:
'VBA
Dim myRange As Excel.Range
set myRange = Application.Range("A1")
MsgBox TypeName(myRange())  ' Calls Range._Default() and returns the Range.Value
MsgBox TypeName(myRange.Cells())  ' Calls Range._Default() and returns the the Range
I really have no idea what they are doing here. I don't know why they would want to allow the default parametrized property to actually be called without any parameters, and I certainly don't know why they want the result returned from Range() to differ from Range.Cells().
Expanding on that, there's only a difference when () are included after the get property call:
Code:
'VBA CODE
Sub foo()
    
    'multiple cells
    Debug.Print TypeName(Range("A1:A10"))           'range
    Debug.Print TypeName(Range("A1:A10").Cells)     'range
    
    'single cell
    Debug.Print TypeName(Range("A1"))               'range
    Debug.Print TypeName(Range("A1").Cells)         'range
    
    'multiple cells
    Debug.Print TypeName(Range("A1:A10")())         'variant array
    Debug.Print TypeName(Range("A1:A10").Cells())   'range

    'single cell
    Debug.Print TypeName(Range("A1")())             'double
    Debug.Print TypeName(Range("A1").Cells())       'range

    
End Sub
It looks to me like, where VBA.TypeName() is returning a Variant Array and a Double, it is because the () are causing the range returned to be dereferenced. This is a known caveat in VBA/6 with arguments in function calls.

To get the Cells property to do the same thing as the Range property, the () have to be doubled up with the syntax becoming:
Code:
   Debug.Print TypeName(Range("A1").Cells())       'range
   Debug.Print TypeName(Range("A1").Cells()())     'double



Hmmm... lots of work to do here...

Cheers,
Colin
Reply With Quote
  #6  
Old 03-03-2010, 12:57 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Yeah, pretty odd stuff. I don't understand the purpose of dereferencing via empty brackets like that -- I prefer to be explicit by calling the Value property. But I guess they wanted to enable this functionality for some reason.

It is probably for this reason that the _Default property and the Item property differ slightly here, but I'm not sure.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #7  
Old 03-04-2010, 05:05 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

I want to check this one with you to make sure I've got it straight:
Code:
'VBA
Debug.Print TypeName(Range("A1").Cells())
In this case the _Default property is not being called... Cells() simply returns a range.

My reasoning behind this conclusion is that the purpose of the _Default property is to provide a set of logic which, depending on the context and the arguments passed, determines internally whether to call either the Item property or the Value property.

If _Default property was being called then the _Default property would have to internally call Value, because an internal call to Item would not be permitted because Item requires at least one argument (no argument has been passed here). This means that if _Default was being called, it would return the value of the cell. But VBA.TypeName returns a Range, so the _Default property cannot be being called here.

However, in this case:
Code:
'VBA
Debug.Print TypeName(Range("A1").Cells()())
Cells() returns a Range and the extra set of () causes the _Default property to be called. Since no arguments have been passed it internally calls Value and therefore VBA.TypeName returns the type of the cell's value (eg. Double).


So going back to what you said earlier:
Quote:
Code:
'VBA
Dim myRange As Excel.Range
set myRange = Application.Range("A1")
MsgBox TypeName(myRange())  ' Calls Range._Default() and returns the Range.Value
MsgBox TypeName(myRange.Cells())  ' Calls Range._Default() and returns the the Range
I guess I'm contradicting your comments. What I've just said means that Range() does call _Default but Cells() simply returns a Range and does not call _Default, which would explain the differing results from VBA.TypeName.

Is that right? Please openly correct any mistakes because I want to make sure I understand this!

Cheers
Colin

Last edited by Colin Legg; 03-04-2010 at 05:18 AM.
Reply With Quote
  #8  
Old 03-04-2010, 07:16 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Yes, definitely confusing stuff...

According to the object model exposed to .NET, the Range.Cells property is simply a read-only property that does not have any parameters. Therefore, I think that when we use:

Code:
Range.Cells()
we are actually calling Range.Cells._Default().

The mystery, then, becomes, how can the Range._Default() property vary it's result to return either a Range or the value held by the Range, as in:

Code:
MsgBox TypeName(myRange())        ' Calls Range._Default() and returns the Range.Value
MsgBox TypeName(myRange.Cells())  ' Calls Range._Default() and returns the the Range
This can only happen if the Range.Cells property is not returning exactly the same range as the object it is being called against. We could think of this as a "Cells" object that inherits from Range, but is not *exactly* a Range class. True inheritance is not used to do this, however, as there is no Excel.Cells class, but I think something similar to what happens with the Range.Rows and Range.Columns properties is going on:

Code:
Dim myRange As Excel.Range
Set myRange = Application.Range("A1:C3")

MsgBox TypeName(myRange)       ' <-- "Range"
MsgBox myRange.Count           ' <-- 9

MsgBox TypeName(myRange.Rows)  ' <-- "Range"
MsgBox myRange.Rows.Count      ' <-- 3
Note that in the above, TypeName(myRange) and TypeName(myRange.Rows) both return a Range object, so true inheritance is not occurring. (And there is no Excel.Rows class either, so only an Excel.Range could be returned here.) But, somehow, the Range.Count property knows to return either the total number of cells or the total number of rows in the range. Pretty cool, right? There would appear to be some kind of internal flag that tells the Range how it should behave: as a generic Range, as a Rows collection, as a Columns collection, etc. In a fully object-oriented language, one would create a Excel.Rows class that inherits from the Excel.Range class, but here they had to resort to a trick.

Anyway, I can only guess, but it would seem that the Range.Cells property is returning an Excel.Range object that has some internal flag set that causes the Range._Default property to return the Excel.Range instead of the Range.Value result when called without any of the optional arguments provided.

This is my best guess though, I don't know anything for sure, although, I don't see how else this could be done in this case.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 03-04-2010 at 07:24 AM.
Reply With Quote
  #9  
Old 03-04-2010, 07:45 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Quote:
we are actually calling Range.Cells._Default().
Quick question because I'm still a touch confused... if Cells() is calling _Default then why are these different?
Code:
    Debug.Print VBA.TypeName(Range("A1:A10").Cells())           'range
    Debug.Print VBA.TypeName(Range("A1:A10").Cells.[_Default])  'variant()

Quote:
But, somehow, the Range.Count property knows to return either the total number of cells or the total number of rows in the range. Pretty cool, right?
Right, very cool.
Kassy picked up on this on the thread I referenced earlier:
http://www.xtremevbtalk.com/showpost...58&postcount=4

That's the only sort of situation where Range.Cells has any practical value for me. For example, if Range holds a "column" or "row" flavour range but you need to enumerate through individual cells, then you can use Range.Cells, eg.
Code:
Sub VBAfoo()

    Dim rRow As Range
    Dim rCell As Range
    
    Set r = Sheet1.Rows(1)
    
    For Each rCell In rRow.Cells
        '
    Next rCell
    
    
End Sub

hmmm.... keep the thread here or move to the Excel VBA forum?!

Last edited by Colin Legg; 03-04-2010 at 07:56 AM.
Reply With Quote
  #10  
Old 03-04-2010, 09:04 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Ok, you are right, I was wrong...

In VBA, the call to:

Code:
myRange.Cells()
is exactly the same as:

Code:
myRange.Cells
I was being fooled because I've been coding in C# too long, where the separation between property syntax and method syntax is rigid. In VBA, one can omit the empty parentheses when calling a method that takes no parameters, and, as I have learned here, one can add empty parentheses to a property call that takes no parameters. I find the former a little odd, and I find the latter pretty crazy -- but this is after experience with other languages. I didn't seem to complain much when I was using VBA all the time!

Using OleView, which you can learn about using here and here, if you look up the IRange class you'll find the IRange.Cells property defined as follows:

Code:
[propget, helpcontext(0x000100ee)]
HRESULT _stdcall Cells([out, retval] Range** RHS);
This basically means that the Range.Cells property is read only (there is only a propget, and no proplet or propset defined), which has no parameters and the out/return value is a reference to a Range object. (And Range implements the IRange interface as its default, so IRange and Range are *pretty much* one and the same.)

So, in C#, I could only call Range.Cells without the parentheses. In fact, even in VB.NET if you try to include the parentheses Range.Cells(), it will not compile, giving the error message:

Quote:
Non-invocable member 'Excel.Range.Cells' cannot be used like a method.
But in VBA or VB 6.0, using the extra set of (unnecessary) parentheses as part of the property call is just fine. I guess the interpretation is that calling (a) a property that has no properties is the same as calling (b) a property that only has optional parameters where no arguments have been provided. Therefore, in both cases, one should allow omitting or adding the empty parenthesis.

Because I've been using .NET for too long, I was incorrectly interpreting a call to:

Code:
myRange.Cells()
as a call to the Range.Cells property, which has no parameters, followed by a call to the Range._Default([RowIndex][ColumnIndex]) property, with neither optional argument provided. But I was wrong and you had it right:

Code:
myRange.Cells()   ' Calls Range.Cells.
myRange.Cells()() ' Calls the Range.Cells._Default().
Quote:
Originally Posted by Colin
hmmm.... keep the thread here or move to the Excel VBA forum?!
I think it's fine here. There is definitely a lot of overlap -- and it helps to explain why I'm making so many mistakes!
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 03-04-2010 at 10:22 AM.
Reply With Quote
  #11  
Old 03-04-2010, 09:27 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Okay, that's great. I think I'm nearly up and with this now.

One more thing is slightly unsettling.
If the thought is correct that
Code:
myRange.Cells()() ' Calls the Range.Cells._Default().
Then I'm not sure why in VBA the first one gives us intellisense but the second one does not??
Code:
range.Cells()().
range.Cells().[_Default].
(Just to clarify, I understand why the 2nd one doesn't because the _Default return type is a Variant - but this means that the first one can't be calling _Default, right?)


Thanks, Mike, as ever, for your help, time and patience.

Last edited by Colin Legg; 03-04-2010 at 09:38 AM.
Reply With Quote
  #12  
Old 03-04-2010, 09:32 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by Colin Legg View Post
I think a lot of the discussion on this and referenced threads is undocumented, so I might try to collaborate the information into an article.
That would be awesome. It would make for a *great* blog post, but I just haven't had the time myself.

If you do something with it, could you do me a favor and just leave off all my bad answers?
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #13  
Old 03-04-2010, 09:33 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Hi Mike,

I edited my post #11 because I spotted another problem with the theory...
Reply With Quote
  #14  
Old 03-04-2010, 10:03 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

LOL, yeah, I just noticed that...

Quote:
Originally Posted by Colin Legg View Post
One more thing is slightly unsettling.
If the thought is correct that
Code:
myRange.Cells()() ' Calls the Range.Cells._Default().
Then I'm not sure why in VBA the first one gives us intellisense but the second one does not??
Code:
range.Cells()().
range.Cells().[_Default].
Ah! And this further explains why I was fooled into thinking that writing Range.Cells() is actually a call to Range.Cells._Default() -- because VBA's IntelliSense *tells* you that it is!! But it isn't! The IntelliSense is *not* in sync with the compiler here! (IntelliSense giving the wrong information can happen occasionally, but it's usually not this blatant.)

I feel much, MUCH better now -- no wonder I was fooled. But my previous explanation that VB.NET and C# does not allow appending the extra parenthesis to a property that has no parameters is also valid: in .NET, the extra parentheses would have to be for the default indexed property.

But as to your question, the answer, I believe, is that IntelliSense simply out of sync with the compiler. I don't know how the metadata is attached for IntelliSense, but it does not seem to directly read the IDL that the compiler would use. I assume this, because the IntelliSense and the compiler can sometimes not match. I've even seen this (in one rare case) in C#, witness my answer from the thread C# Potential Interview Question…Too hard?, where I wrote:

Quote:
Further, IntelliSense within the C# IDE suggests that there are two overloads for the class B (because there are, or at least should be!), but the B.Foo(int n) version actually can't be called (not without first explicitly casting to a class A). The result is that the C# IDE is not actually in synch with the C# compiler.
So I don't know how the IntelliSense is determined, but even in .NET, it can become incorrect with respect to what the compiler allows or what will actually be called. In this case, we now have three examples where the VBA IntelliSense is giving misreading signals:

(1) When typing "myRange.Cells(" the IntelliSense shows that you are calling Range._Default([RowIndex][ColumnIndex]). This turns out to be correct if you actually provide an index, but if you omit them both, concluding with "myRange.Cells()", then you are in fact still calling the Range.Cells property. So IntelliSense is not wrong here, but it is misleading if you do not provide parameters. In VB.NET, by the way, it shows *both* the Range.Cells and the Range._Default([RowIndex][ColumnIndex]) overloads when you commence typing "myRange.Cells(", but VBA's IntelliSense cannot show multiple overloads, so it shows only one.

(2) When typing "myRange.Cells()()." the IntelliSense shows the members for the Range class. It also shows the same when you type "myRange.Cells()." or just "myRange().". I believe that IntelliSense is just wrong here, assuming that the _Default property returns a Range object, while it can actually return a Range or Value and, therefore, has a Variant return type. In simple terms: the IntelliSense and the compiler do not match.

(3) When typing "range.Cells().[_Default]." the IntelliSense does not provide any options. There are two ways to interpret this: either (a) IntelliSense is correctly showing nothing because the Range._Default property returns a Variant, or (b) the Range._Default property is a hidden member and so no IntelliSense is provided.

I believe that it is version (b) in this case. We have already shown that the IntelliSense is wrong for the Range._Default property, when called implicitly, so I don't see why it would get it right when called explicitly. When calling a hidden member, no IntelliSense is provided, much like when using late binding. The call is actually early-bound, but you don't get any IntelliSense support because they are discouraging the coder from trying to call the hidden member like this. In .NET one can get a similar result by adding the EditorBrowsableAttribute to a member.

Well, I hope this does the trick! You've definitely dug up some very cool stuff Colin, thanks for getting the brain cells going!

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 03-04-2010 at 04:07 PM.
Reply With Quote
  #15  
Old 03-04-2010, 12:11 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Aha! Yes, Mike - I think you've absolutely nailed it.
Great work, my friend.

I'm much more comfortable these range class properties now, but am slightly alarmed by the intellisense and compiler being out of synch like that - but it really is a very logical explanation for what we are seeing. Ouch! No wonder this is so difficult to nail down.

Quote:
Originally Posted by Mike
That would be awesome. It would make for a *great* blog post, but I just haven't had the time myself.

If you do something with it, could you do me a favor and just leave off all my bad answers?
Sure, I'll leave them out... if you fill in the technical editor role. LOL

Thanks again,
Colin
Reply With Quote
  #16  
Old 03-04-2010, 12:28 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by Colin Legg View Post
Aha! Yes, Mike - I think you've absolutely nailed it.
Great work, my friend.
LOL, well, nothing like getting it on the twelfth try.

Quote:
I'm much more comfortable these range class properties now, but am slightly alarmed by the intellisense and compiler being out of synch like that
Yeah, really surprising, but there is no doubt that this is the issue. Given that we've now found that both VBA and C# can have the IntelliSense out of synch with the compiler, I really do wonder where IntelliSense (in both systems) gets its information?

Quote:
but it really is a very logical explanation for what we are seeing. Ouch! No wonder this is so difficult to nail down.
Yes, I feel much better now! I should have looked at the IDL earlier instead of relying on IntelliSense. Still, I had no idea that IntelliSense was lying to us until post #14.

Quote:
Sure, I'll leave them out... if you fill in the technical editor role. LOL
lol, ok, sounds good.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #17  
Old 03-09-2010, 04:54 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Okay, so coming round a full circle and back to that old discussion....

Quote:
Originally Posted by Mike back in October 2005
Ok, and to circle back to the scene of the crime, we are still left with one more mystery on the .Net side of things, to review:


Code:
xlWS.Range("a1") = "hello"
The VB.Net compiler complains about this stating "Property 'Range' is read only". This makes sense as the default '.Value' property is not recognized in .Net.
So, in this case, _Default isn't called at all, hence the interpreration that it is an attempt to write to the read-only Range property. I'm not 100% clear why _Default is called in the VBA equivalent but not in .Net, though. Is it because a class's default member cannot be implicitly called in VB .Net? I noticed that this is perfectly fine in VB .Net, explicitly calling _default with no arguments:
Code:
'VB .Net
xlWS.Range("A1")() = "Hello"
I think this comes back to what you said earlier in the thread here (bolded):
Quote:
I've been coding in C# too long, where the separation between property syntax and method syntax is rigid. In VBA, one can omit the empty parentheses when calling a method that takes no parameters, and, as I have learned here, one can add empty parentheses to a property call that takes no parameters. I find the former a little odd, and I find the latter pretty crazy -- but this is after experience with other languages. I didn't seem to complain much when I was using VBA all the time!
but I just want to tie it up in my own head...

Last edited by Colin Legg; 03-09-2010 at 05:04 PM.
Reply With Quote
  #18  
Old 03-09-2010, 06:00 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey Colin,

Quote:
Originally Posted by Colin Legg View Post
Okay, so coming round a full circle and back to that old discussion....

Quote:
Originally Posted by Mike Rosenblum
Ok, and to circle back to the scene of the crime, we are still left with one more mystery on the .Net side of things, to review:

Code:
xlWS.Range("a1") = "hello"
The VB.Net compiler complains about this stating "Property 'Range' is read only". This makes sense as the default '.Value' property is not recognized in .Net.
So, in this case, _Default isn't called at all, hence the interpreration that it is an attempt to write to the read-only Range property.
This is correct, but the more direct point isn't that the parametrized-default property named "_Default" isn't being called, but that the parameterless-default property named "Value" isn't being called. Since you are providing no parameters here, only the parameterless default would be possible -- that is, invoking the _Default property here is never an option due to the syntax being used.

And since .NET cannot distinguish whether a parameterless property is being called or not (because it lacks the let vs. set keyword that enables this distinction in VBA), .NET always assumes that a set call is being made and any parameterless default property effectively does not exist. Therefore, in VB.NET, the call to xlWS.Range("a1") = "hello" is interpreted as an attempt to assign the string value "hello" to the Worksheet.Range method, which fails, of course, because methods can only return values. This call succeeds in VBA, however, because a 'let' assignment is assumed by default, so the default parameterless property (the "Value" property in the case ofa Range object) is called.

Quote:
Originally Posted by Colin
I'm not 100% clear why _Default is called in the VBA equivalent but not in .Net, though. Is it because a class's default member cannot be implicitly called in VB .Net? I noticed that this is perfectly fine in VB .Net, explicitly calling _default with no arguments:
Code:
'VB .Net
xlWS.Range("A1")() = "Hello"
Without the extra empty parentheses, as in xlWS.Range("A1") = "Hello", this command would be an attempt to assign "Hello" to the Worksheet.Range method in .NET, which fails because one cannot assign a value to a method. In VBA, however, a 'let' assignment is the default unless the 'set' keyword is used explicitly, and so in VBA this would be interpreted as an attempt to assign "Hello" to the Value property of the range returned by the Worksheet.Range method, which is perfectly legal. With the extra set of empty parentheses, however, you are explicitly assigning "hello" to the Range._Default property, while providing neither optional argument.

Quote:
Originally Posted by Colin
I think this comes back to what you said earlier in the thread here (bolded):
Quote:
Originally Posted by Mike
I've been coding in C# too long, where the separation between property syntax and method syntax is rigid. In VBA, one can omit the empty parentheses when calling a method that takes no parameters, and, as I have learned here, one can add empty parentheses to a property call that takes no parameters. I find the former a little odd, and I find the latter pretty crazy -- but this is after experience with other languages. I didn't seem to complain much when I was using VBA all the time!
but I just want to tie it up in my own head...
In the above I was just trying to say that VBA is loose with respect to whether parentheses are required or not. In general, in VBA, if the parameters are not present or are all optional, it is legal to either leave the parentheses off, or include a pair of empty parentheses, regardless of whether a method or a property is being called.

For example:
Code:
Sub MyMacro()
    Application.Range("A1").Formula() = MyValue
End Sub
Function MyValue()
    MyValue = 5
End Function
In the above, I've included a needless pair of parentheses when refering to the Range.Formula() property, which is allowed in VBA (if terribly ugly), but many languages would complain that this syntax is an attempt to use a property like a method. Going the other way, the call to the method 'MyValue' does not include any parentheses, which would not be allowed in many languages, because the 'MyValue' member *is* a method and the extra parentheses would be *required* when calling a method.

This kind of thing can make it confusing when looking at VBA syntax, if trying to determine whether Range.Cells() is a call to the Range.Cells property with a needless set of parentheses attached, or is, in fact, a call to the Range.Cells property followed by a call to the parametrized _Default property, with both optional parameters omitted. Since I'm so used to using .NET at this point, I assumed that Range.Cells() in VBA was the latter: a call to Range.Cells property (which in .NET could never include parentheses), followed by a call to the parametrized _Default property, with both optional parameters omitted. Further evidence for this is that IntelliSense *tells* us that the _Default property is being called.

The answer, however, is that, in VBA, a call to Range.Cells() actually represents just a call to the Range.Cells property, with an extra set of needless (but permitted) empty parentheses attached at the end. This can make it *very* difficult in any situation to determine if the _Default property is being called

E.g., in the following, the Range.Cells property is being read, followed by a let assignment to the Range.Value property:
Code:
' VBA
Range.Cells() = 5
In the following, however, the Range.Cells property is being read, followed by a let assignment to the Range._Default property.
Code:
' VBA
Range.Cells(1) = 5
Confusing looking eh? Even more so when the IntelliSense information provided incorrectly suggests that Range.Default is being called in both cases!

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 03-10-2010 at 06:18 AM.
Reply With Quote
  #19  
Old 03-12-2010, 02:11 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Hi Mike,

Thanks for that - I think that clears everything up for me... for now.....

Thanks again,
Colin
Reply With Quote
  #20  
Old 03-12-2010, 02:30 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by Colin Legg View Post
Thanks for that - I think that clears everything up for me... for now.....
Well, let's hope so!
,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Cells property, early and late binding
Cells property, early and late binding
Cells property, early and late binding Cells property, early and late binding
Cells property, early and late binding
Cells property, early and late binding
Cells property, early and late binding Cells property, early and late binding Cells property, early and late binding Cells property, early and late binding Cells property, early and late binding Cells property, early and late binding Cells property, early and late binding
Cells property, early and late binding
Cells property, early and late binding
 
Cells property, early and late binding
Cells property, early and late binding
 
-->