Range.Cells() default member access explained

IvenBach

Oct 29, 2019 21:03

Is the default member access inspection correct? bar = fooSheet.Cells(inputRow, inputColumn).Value2 is coming up as a result. I'm not using the Excel.Range._Default member since I'm explicitly using the Value2 member. What am I missing?

M.Doerner

Well, there also is that they introduced a completely new API for 32bit.

You really do not want to do that again today.

this

That would probably explain why there is so little VB5 code transitioned to VB6 code. At least the API stuff.

M.Doerner

@IvenBach It is the indexed one, right?

IvenBach

Indexed one?

M.Doerner

ÌndexedDefaultMemberAccessInspection

IvenBach

Oct 29, 2019 21:06

Yes. That is the inspection.

M.Doerner

You might want to have a look at the documentation for Range.Cells, which I extended two weeks ago.

To make it short, Cellsreturns a range and has no parameters.

Cells(a,b) is Cells.Item(a,b).

The result is correct and only a hint since default member access for collection access is an understandable and quite transparent use case.

IvenBach

Is that the same for Range, Range.Columns, Range.Rows and pretty much everything else that returns a range object?

I have a braintickle telling me you let me know this already.

M.Doerner

Actually, Cells(a,b) is Cells.[_Default](a,b), but that forwards to Item.

Yes, and it is in the documentation, now.

Cells just changes the internal state of the range to consist of single cells.

this

hmm weird - it says it's merged but this still seems to be the old

IvenBach

^ I've been reading the commit to better grok your examples.

M.Doerner

Oct 29, 2019 21:17

Argh, I did not read far enough. It is not transferred yet.

IvenBach

Your example for columnsRange with the 3 explicit Debug.Print was very helpful.

M.Doerner

It is merged to master, but they only merge master to life at distant points in time.

Basically, their master is our next.

this

i have to say that it's kind of confusing

IvenBach

If you're not expecting that behavior.

this

I usually think of master as the primary trunk and the next / dev / whatever you call it as the growing stem

IvenBach

Oct 29, 2019 21:23

Never realized how pervasive default member access still is. Even after learning what it is and trying to avoid it I have a lot.

M.Doerner

Oct 29, 2019 21:34

I think it is OK for collection access as long as you are aware that it happens.

The completely implicit default member access fot Let coercion is the evil one.

IvenBach

The danger is "as long as you are aware that it happens." and it wasn't realizing it was happening. Even with .Cells() I wasn't understanding it.

M.Doerner

That is really the most confusing one, along Columns and Rows.

IvenBach

Range.Columns.Item(RowIndex:= ,[ColumnIndex:= ]) is doubly confusing since RowIndex isn't required.

M.Doerner

Yes, RowIndex is the relative column index here.

IvenBach

Range.Columns.Item("2:3") barfs while Range.Rows.Item("2:3") is perfectly fine.

M.Doerner

Oct 29, 2019 21:40

Oh, you can do that?

Then my changes on the documentation are still uncomplete.

IvenBach

Range.Columns.Item(2).Resize(ColumnSize:=2) is the workaround I've found for that difference.

M.Doerner

You can do Range.Columns("B:C").

this

don't they always have a R1C1 alternate?

M.Doerner

Not for this.

IvenBach

@M.Doerner WTF... I would have never thought of doing that. #TIL.

this

Oct 29, 2019 21:42

how fun

M.Doerner

At least, as far as I know.

IvenBach

Excel is stranger than I realized.

M.Doerner

See, I thought it only works for the Columns one.

IvenBach

It kinda-sorta make sense.

this

In my ignorance, I did write a routine to convert a number into a letter for accessing the column. It really chafes me that I had to do it myself when it should have been provided by Excel OM.

M.Doerner

Oct 29, 2019 21:43

You surprised me with the Rows equivalent.

this

(of course, now that I've written this, I'll probably learn about a super secret sauce way of converting a number to letter)

IvenBach

docs.microsoft.com/en-us/office/vba/api/… was my derp moment when I found out about it.

this

um. That presupposes there's a formula, doesn't it?

(i'm just using it for addressing)

M.Doerner

I only found out about the Columns version when rewriting the documentation for Item.

this

but I guess I could just wrap it with "=" & whatever, convert it then strip the =.... Clunky.

IvenBach

Oct 29, 2019 21:49

^ I know it's not always applicable.

I use formulas with cell references so for me it was a no-brainer.

this

That's an example of one responsibility too much right there

IvenBach

How so?

this

well, I wanted a conversion from A1 to R1C1 style (or vice versa)

but to use that function, I have to make a formula, then strip it away to get the result I wanted.

IvenBach

Yeah.

Now I see what you're saying.

this

in actuality, I should be calling Application.ConvertColumn(), whatever

and the Application.ConvertFormula() could then call the Application.ConvertColumn() as part of its conversion.

e.g. do one thing only and do it well.

but it's OK! Excel OM is totally intuitive! Fun! Simple!

M.Doerner

Oct 29, 2019 21:57

I think Rows, Columns and Cells should really return Ranges and Item should always work as for single cell ranges.

However, that will never change.

IvenBach

Regarding a For Each cell in FooBarArea depending on how FooBarArea is originally set that determines whether it's looping through each Range.Row, Range.Column, or Range.Cells?

M.Doerner

Yes

If it comes from someRange.Columns, it will iterate over the columns, for someRange.Rowsover the rows, both times only over the first area, and for someRange.Cells`over all individual cells.

IvenBach

Quite enlightening if you're not expecting that. So many times I've done set foo = BarSheet.Range("A1:D15") without realizing implicitly .Cells was part of the For Each loop.

M.Doerner

That is why it makes sense to always iterate over FooBarArea.Cells if you want individual cells.

IvenBach

32 mins ago, by IvenBach

The danger is "as long as you are aware that it happens." and it wasn't realizing it was happening. Even with .Cells() I wasn't understanding it.

M.Doerner

Oct 29, 2019 22:09

No, there is no implicit cells.

IvenBach

Now I am though.

If it's omitted it's implied, as far as I've understood.

M.Doerner

Worksheet.Range just returns a range consisting of single cells.

No, you can save soemRange.Comlumnsin a range variable and iterate over it later.

IvenBach

That would iterate over the column ranges.

M.Doerner

The kind of iteration behaviour and behaviour of Item is internal state of the range.

Yes, exactly.

IvenBach

I'd never understood all that adequately before to understand it correctly.

M.Doerner

Oct 29, 2019 22:12

BTW, this is bad API design.

IvenBach

Because .Item get's really confusing and isn't actually doing what its claiming?

M.Doerner

You basically have subtypes of Range violating the Liskov substitution principle.

IvenBach

Ah. You can't substitute a range object that a fooRange.Columns for a bar.Range.Rows and get the same behavior.

M.Doerner

Whenever you see a range, Item should do an equivalent thing for the same parameters supplied.

^^Exactly

I also was not aware of this until recently.

IvenBach

@M.Doerner :click: That's what you meant.

M.Doerner

Oct 29, 2019 22:14

I always just took ranges as parameters and never guarded against such differences.

IvenBach

Neither have I.

Then again I'm really the only one using my own code.

M.Doerner

When the methods are only private, i is OK, because you will probably not produce bad ranges in your own code in the module, but for public ones, it might make sense to guard against it.

Well, thinking about this strange behaviour of Range I think one understands why there is the L in SOLID.

The most stupid thing about this mess is that supplying both arguments to Item for a range obtained via Columns actually causes an error.

IvenBach

Public Sub TestingVariousRanges()
    Dim sourceArea As Range
    Set sourceArea = Sheet1.Range("A1:J7")

    Dim rowRange As Range
    Set rowRange = sourceArea.Rows

    Dim columnRange As Range
    Set columnRange = sourceArea.Columns

    Dim cellsRange As Range
    Set cellsRange = sourceArea.Cells

    PrintNthItem rowRange, 1
    PrintNthItem columnRange, 1
    PrintNthItem cellsRange, 1
End Sub

Private Sub PrintNthItem(ByVal printRange As Range, ByVal NthItem As Long)
    Debug.Print printRange.Item(NthItem).Address

@M.Doerner ^ That returned the results I was expecting. Thanks for this enligtenment.

M.Doerner

You are welcome!

VBA Rubberducking

Participants