VBA handling of selected cells in multiple areas

[nac]

OS X 10.6.2
Excel 2004 11.5.6 (090928)

I'm hoping someone might be willing to see if an error I'm
experiencing with VBA for Excel 2004 is reproducible. I've been
working with VBA for eons and consider myself proficient.

Create a new spreadsheet.

Make a 2-row by 3-column matrix

1.1 1.2 1.3
2.1 2.2 2.3

Select the top-left cell (1.1) and, holding down the command key, the
top-right cell (1.3). You should see the two, separated cells
highlighted.

Open the VBA editor and choose the Immediate window.

?selection.cells.count should = 2
so far so good.

?selection.areas.count should also = 2
yes, that's also correct

?selection.cells(1).value should = 1.1,
still good

but,
?selection.cells(2).value should = 1.3

and i'm seeing 2.1.

I've done this with multiple workbooks, worksheets, numbers, and cell
offsets.


Would appreciate knowing if this in an Excel 2004 VBA bug, something
wrong with my setup, or user dumbness,


THANKS

 

[JE McGimpsey]

Would appreciate knowing if this in an Excel 2004 VBA bug, something
wrong with my setup, or user dumbness,

Not a bug.

For a multi-area selection, using index on the cells collection refers
implicitly to the first area.

In addition, the Range's (Cell's) Item property, when used with a row
offset, doesn't confine itself to the original Range.

So

Selection.Cells(2).Value

is equivalent to

Selection.Areas(1).Cells.Item(2,1).Value

which refers to the first cell in the first area of the selection,
offset by one row.

 

[nac]

Not a bug.

For a multi-area selection, using index on the cells collection refers
implicitly to the first area.

In addition, the Range's (Cell's) Item property, when used with a row
offset, doesn't confine itself to the original Range.

So

   Selection.Cells(2).Value

is equivalent to

  Selection.Areas(1).Cells.Item(2,1).Value

which refers to the first cell in the first area of the selection,
offset by one row.

Thank you so much. I would never have made sense of the observed
behavior without your crystal-clear explanation.