Solved: Basic Question: EntireColumn Property statement

[YellowLabPro]

This is from the help file- EntireColumns
"This example sets the value of the first cell in the column that contains the active cell. The example must be run from a worksheet."
ActiveCell.EntireColumn.Cells(1, 1).Value = 5

I have a few questions about it;

1) if reference is made to the EntireColumn, why does the reference revert back back to the .Cells?

2) it also references ActivCell, which seems to get referenced back to .Cells(1,1), where the starting point would be row 1?

3) in the help file, the following point I do not see how to carry this command out if it is to be run from the worksheet- could you provide the how to: to execute this so I can see the results?
"The example must be run from a worksheet."

4) Still working on understanding the object model, Value is a what? A parameter object?

Thx,

Doug

[rory]

1. EntireColumn returns a range representing every cell in the column; adding .Cells(1,1) returns the top left cell of that range - i.e. the first cell in the column. So no matter what row the activecell is in, you would always set the value of the cell in row 1 of that column.
2. ActiveCell is just that - the active cell. It can be anywhere on the sheet.
3. I assume that just means that you have to have a worksheet (rather than chart or macro sheet) active.
4. Value is a property of the range object. (It also happens to be the default property in every version of Excel to date)

[Bob Phillips]

Doug,

What you must understand that as you work down the object model, you are homing in on a more precise part of that object model.

So if you start with Activecell. you have a very specific range that has a property. However, you can easily increase that range with Resize, or as in this example, Entirecolumn. Here

Activecell.Entirecolumn

then expands the range to the whole of the column that the activecell is within.

The cells property

ActiveCell.EntireColumn.Cells(1, 1)

is being used to home in on a particular cell of the column. It is being used presumably on the basis that the code does not know which row the activecell is in, so it cannot use Offset. Here, the cells property is picking out row 1 column 1 of the column.

Then the Value property is simple used to get the value in the identified cell.

It is one way. You could as easily have used

Cells(1,Activecell.Column).Value = 5


But don't get too hung up on whether things are properties or objects, sometimes they can be both, concentrate on the OM structure.

[YellowLabPro]

Thanks Rory, Thanks Bob.

Bob-
This line was key:


Here, the cells property is picking out row 1 column 1 of the column.



What I expected to see was the value of 5 down the entire column, since it referenced the EntireColumn. In testing I actually changed my arguments to (1,16) from (1,1) so I expected to see the value 5 down col. P.
It put the value in 16 columns to the right of the active cell- which was way off my screen- so I did not see the value show up anywhere. So as a result of the help files statement "that it needed to be run from the worksheet", I was then thinking I needed to call it somehow from the worksheet..... so this is how one misconception can lead to a completely wrong interpretation. "Bassackwards".

Now w/ your explanation I see that it was referencing the cell in row 1, 16 columns to the right of the activecell.

Most grateful for the help here-


Cheers,

Doug

[YellowLabPro]

One final note-
In using the Object Browser- I followed it down to its core in Group:

Members:
Property Value([RangeValueDataType])
Member of Excel.Range


Here it tells me as both Rory and Bob point out it is a Property and a Member of Range

[Bob Phillips]

That is an interesting point Doug. IMO, you should get a runtime error when you do that. My reasoning here is that by the time you have done

Activecell.EntireColumn

your range is 1 columns wide. To then pick out the 4th column of a one column range is nonsense IMO, and should error.

Another case where VBA is far too loose, and would be better if it were tighter.

BTW, 1,16 is 15 columns to the right, column 1 is this column (unike Offset, where 0 is this).

[rory]

And from my point of view, it's really a method rather than a property, since Value takes an argument.

[Norie]

Rory

Value doesn't take any arguments, you set it's value.:)

[rory]

Not sure how far back this goes, but certainly works in XL2002:
Debug.Print ActiveCell.Value(xlRangeValueXMLSpreadsheet)

which as far as I am concerned is more method than property!

[Bob Phillips]

Just because you can pass a constant to tell how you want the property to be processed does not make it a method. A method acts upon an object, so assuming that the Range must be the object in this instance, what action upon the range is the value performing? It isn't, it is still an atrribute, or property, of that object.

[rory]

I admit I had forgotten that you can use arguments in Property statements but I think it can be a bit of a grey area as to what is a property and what is a method - I don't see how that's less of a method than Application.Volatile or the Item or Count methods of a collection. To my mind it converts a range to an XML document effectively, which seems more like a function than a property. But it's not a big deal.