Log In
Register
Home
December 29, 2020
Offline
RangeMortality(1, 1) is the incorrect way to use the Range Item Property like its proper way RangeMortality.Item(1, 1) , but I think you will usually get away with it.
Similarly in the last two, Cells.Item(1, 1) is the proper way to write Cells(1, 1)
Using Cells in this case is pointless I think since it returns a range object consisting of all the Cells of the object its applied to. So I think it returns what’s before it in this case. Applying it to a worksheet seems to be the most valid use of it.
I am not 100% sure on all that, but I thinks that’s basically it.
I’m using the word incorrect , proper , valid all very loosely and probably these and related aspects of the Areas is open to pointless academic debate, since there’s no real authority on this. You can argue if you like that the Cells item Property doesn’t exist: You could say Cells returns a range object to which you apply the Range Item Property.
Microsoft will agree with you one day and contradict you the next. Say what you like, it doesn’t matter. Microsoft change there documentation on it seemingly randomly.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Similarly in the last two, Cells.Item(1, 1) is the proper way to write Cells(1, 1)
Why is that the proper way?
TypeName(Cells.Item(1, 1)) returns an object of Range type, like any version below :
Cells(1)
Cells(1,1)
The object returned has the same Range type, all variations are 100% correct, there is no "better range" concept.
Same for Collections or Dictionary objects:
They accept different syntaxes, all correct:
objDict.Item("ItemKey"), objDict("ItemKey"), objDict.Item(1), objDict(1)
objColl.Item("ItemKey"), objColl("ItemKey"), objColl.Item(1), objColl(1)
The Item method is the default method for an object. Therefore, all variations are equivalent.
Microsoft says:
The default member of Range forwards calls with parameters to the Item member. Thus, someRange(1) and someRange(1,1) are equivalent to someRange.Item(1) and someRange.Item(1,1), respectively.
I would avoid Item, as it might bring more confusion, for example:
Debug.Print Range("B2:D4").Item(2, "D").Address 'Prints "$E$3"
More, if you select let's say cell G1, this code will return P1:
activecell.item(1,10).address
If you select another cell, the code will return another address, based on selected cell.
In this scenario, Item acts like the offset method:
activecell.Offset(1-1,10-1).address will also return P1.
December 29, 2020
Offline
Most of David’s problems originally arose because he was unknowingly using range object item referencing in this form
ArrayMortality(j, l)
, but he was mistakenly thinking he had an array.
He was using a range object and thinking it was an array, or visa versa.
So I put some emphasis on discussing Range Item and Range .Value ideas to try and help get across the difference between range objects and arrays.
I tend to be a bit more explicit when answering forum questions as I might be otherwise. I think it is easier to “go backwards” later, rather than starting to rely on implied defaults, and then trying to figure out what’s going on when things go wrong later. Just personal opinion , that’s all.
The word “proper” was a loose way of saying explicit, to some extent, I suppose.
Once again, I am using the term "go backwards” very loosely. It can be regarded as very professional to use the shortest, simplest coding making good use of all the implied defaults.
I don’t like these range item things that we are discussing myself.
My favourite way to range reference is the single argument Range(" ") way. But just personal choice again
Alan
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Alan,
You did a great job with detailed explanations, thank you for that, we all have preferences and habits.
Indeed, it is hard to see the difference between objects when reading data from sheets. For an object declared as a variant:
Dim MyData as Variant,
Set MyData =Range("A1:A10") makes MyData a Range
MyData =Range("A1:A10") makes MyData a bidimensional Array
Set is the only difference between the 2 statements, so it should be used wisely when assigning objects, Set is used only for assigning object references to a variable.
Objects like workbooks, worksheets, ranges, names, applications, cannot be assigned to a variable without the Set statement. Also, Set cannot be used to assign elements that are not valid object references.
Hope it helps clarifying things.
1 Guest(s)
